Create a Report without defaulting a Record Source 2

[BrazilTechie]

I wonder if there is a way to setup a report and not have to hard code a report source. Instead to do assign a record source at the time of the call of the report (i.e DoCmd.OpenReport....

Can someone help me, please?

Thanks in advance.

 

[lameid]

I have successfully set the the recordsource property of a report on it's OnOpen event. You can probably do all your testing there to set the correct recordsource including checking values on a form. You can also use a global variable to assign the future recordsource and then assign the recordsource to it.

I wrote this in general terms. Let me know if you need more specific examples.

 

[Bry12345]

Lame, can you please give me an example of how you would set the global variable to the future recordsource? I have a form with 10 buttons, each of which prints an identical report but with a different recordsource. I'd like to set the recordsource as part of the OnClick Event of each button.

Thx! - - - -

Bryan

 

[lameid]

If I use global vairables in a database I make a module to house them and name it somthing like basGlobals.

Global strReporRecordsource as String

The above declares the global variable strReporRecordsource.

Because it is a global declaration and it can be used in any module at any time, it does not go insied a procedure or module.

Instead of opening a different report on each button, assign a different recordsource...

strReporRecordsource = "Query1"
DoCmd.OpenReport "Rpt1",acViewPreview

Then on the on open event of Rpt1 (or whatever your report is)...

Me.RecordSource = strReporRecordsource

 

[Bry12345]

Thanks, just what I needed. Have a star!

One followup:

I have written 10 queries and then set the RecordSource to each query, as appropriate.

Instead of defining individual queries, do you know of any way to set the RecordSource using SQL?

Thanks! - - - -

Bryan

 

[lameid]

Looks like this thread, you and thread703-478496 are closely related

You can set your recordsource to an SQL statement in code.

To get your base SQL string, open the query in design view, switch to SQL view. You want to get at that string.

So

If you have in the QBE a field1 and newField: field2
selecting from the Table Table your SQL will look something like

Select Field1, Field2 as newField From Table;


Now if you want to substitue a different field to be aliased to new field...

strReporRecordsource = "Select Field1, " & strFieldName & " as newField From Table;"

There is a lot to know about string manipulation but the piece you might not know which may bite you is that to get double quotes into a string, you just use a pair of double quotes...

debug.print "Hello "" World"
will display
Hello " World
in the debug window

debug.print """Hello World """
displays
"Hello World"

debug.print """"
Displays
"

That's got you on your way.

 

[Bry12345]

Thanks, I got it.

I was assigning the recordsource as a sql string, but I now realize it was not working because the variable to which I had been setting it was not global.

If I could give you 2 stars, I would. - - - -

Bryan

 

[BrazilTechie]

lameid, thanks for your big help.
The question presented by Bry12345 was also good and your answer was pretty good.

Thanks again.