Report - Record Source

N

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
 
E

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
 
N

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
 
N

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
 
F

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.
 
E

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
 

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

Top