Pass x number of parameters to a report from a form

M

mc

I created a form that allows the user to print reports based on which GL
accounts that they select. The selection could be 1 or x number of GL
Accounts. If it is only 1 GL account, this is the coding:

Private Sub cmdCollateralRpt_19396xx_Click()
On Error GoTo Err_cmdCollateralRpt_19396xx_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b, as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
strWhere = "[GLACCT] >= " & lngAcct1a & " and [GLACCT] < " & lngAcct1b
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_19396xx_Click:
Exit Sub

Err_cmdCollateralRpt_19396xx_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_19396xx_Click
End Sub

If it is 2 GL Accounts, this is the coding:

Private Sub cmdCollateralRpt_Click()
On Error GoTo Err_cmdCollateralRpt_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b,lngAcct2a,
lngAcct2b as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
lngAcct2a = 2913000
lngAcct2b = 2914000

strWhere = "([GLACCT] >= " & lngAcct1a & " And [GLACCT] < " &
lngAcct1b & ") "
strWhere = strWhere & "OR ([GLACCT] >= " & lngAcct2a & " And [GLACCT]
< " & lngAcct2b & ") "
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_Click:
Exit Sub

Err_cmdCollateralRpt_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_Click
End Sub

The report works fine but the user wants to include the GL accounts in the
report header. Can I pass the parameters lngAcct1a, lngAcct1b, lngAcct2a,
lngAcct2b to the report depending on which selection the user runs? Or do I
have to create x number of reports and hardcode the GL Account in each
report? I have over 50 GL accounts that the user wants.
 
D

Duane Hookom

The easiest solution would be to add a text box to your report header section
with a control source of:
=[Filter]
 
M

mc

Duane - thanks for the quick response. I did try what you suggested earlier,
my text box = [Forms]![name of my form]![lngAcct1a] but I got an #error
because lngAcct1a doesn't really exist on my form, it's a variable.

Duane Hookom said:
The easiest solution would be to add a text box to your report header section
with a control source of:
=[Filter]
--
Duane Hookom
Microsoft Access MVP


mc said:
I created a form that allows the user to print reports based on which GL
accounts that they select. The selection could be 1 or x number of GL
Accounts. If it is only 1 GL account, this is the coding:

Private Sub cmdCollateralRpt_19396xx_Click()
On Error GoTo Err_cmdCollateralRpt_19396xx_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b, as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
strWhere = "[GLACCT] >= " & lngAcct1a & " and [GLACCT] < " & lngAcct1b
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_19396xx_Click:
Exit Sub

Err_cmdCollateralRpt_19396xx_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_19396xx_Click
End Sub

If it is 2 GL Accounts, this is the coding:

Private Sub cmdCollateralRpt_Click()
On Error GoTo Err_cmdCollateralRpt_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b,lngAcct2a,
lngAcct2b as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
lngAcct2a = 2913000
lngAcct2b = 2914000

strWhere = "([GLACCT] >= " & lngAcct1a & " And [GLACCT] < " &
lngAcct1b & ") "
strWhere = strWhere & "OR ([GLACCT] >= " & lngAcct2a & " And [GLACCT]
< " & lngAcct2b & ") "
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_Click:
Exit Sub

Err_cmdCollateralRpt_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_Click
End Sub

The report works fine but the user wants to include the GL accounts in the
report header. Can I pass the parameters lngAcct1a, lngAcct1b, lngAcct2a,
lngAcct2b to the report depending on which selection the user runs? Or do I
have to create x number of reports and hardcode the GL Account in each
report? I have over 50 GL accounts that the user wants.
Click to expand...
Click to expand...
 
M

mc

Okay, I decided to put a text box in my form, put the correct value(s) in
this box each event procedure and then refer to it in my report. Thanks for
your help.

mc said:
Duane - thanks for the quick response. I did try what you suggested earlier,
my text box = [Forms]![name of my form]![lngAcct1a] but I got an #error
because lngAcct1a doesn't really exist on my form, it's a variable.

Duane Hookom said:
The easiest solution would be to add a text box to your report header section
with a control source of:
=[Filter]
--
Duane Hookom
Microsoft Access MVP


mc said:
I created a form that allows the user to print reports based on which GL
accounts that they select. The selection could be 1 or x number of GL
Accounts. If it is only 1 GL account, this is the coding:

Private Sub cmdCollateralRpt_19396xx_Click()
On Error GoTo Err_cmdCollateralRpt_19396xx_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b, as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
strWhere = "[GLACCT] >= " & lngAcct1a & " and [GLACCT] < " & lngAcct1b
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_19396xx_Click:
Exit Sub

Err_cmdCollateralRpt_19396xx_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_19396xx_Click
End Sub

If it is 2 GL Accounts, this is the coding:

Private Sub cmdCollateralRpt_Click()
On Error GoTo Err_cmdCollateralRpt_Click
Dim strReportName, strWhere As String, lngAcct1a, lngAcct1b,lngAcct2a,
lngAcct2b as Long

strReportName = "rpt_GL"
lngAcct1a = 1939600
lngAcct1b = 1939700
lngAcct2a = 2913000
lngAcct2b = 2914000

strWhere = "([GLACCT] >= " & lngAcct1a & " And [GLACCT] < " &
lngAcct1b & ") "
strWhere = strWhere & "OR ([GLACCT] >= " & lngAcct2a & " And [GLACCT]
< " & lngAcct2b & ") "
DoCmd.OpenReport strReportName, acPreview, , strWhere

Exit_cmdCollateralRpt_Click:
Exit Sub

Err_cmdCollateralRpt_Click:
MsgBox Err.Description
Resume Exit_cmdCollateralRpt_Click
End Sub

The report works fine but the user wants to include the GL accounts in the
report header. Can I pass the parameters lngAcct1a, lngAcct1b, lngAcct2a,
lngAcct2b to the report depending on which selection the user runs? Or do I
have to create x number of reports and hardcode the GL Account in each
report? I have over 50 GL accounts that the user wants.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass Date parameters to a Query 1
Can't get Date Range to work from Form to Report 1
Cannot pass value of date to a report that uses a query as its sou 3
Printing report from a form, what's going wrong 4
Printing one record on a report from a command button in a form 1
Passing Variable String from Form to a Report Textbox 3
Generating a report from a form 0
print more than one copy of a report from a form. 5

Top