Report - Record Source

[NEWER USER]

I want to build (1) report and have my Record Source be one of three queries
based on a selection in an option box on a form. Can I do this? If so, do
you have any coding that might get me started?

OR

Do I need to create three reports and use each query as the record source?

Thanks

 

[Evi]

If you have Access 2003 + you can use

Reports!MyReportName.RecordSource = "MyQuery"
where MyQuery is the name of the query you wish to change to.
I don't know if it works with earlier versions.
Evi

 

[NEWER USER]

Would my code go something like this?

DoCmd.OpenReport "rptCatalog"

If OptionA =1 Then
Reports!rptCatalog.RecordSource = "MyQuery"
End If

If OptionA =2 Then
Reports!rptCatalog.RecordSource = "MyQuery1"
End If

 

[NEWER USER]

My current code is: How would I change it as per your example?

If Forms!PrintReportsDialog!SelectBuyer = 1 Then
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere
End If
If Forms!PrintReportsDialog!SelectBuyer = 2 Then
DoCmd.OpenReport strDoc1, acViewPreview, _
WhereCondition:=strWhere
End If
If Forms!PrintReportsDialog!SelectBuyer = 3 Then
DoCmd.OpenReport strDoc2, acViewPreview, _
WhereCondition:=strWhere
End If

 

[fredg]

Would my code go something like this?

DoCmd.OpenReport "rptCatalog"

If OptionA =1 Then
Reports!rptCatalog.RecordSource = "MyQuery"
End If

If OptionA =2 Then
Reports!rptCatalog.RecordSource = "MyQuery1"
End If

No it would not go like that.
What are the differences between the queries?
Only the criteria or do they each return different fields.

If they each return different fields, then you would be probably be
better off to have 3 different reports.

If the fields are the same in all three, but the criteria fields are
different, you could open the one report using the filter argument of
the OpenReport method.

Assuming you have 3 different queries:

Dim strQuery as String
If OptionA = 1 Then
strQuery = "Query1"
Elseif OptionA = 2 Then
strQuery = "Query2"
Else
strQuery = "Query3"
End if

DoCmd.OpenReport "ReportName", acViewPreview, strQuery

The above Filter argument is seldom used.

Better yet, simply have one report that returns all the records (no
criteria), and use the Where clause argument, instead of the filter
argument, to open the correctly filtered report.

Dim strWhere as string
If OptionA = 1 Then
strWhere = "[RecordID] = " & Me![RecordID]
ElseIf OptionA = 2 Then
strWhere = "[CompanyName] = """ & Me![CompanyName] & """"
Else
strWhere = "[SalesDate] = #" & Me![SalesDate] & "#"
End If

DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

Note that there is a difference in the syntax when writing a where
clause depending upon the criteria field's datatype.
In order above, Number, Text, and Date datatypes.

In VBA help, look up OpenReport, Where clause, and Restrict data to a
subset of records, for more information.

 

[Evi]

From your code, it looks as if you would be better off filtering your report
as Fred suggested, rather than changing the record source or creating 3
different reports. Why won't that work? Do you have a badly constructed
database with each buyer in a different table? If yes, read the trials of
ftwguy and weep!
Evi