Make ADO recordset available in another form 1

[MrMerlotti]

From user input I create a select statement as a string. I then run the query as follows:

Dim rsSearch As New ADODB.Recordset
rsSearch.Open (strSearch), CurrentProject.Connection, adOpenStatic

... if multiple records are returned I need to display them in the sub-form of another form. I cannot seem to make the rsSearch recordset available in the load form event of the sub form being opened. Any ideas?

 

[PHV]

Why not setting the RowSource to strSearch ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrMerlotti]

I'm not sure how you mean. The code I'm trying to use in the on load event of the opening sub form is:

Set Me.Recordset = rsSearch

Which returns a run time error '424' object required as rsSearch is empty - which is presumably something to do with the scope of the variable. Am I coming at this from entirely the wrong direction?

 

[PHV]

Where is strSearch coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Crowley16]

there's a property called rowsource, it takes a sqlString and automatically bounds the form/list/combo... to the records returned by that sqlString

--------------------
Procrastinate Now!

 

[PHV]

Oops, I meant RecordSource

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrMerlotti]

strSearch is a string built up from user entry into a number of text boxes: For example:

"SELECT * FROM BOOKINGS WHERE postcode = '" & Me.txtPostCode.Value & "'"

It's a bit more complicated than that but it gives you the general idea.

 

[PHV]

I need to display them in the sub-form of another form
Provided the another form is already open:
Forms![another form]![sub-form controlname].Form.RecordSource = strSearch

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrMerlotti]

Many thanks!