Requery subform issue

[bbatson]

Hello,

I have created a form that allows the user to search a sales table with the option to choose 1) a sales representative and 2) a car type matching any portion of the inputted text. For example, you can choose "Joe Smith" as your salesman and type "mus" in the search box and all sales by Joe Smith selling a Mustang or a Mustafa (if such a goofy name for a car existed) would return.

The problem I am running into is with the subform on the form, which is supposed to display the results of the query. The code (below) instructs the subform to requery but it doesn't seem to be working.

Private Sub Command9_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strsql As String
 
Set db = CurrentDb
 
strsql = "SELECT [sales_rep], [sale_id], [automobile], [sale_price]" & _
"FROM automobile_sales " & _
"WHERE [automobile] Like '*' & '" & Me.automobile_search & "' & '*' AND [sales_rep]=[Forms]![Sale_Search]![associate];"
 
db.QueryDefs.Delete "tmp_qry"
Set qdf = db.CreateQueryDef("tmp_qry", strsql)
 
me.sale_search_subform.requery
 
End Sub

Any ideas as to why the final line of code (requery) doesn't seem to be working?

Thank you very much in advance!

[Stewart Ross]

...Set qdf = db.CreateQueryDef("tmp_qry", strsql)
 
me.sale_search_subform.requery
 
End Sub

Hi. The querydef has no relation to the subform. What you have done is to create a new query; doing so does not filter or alter your subform in any way. You can either (1) apply a filter to your subform, or (2) set the subform's recordsource to be your new query.

Option 1 is simpler; you just filter using the WHERE clause in line 10 without the word WHERE. To do so with a string called strWhere created from your current WHERE clause (but without the word WHERE!) you would set the filter property of your subform as follows:

me.sale_search_subform.Form.Filter = strWhere
me.sale_search_subform.Form.FilterOn = True

Alternately, to set its recordsource to strSQL:

me.sale_search_subform.Form.RecordSource = strSQL

-Stewart

[bbatson]

Thank you so much. I opted to use the second option you provided, which works very well. Thank you again!