By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,775 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,775 IT Pros & Developers. It's quick & easy.

Requery subform issue

P: 46

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click()
  2. Dim db As Database
  3. Dim qdf As QueryDef
  4. Dim strsql As String
  6. Set db = CurrentDb
  8. strsql = "SELECT [sales_rep], [sale_id], [automobile], [sale_price]" & _
  9. "FROM automobile_sales " & _
  10. "WHERE [automobile] Like '*' & '" & Me.automobile_search & "' & '*' AND [sales_rep]=[Forms]![Sale_Search]![associate];"
  12. db.QueryDefs.Delete "tmp_qry"
  13. Set qdf = db.CreateQueryDef("tmp_qry", strsql)
  15. me.sale_search_subform.requery
  17. 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!
Mar 18 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Expand|Select|Wrap|Line Numbers
  1. ...Set qdf = db.CreateQueryDef("tmp_qry", strsql)
  3. me.sale_search_subform.requery
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. me.sale_search_subform.Form.Filter = strWhere
  2. me.sale_search_subform.Form.FilterOn = True
Alternately, to set its recordsource to strSQL:
Expand|Select|Wrap|Line Numbers
  1. me.sale_search_subform.Form.RecordSource = strSQL
Mar 18 '08 #2

P: 46
Thank you so much. I opted to use the second option you provided, which works very well. Thank you again!
Mar 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.