Run a report , filter by inputbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 02 Feb 2006 19:21 GMT

I am trying build a function, where a report can run, and is filtered by user
inputting a parameter in a inputbox.

This does not work; it still prompts for the parmater from the underlying
query
prmQuote is a text field, specified on the underlying query behind the report

also generates an error message ...no n umber, but "The expressioin is
typoed incorrectly". This occurs on the first "DoCmd.OpenReport ..."

Any help is appreciated

Code is as follows

Option Compare Database
Option Explicit
Public strDocName As String
Public strWhere As String

Function Internal_Report()
On Error GoTo Err_Internal_Report
strWhere = "[prmQuote] = " & InputBox("Enter Quote Number")
strDocName = "rpt_Approval_Summary"
DoCmd.OpenReport strDocName, acPreview, , strWhere

strDocName = "rpt_ExplodedBOM"
DoCmd.OpenReport strDocName, acPreview

strDocName = "rpt_approval"
DoCmd.OpenReport strDocName, acPreview

Exit_Internal_Report:
Exit Function

Err_Internal_Report:
MsgBox Err.Description
Resume Exit_Internal_Report
End Function

Reply to this Message

Jeff Boyce - 02 Feb 2006 20:16 GMT

Ed

Another approach to "ordering" a report that has a selection criterion
(i.e., a parameter) is to use a form. The form has a control for selecting
(or entering) the value, and a command button for opening the report. The
report is based on a query. The query has a selection criterion that
"points" to the control on the form, with syntax something like:

Forms!YourFormName!YourControlName

Regards

Jeff Boyce
<Office/Access MVP>

>I am trying build a function, where a report can run, and is filtered by
>user
[quoted text clipped - 36 lines]
> Resume Exit_Internal_Report
> End Function

Reply to this Message

Ed - 03 Feb 2006 11:29 GMT

I was hoping to use an inputbox vs creating a new form.

The reports are generated from a switrchboard option; 1 button on the
switchboard drives 3 different reports, each using one common prompt
parameter of prmQuote.

Pls advise...and thx for the help

> Ed
>
[quoted text clipped - 51 lines]
> > Resume Exit_Internal_Report
> > End Function

Reply to this Message

Jeff Boyce - 03 Feb 2006 19:43 GMT

Sorry, haven't done it that way. Perhaps one of the other 'group readers
can offer assistance on doing it the way you've decided.

Regards

Jeff Boyce
<Office/Access MVP>

>I was hoping to use an inputbox vs creating a new form.
>
[quoted text clipped - 62 lines]
>> > Resume Exit_Internal_Report
>> > End Function

Reply to this Message

Chuck - 04 Feb 2006 13:29 GMT

>I was hoping to use an inputbox vs creating a new form.
>
[quoted text clipped - 3 lines]
>
>Pls advise...and thx for the help

It looks to me like you are almost there.
Instead of having the button on the switch open the 3 reports, have it open a
small form. On the form have a control that allows you to enter the desired
parameter. Design the control the way you want. It could accept a simple
typed in parameter. Or it could be designed to get a parameter from a drop
down list. Move the button from the switchboard that opens the reports to the
form you just made. In the queries for the reports, set the criteria for the
parameter to be whatever is in the control in the form.

[Forms]![exact name of your new form form]![exact name of control you just
added to the form]

Do not close the form before opening the reports. After the 3 reports have
been viewed, printed exported, or what ever, close them. Your form will be
visible again. You can enter a different value and open the reports again if
you want. Put another button on the form to close the form when you are
finished with it.

Each report will look for the query it requires. Each query will look for the
value in the control in the form you just made.

This is exactly what Jeff said. Just elaborated a little.

Just a wizard prodder
Chuck

Signature

>> Ed
>>
[quoted text clipped - 10 lines]
>> Jeff Boyce
>> <Office/Access MVP>

Reply to this Message