Call report from VB, assign parameters dynamically and preview

[greggranger]

I have been struggling to find an easy solution to open a report and assigning varaiables depending on a user's form criteria.

For example, I have a single report named "RcrvOhio". The report is based on a stored procedure which accepts two parameters: @REPORTDATE and @NAIC. THe @REPORTDATE parameter is always based on a Form object ([Forms]![frmReportList]![cmbPerEndDate]) but the @NAIC variable is a constant value depending on the button a user presses.

I was unable to "hard code" the values into the report's InputParameters property since the @NAIC variable changes depending on the user's selection.

This is the solution I came up with. Mabe it can help someone with a similar problem.

CODE:

Private Sub cbt_OH_148_Click()
cmdRunEstateReport.Visible = False

'Open design view of report (hidden)
DoCmd.OpenReport "RcvrOhio", acViewDesign, , , acHidden

'Set report parameters
Reports(RcrvOhio).InputParameters = "@REPORTDATE = '" & [Forms]![frmReportList]![cmbPerEndDate] & "', @ESTATE = '" & 28100 & "' "

'Close design view of Report and Save
DoCmd.Close acReport, "RcrvOhio", acSaveNo

'Open Report
DoCmd.OpenReport "RcvrOhio", acViewPreview

'Save report so user is not promted to
DoCmd.Save acReport, "RcvrOhio"
End Sub

 

[cmmrfrds]

I typically return the variables from function(s) in the standard module.
For example.
In standard module.
Public pubreportdate as date
Public pubnaic as long

Public Function ReturnReportDate() as Date
ReturnReportDate = pubreportdate
End Function
Public Function ReturnNAIC() as Long
ReturnNAIC = pubnaic
End Function

Load these public variables from the Form buttons.
Input parameters on the report.
"@REPORTDATE=ReturnReportDate(),@NAIC=ReturnNAIC()