Pass x number of parameters to a report from a form

  • Thread starter Thread starter mc
  • Start date Start date
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.
 
The easiest solution would be to add a text box to your report header section
with a control source of:
=[Filter]
 
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...
 
Back
Top