Changing Record Source by Code

[cable]

I have a reports record source that I need to change via code.

The record source currently points to a temp table, and this temp table changes name (but obviously not structure). I've no idea of the possible table names so I can't create a report for each name, so I need to change the record source just before the report is run.

any ideas?

 

[jtvcs]

Two suggestions: 1) Rename variable file you're sending to report (or copy it to fixed name) then the report can use the 'fixed name'. 2) Set the RecordSource property of the report before you open it. HTH

 

[cable]

1) probly isn't do able because of conflicts with different users
2) seems what i need but how?

 

[jtvcs]

Try this (can't seem to put my fingers on the code I've used but this should be close enough to get you started)...

Reports![rpt_Vouchers].RecordSource = "Voucher-Fund02"
DoCmd.OpenReport "rpt_Vouchers", acPreview, "", ""

HTH Good luck JT

 

[cable]

mmm weird its now complaining:
Error:2451
The report name 'MonthlyUpdate' you entered is misspelled or refers to a report that isn't open or doesn't exist.

Well its not misspelled and it does exist, but how the hell can you alter the record source when its open??

stDocName = "MonthlyUpdate"

'alter reports recordsource
Reports(stDocName).RecordSource = sSQL

any ideas?

 

[jtvcs]

You're right but check out the attached file. Specifically the code in the Open event of the Report. This should get you going in the right direction. Good luck.

 

[cable]

brilliant it worked, thanks

just had to sort out my table naming.

Now all I've got to do is come up with a string search and replace, because the record source might change!

 

[jtvcs]

You're welcome. Good luck.