display query result on subform

[tracktraining]

Hi ,

I have several queries that I already wrote. I am in the progress of
creating a user interface form. I want to call the queries and display the
results on a subform. I have written a short code (see below), but i keep
getting an error (Error Message: Run_time error '2467'. The expression you
entered refers to an object that is closed or doesn't exist). Is the code
below correct? Any suggestions/advices on how to make this work?

Private Sub Read_Click()
Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("SearchQuery")
qd.Parameters("Document ID") = Me.SearchDocID
Me.SearchResults.Form.RecordSource = "SearchQuery"
Me.SearchResults.Requery
End Sub


THANKS SO MUCH.
Tracktraining

 

[Allen Browne]

Your code creates an instance of the QueryDef, assigns the parameter, and
then doesn't do anything with it. Setting the RecordSource to the name of
the query doesn't use the QueryDef you created (which goes out of scope at
the end of the procedure.

It might be possible to name the parameter in the query to something like:
[Forms].[Form1].[SearchDocID]
Then just bind the subform to the query, and it will read the value from the
form.

If you always want to filter the form by the SearchDocID value, you could
simplify the whole process by omitting that parameter from the query.
Instead set the LinkMasterFields and LinkChildFields properties of the
subform control on your main form. No code is needed.

If there are actually lots of parameters you want to offer, this approach
might work:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It builds the WHERE clause of the SQL statement dynamically, and then
filters the form.

 

[tracktraining]

Thanks so much. Your database was very helpful.
--
Learning

Your code creates an instance of the QueryDef, assigns the parameter, and
then doesn't do anything with it. Setting the RecordSource to the name of
the query doesn't use the QueryDef you created (which goes out of scope at
the end of the procedure.

It might be possible to name the parameter in the query to something like:
[Forms].[Form1].[SearchDocID]
Then just bind the subform to the query, and it will read the value from the
form.

If you always want to filter the form by the SearchDocID value, you could
simplify the whole process by omitting that parameter from the query.
Instead set the LinkMasterFields and LinkChildFields properties of the
subform control on your main form. No code is needed.

If there are actually lots of parameters you want to offer, this approach
might work:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It builds the WHERE clause of the SQL statement dynamically, and then
filters the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have several queries that I already wrote. I am in the progress of
creating a user interface form. I want to call the queries and display the
results on a subform. I have written a short code (see below), but i keep
getting an error (Error Message: Run_time error '2467'. The expression you
entered refers to an object that is closed or doesn't exist). Is the code
below correct? Any suggestions/advices on how to make this work?

Private Sub Read_Click()
Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("SearchQuery")
qd.Parameters("Document ID") = Me.SearchDocID
Me.SearchResults.Form.RecordSource = "SearchQuery"
Me.SearchResults.Requery
End Sub


THANKS SO MUCH.
Tracktraining