Run a report , filter by inputbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 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

Jeff Boyce said:
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>

Ed said:
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
Click to expand...
Click to expand...
 
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>

Ed said:
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

Jeff Boyce said:
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>

Ed said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

:
Click to expand...
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[/QUOTE]
 
Back
Top