How to set the Record Source for a Report using VBA?

[vwgolfman]

Hi,

I have a Report which can be driven by either of two Queries (each has slightly different criteria).

On a form I have a ComboBox and a CommandButton.
I wish to set the RecordSource for the Report before opening it.
It will be one of the two Queries mentioned and will be determined by the value selected in the ComboBox.

The Click event for the CommandButton opens the Report by using the following method...
DoCmd.OpenReport "ReportName", acPreview

Please can somebody tell me how I set the RecordSource to the necessary Query before opening the Report?

Thank you.

 

[pbaldy]

I do it in the Open event of the report:

If Forms!FormName.ComboName = "Whatever" Then
  Me.RecordSource = "QueryName"
Else
  Me.RecordSource = "OtherQuery"
End If

 

[vwgolfman]

Paul,

What I failed to mention (as I wanted to keep it simple) is that there are 2 buttons and several comboboxes on the form.

The RecordSource is determined by a combination of which buttons is clicked and the options chosen in the comboboxes.

For this reason I believe the RecordSource would be best set in the Click event of the button(s).

Is this possible?

 

[pbaldy]

I believe so, but I think you have to open the report in design view, set the source, close/save it, then open it again in preview/print mode. That's why I find the Open event simpler. If you intend to distribute the application as an MDE, you can't open the report in design view, which is another vote for the open event of the report.

 

[datAdrenaline]

What I do is dedicate a query object to a report. For example:

for a report named "rptMyReportName", i'd have a query named "sel_src_rptMyReportName"

So the reports RecordSource property would be FIXED as ...
sel_src_rptMyReportName

Then in the code that is used to Open the report, I modify the dedicated Query's SQL property in such a way to provide the data I want for the report..

Public Sub cmdOpenMyReport_Click()
 
    Dim strSQL As String
 
    'Build the SQL statement that gets the data you want
    If <SomeConditionExists> Then
        strSQL = "SELECT * FROM YourFirstDataGatheringQueryName"
    Else
        strSQL = "SELECT * FROM YourOtherDataGatheringQueryName"
    End If
 
    'Set the SQL of the QueryDef that is set as the Reports source
    CurrentDb.QueryDefs("sel_src_rptMyReportName").SQL = strSQL
 
    'Now open the report, which is bound to the query you just modified.
    On Error Resume Next 'supress the message if OnNoData cancels report
    DoCmd.OpenReport "rptMyReportName", acViewPreview
 
End Sub

By using this technique, you don't have to continually modify the Reports design via code, thus reducing the chance of corruption, and you decrease db bloat, PLUS, your app can still be distributed as an MDE/ACCDE (reports can not be put in DESIGN mode in an MDE/ACCDE).

Hope that helps!!

 

[vwgolfman]

Thanks both very much for two excellent methods to use.

I have considered both and also my project scenario and have come to the conclusion that the best option for me would be to set the query in the report's open event BUT...

Is it possible to pass the Query Name over to the Report_Open Sub from the Button_Click event on the form?

If so what would be the syntax?

Thank you.

 

[datAdrenaline]

If you have A2003 or newer, you can utilize the OpenArgs argument of the DoCmd.OpenReport method ... its the last argumnent and accepts a string. You could pass the name of the query you want to use as the recordsource ... then use that value in the reports open event ....

DoCmd.OpenReport "",acViewPreview,,,,"NameOfQuery"

....

Then in the Reports open event....

Private Sub Report_Open(Cancel As Integer)
    
    If Len(Me.OpenArgs & "") > 0  Then
        Me.RecordSource = Me.OpenArgs
    End If

End Sub

Hope that helps!

 

[LPurvis]

Indeed 2002 or newer.

 

[datAdrenaline]

DOH!! ... I skipped that version ... I keep forgetting in existed!!! ...

------

I almost edited my post and replied with ...

"Yep ... that's what I said! ... mwuahahahaha!!! .."

 

[LPurvis]

lol
Poor old 2002. I know what you mean. It never got a good shot at the world. It introduced many important functionalities that we take for granted (not least of which my old favourite List Control binding!)
The soon to follow, bug fixing, stable release always steals the thunder (Acc 97 and 2003).
I imagine Acc 15 could be kick-ass.

 

[vwgolfman]

OpenArgs is indeed what I have gone for

Thanks guys Woohoo!!