How can I change the recordsource of a report in code?

[Andrew Soep]

I have a report that I want to be able to control which recordsource it uses in code. I tried setting Me.RecordSource = MySQL statement in the OnOpen event, but it uses the one bound to the report instead and will never set it properly from code.

Any help would be appreciated


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Functions For UserData Persistence Store
http://www.eggheadcafe.com/tutorial...c-f7cc91cb3c39/generic-functions-for-use.aspx

 

[ghetto_banjo]

I had this same problem and never found a direct solution, but i did
find a workaround that got the job done.

I saved a query that was being used as the recordsource of the
report. then in VB, i use a QueryDef and point it to that saved
query. I then updated the SQL parameter of the QueryDef before I open
the report.

here is the snippet of code for a querydef as an example:

Dim qdf As QueryDef
Dim db As Database

Set db = CurrentDb
Set qdf = db.QueryDefs("qryReportSource")

qdf.SQL = "SELECT IDNum FROM tblData WHERE IDNum > 100"

DoCmd.OpenReport "rptIDNumbers"

 

[Duane Hookom]

What is MySQL?
I generally prefer to change the SQL of a saved query by you shouldn't have
an issue changing the record source in the On Open. Maybe you should share
more of your code and context.