How to have Search form's selection become RecordSource of a report? 2

[lastout]

I have a search form that has a bunch of unbound comboboxes from which the user selects values and then clicks a search button that builds an SQL string based on the values entered. The results are then assigned to the ControlSource of a subform. The user can execute as many different searches as they like and the results in the subform update on the fly.

The form is also intended to allow the user to be able to preview (and print if desired) a report of the current selection by clicking a PreviewReport button.

Problem is I don't know how to set the report's RecordSource and allow it to change on the fly.

As far as I know, I can't set the RecordSource of the report before the report is opened. Instead, I have to use the report's OnOpen event but how can I set the report's RecordSource in OnOpen to an SQL string in a module in the form?

If I use CreateQueryDef and set the RecordSource (not in OnOpen) to the new query name, if I then change the selection in the form to return a different set of records and then try to go to the report, I get the error message that the query object already exists.

Does anyone have any suggestions on how to make the report change it's set of records according to the selection made in the form?

Any advice is greatly appreciated. lastout (the game's not over till it's over)

 

[cmmrfrds]

Make a Public string variable in the standard module then put your sql statement in the variable. In the OnOpen event equate the record source to the string variable.

Me.RecordSource = MyVar

 

[lastout]

Thanks cmmrfrds. I tried that earlier and it didn't work. Just now I've tried again and it still doesn't work. I must be doing something wrong. When the report opens all the fields say "#Name?" because the RecordSource is still blank because strSQL is null. Any ideas? lastout (the game's not over till it's over)

 

[cmmrfrds]

Is strSQL a Public variable in the standards module????? It needs to have scope that covers both the form and report.

In the standards module
Option Explicit
Public strSQL as string

In the search form
strSQL = "select .....etc......"
'-Make sure you have a good sql string
debug.print strSQL
Docmd.OpenReport "TheReport"

Report OnOpen event
Me.RecordSource = strSQL

 

[jjob]

I think I'm currently doing this in an app for reports with simple table or query source, use the following:-

Private Sub ActualRepObjectives_Click()
On Error GoTo LocalError

Dim stDocName As String
Dim strWhere As String

stDocName = "ActualRepActivityBut11"

strWhere = getActualRepActivityWhereCondition

DoCmd.OpenReport stDocName, acPreview, , strWhere

ExitSub:
Exit Sub

LocalError:
MsgBox Err.Description
Resume ExitSub


End Sub


I also work with querydefs depending upon complexity of the original so I either

a) create a temporary query def then delete the query def after printing for more complex stuff.

or

b) opened an existing querydef for the report , stored the SQL added/amended SQL to include new Where criteria, then after printing set the original SQL back

 

[lastout]

Thanks for both of your responses.

cmmrfrds: Yes, the strSQL variable is declared Public in a standard module and the strSQL in the form module works perfectly (in the form that is, there's nothing wrong with the SQL).

jjob: The original SQL from the search form can be quite complex. There are many variations, some more complex than others.

When the user clicks the PreviewReport button I don't necessarily want it to cause the report to print right away.

All I want is for them to have the records that appear in the subform after they enter their search criteria in the search form, appear in the preview of the report after they click the preview report button.

If I use a querydef, where do I delete the previous querydef? lastout (the game's not over till it's over)

 

[nimarii]

Hi Lastout -

You mentioned the following:

I have a search form that has a bunch of unbound comboboxes from which the user selects values and then clicks a search button that builds an SQL string based on the values entered. The results are then assigned to the ControlSource of a subform. The user can execute as many different searches as they like and the results in the subform update on the fly.

do you think I could see the code you used to accomplish this? I'm guessing that you're setting the recordsource of the form equal to the SQL statement generated? Did you use a global variable to do this? I'm trying to do this as well, and can't seem to get anywhere with it. I have a search form that works, but it's extremely slow, and I'm trying to find something more efficient/faster.

I'd greatly appreciate your help with this,
nimarii

 

[hymn]

Have a look at the code I posted in the thread below if your going to use a querydef
thread702-534577

Hope this helps
Hymn