passing a string (SQL) to a report for use as RecordSource

[Eric6]

hi,

i'd like to know how i can pass a string
containing an SQL String (which varies depending on the users selection) from a form to a Report.

the SQL string needs to be used as the RecordSource of that report. I originaly planed on using OpenArgs but the openReport method does not seem to support it

how would i pass the SQL String to my report?

thank you in advance
Eric

 

[FancyPrairie]

There are 2 ways you can do it.

1. Create a global variable (i.e. Dim gstrSQL as string). And on the OnOpen event of the form set the Recordsource of the report to gstrSQL. I usually create a module that contains all of my global variables (i.e. basPublicDeclarations).

2. On the your print report button on the form, prior to executing the OpenReport command, save the SQL string to a new query (delete the old query first). Then the report's Recordsource should point to the query you create. For example, if the Recordsource of the report is assigned to qryMyReport, then the query you delete and recreate should be the query qryMyReport. Note that this method will eat up space, because Access does not reclaim the space the deleted query took up. You would have to compact the database to recover the space (won't have to do this very often though)

 

[Junior1544]

i did this a little differently... the way i did it was within the report... i'll try to explain the way i did it...

the form i have is a continuous form with filters on the top of it... depending on what filters the user put in it changes the records that get returned...

i also have a print button to call a report that will then use the same filters, and i do it like this.

in the on open event of the report i have a group of if statments to build a sql string, then make the record source in the on open event... i can provide sample code if you tell me more info, but that's basicly how i do it...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[gol4]

3 other alternatives for consideration to add to the above excellent suggestions.

1.create a hidden textbox on your form
set it to the sqlstring
me.text2.value = strsql
then in the reports_open event
me.recordsource = forms!yourformname!textboxname

2.if the tables and fields returned are consistant as most probably are since you are building a report
then only build the where condition of the sql statement and pass that.
DoCmd.OpenReport "reportname", acViewPreview, , strsql

3.add a listbox to the form
set its rowsource to the strsql
me.list1.rowsource = strsql
this give the users an idea of what records they will be returning to the report
in the reports on_open event
me.recordsource = forms!yourformname!listboxname.rowsource

good luck

 

[Eric6]

i have a form with 5 listboxes, each box corresponds to a catagory of filters from which the user can select what he wishes to display in the report...

my code on the ok button on the form generates the SQL String and opens the report...

I tried creating a global variable but since i'm not working from a VB project, i could figure out how to do it.
(i'm only using VBA that come with access 2000, if you know how to do it, could you please tell me... thanks)

in the mean time, i guess i'll have to use an invisible textbox to pass the string.

ps: recreating a query would be a great way to do it but since the user will probably run tons of reports on the system, the database would just balloon to an incredible size.

thanks for all the help

Eric

 

[Eric6]

i have a form with 5 listboxes, each box corresponds to a catagory of filters from which the user can select what he wishes to display in the report...

my code on the ok button on the form generates the SQL String and opens the report...

I tried creating a global variable but since i'm not working from a VB project, i could figure out how to do it.
(i'm only using VBA that came with access 2000, if you know how to do it, could you please tell me... thanks)

in the mean time, i guess i'll have to use an invisible textbox to pass the string.

ps: recreating a query would be a great way to do it but since the user will probably run tons of reports on the system, the database would just balloon to an incredible size.

thanks for all the help

Eric

 

[Eric6]

sorry about the double post

 

[FancyPrairie]

As I stated in my previous post, I declare all of my global (public) variables in one module (i.e. basPublicDeclarations). Just realized that I gave you the wrong syntax for creating a global variable, it should be:
Public gstrSQL as String
instead of "Dim gstrSQL as String"
Note that I started the variable with the letter "g" so that when I'm looking at my code I know that the variable is a global variable.

Also, creating global (public) variables has nothing to do with whether or not your using a VB project.

Finally, the reason I set my Report's Recordsource property to the SQL string (gstrSQL), rather then the OpenReport method shown in one of the previous posts, is because I also give the user the option of specifying how they want the reports sorted (via dual list boxes). Using this technique (sorting) may require your code to build the join clause on the fly. Therefore, not only am I building the Where clause but also modifying the From clause, etc.