Changing the Recordsource of a Report

[branston]

Hi,

I have a report with the Record Source Qry1. 99% of the time, I want it to open with Qry1 as its record source, but when a certain button is pressed I want its record source to change to Qry2.
Ive looked this up on the net, and i seem to be getting conflicting information, some saying you cant do it when the report is open, others saying you cant do it when its closed.

Because of the way its set up i dont really want to have anything in the OnOpen comand of the report, so is there a way to change it in the VBA code of the button which opens the report?

I tried:
DoCmd.OpenReport "RepCtrForm", acViewPreview
Me.RecordSource = "Qry2"

but that didnt seem to work.
Any ideas/information would be greatly appreciated. Thank you!

 

[boblarson]

Actually, what is the difference between Query 1 and Query 2? If it is just the WHERE clause then you can do something like I have here where I have a generic report and I use code to change the Where clause and pass that when opening.

 

[branston]

Ooh, nice form... I think that might be very useful with a future project I have!
But for this, unfortunatley, I dont think it will work. Qry2 has an extra table in, with extra linked fields etc. I cant think how you would do that in a where clause.

 

[boblarson]

Ooh, nice form... I think that might be very useful with a future project I have!
But for this, unfortunatley, I dont think it will work. Qry2 has an extra table in, with extra linked fields etc. I cant think how you would do that in a where clause.

Yep, that wouldn't work then. Personally, as it has changed that much I would just have a separate report and call the correct one as needed. Changing the record source of a report is a hassle as you have to open it first in design view (you can do it while hidden) and then do the change and then save it and then reopen it. That by code.

 

[branston]

Mmm, yeah, I read that somewhere, but it said it wouldnt work if the database was used in run time, which ours will be eventually.
I managed to get the recordsource bit working:

DoCmd.OpenReport "RepCtrForm", acViewPreview
Reports("RepCtrForm").RecordSource = "QryCtrMainStoreForPcr"

but it errors, saying you cant change the recordset in preview mode (so thats the bit that it needs to be in design for)
Ah well, looks like ill set up a new report then... shame!
Thanks anyway!

 

[boblarson]

Ah well, looks like ill set up a new report then... shame!
Thanks anyway!

Things keep getting better with the different versions, but it isn't completely perfect yet. Keep hoping