Change Record source in Report

[M.A.Halim]

I have a report "RptSales" that gets it's record source from a "SalesQry".
and I want to use the same report but to change the record source from
"SalesQry" to "TransactionsQry".
I was trying to write the proper code on the click event of a command Button
on a form, using a Code but doesn't Work or I guess I wrote it wrong.
Any Idea how to write the code properly?
Appreciating your time and continuous help.

 

[Douglas J. Steele]

If the report's open, you can't change its recordsource.

If it's not open, you can open it in Design view, change its recordsource
(using Report![NameOfRepor].RecordSource = "QueryName") then open it in
Preview (or Print) mode.

 

[Tom Wickerath]

You can use the Report_Open procedure to change the recordsource for a report
on-the-fly. If your version of Access supports the optional OpenArgs
parameter for DoCmd.OpenReport, you could use code similar to this in the
report:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Nz(Me.OpenArgs, "qryAllCustomers")
End Sub


and code similar to this in the form that is used to open the report:

Option Compare Database
Option Explicit

Private Sub cmdAllCustomers_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="Customers", _
View:=acViewPreview, OpenArgs:="qryAllCustomers"

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Command0_Click..."
Resume ExitProc

End Sub

Private Sub cmdGermanCustomers_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="Customers", _
View:=acViewPreview, OpenArgs:="qryGermanCustomers"

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Command0_Click..."
Resume ExitProc

End Sub


I think OpenArgs was added as an optional parameter for DoCmd.OpenReport for
Access 2002 (?), but it might have even been as early as Access 2000. I'm
pretty sure it is not available with Access 97.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

PS. In the example I showed you above, I used two different command buttons
on the form. However, you could also modify the code to grab the appropriate
OpenArg value from a control such as a text box, combo box, or list box (a
list box with Multiselect property set to None).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[M.A.Halim]

I'm Using Access 2003 and I think that it will work this way
Thanks a lot,

 

[M.A.Halim]

So in this case do I have to use the report open procedures along with the
DoCmd on the form or just any of them?

 

[Tom Wickerath]

I don't understand what you are asking...

The report open procedure can be used to change the recordsource (table or
query) for the report, if this is something that you really need to do.
_______________________

If your database design is such that you don't need to change the
recordsource for the report (ie. the various queries that you want to specify
are all based on the same table or tables), then you do not need the code in
the report. You can use the optional WhereCondition parameter to open the
report filtered to the appropriate records. For example:

DoCmd.OpenReport ReportName:="ReportName", View:=acViewPreview, _
WhereCondition:="[TransType] = " & Me.cboTransType

This would require that you have a field named TransType in your table, to
designate the type: Sales vs. Transactions. The combo box would present the
available choices to the user.

Another option would be to base the report on a query that looks to your
open form to pick up a parameter. For example, the query might include the
following criteria:

Like [Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"

An example of this last form is available for download, here:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________