Changing a Reports Recordsource

[dapotter]

I have a form with a pushbutton which, when clicked, is to launch a report. I am using the DoCmd.OpenReport method to open the report.

What I want to do is change the report's record source based on data contained in the form. My preference is to set the report's record source property equal to some query string I define using the forms VBA code. I'm willing to set the report's record source to a saved query if I can then modify the saved query from the form's VBA code.

I've made several attempts to edit the reports record source string, but each one has failed for some reason or another (cant find the report, object not defined, etc.).

How can I modify the record source of an existing report? Is there a way to modify the definition of a saved query? Is there a different/better approach to take?

Thanks,
Don

 

[flyover789]

changing the SQL of a saved query via VBA code:

Dim strSQL as String
Dim db as DAO.Database
Dim qdf as DAO.Querydef
Set db = Application.CurrentDB
set qdf = db.Querydefs("yourSavedQueryName")

strSQL = "SELECT ... blah blah ...;"

qdf.SQL = strSQL

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[dapotter]

I tried your suggestion, but since my database is currently modeled for ADODB, it didn't work well. Is there a method that would allow me to do this without having to convert to DAO?

Don

 

[cmmrfrds]

Are you creating the SQL in the OnOpen Event of the Form? If so, what have you done that didn't work.

 

[PHV]

Unless you're playing with an ADP database all the Form's and Report's RecordSource are DAO recordsets ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dapotter]

cmmrfrds,

I wasn't creating the SQL in the OnOpen event, but in the Click event of a push button. I've tried so many different things that I can't remember what I've done and when. I'm trying to duplicate some of them now in the few minutes before I have to leave for work. To the best of my recollection, I was having a difficult time getting the VBA to even recognize that I had a report saved in the database. When I finally was able to access the report, my attempts to set the RecordSource property failed.

PHV,

I don't know anything about ADP databases, but when I tried the DAO based suggestion given by flyover789, it wouldn't work until I added the Microsoft DAO 3.6 Object Library as a reference and moved it in front of the Microsoft ActiveX Data Objects 2.1 Library. I don't even know if that works because after changing the references, my ADODB recordsets stopped working.

I won't have much time in the next couple days to do anything. When I get time, I may try to get rid of all of my ADODB recordsets with DAO recordsets.

Thanks,
Don

 

[PHV]

When you use both ADO and DAO you have to qualify the objects:
Dim rs1 As DAO.Recordset, rs2 As ADODB.Recordset

The most common concerned objects are Recordset, Field(s) and Parameter(s).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886