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

Change subform recordsource query on click event?

stonward
100+
P: 145
I'm trying to change the recordsource of a subform using a simple button click: by default, the subform's source is one query, when a button is clicked it changes to another query (the same query, but this time also showing stock items with less than 1 in stock...).
Having tried all manner of methods, then having read previous posts, i see the problem may be related to link criteria? Trouble is, i have no link criteria...my subform is not directly linked to the main form - if I do that, then my search facility fails!
I can get around this by using a listbox, instead of a subform (datasheet view), or by having two subforms and doing the old visible.false thing, but both strike me as a bit of a cop out? I like the appearance and navigation properties of a datasheet, so if I can use that i'd like to.

Is there a way i'm missing?
Apr 23 '10 #1

✓ answered by robjens

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click()
  2. Dim Query1 as String, Query2 as String
  3. Query1 = "SELECT * FROM Table1;"
  4. Query2 = "SELECT * FROM Table1 WHERE Parameter = True;"
  5.  
  6. If Me.Subform1.Form.RecordSource = Query1 then
  7. Me.Subform1.Form.RecordSource = Query2
  8. Else 
  9. Me.Subform1.Form.RecordSource = Query1
  10. End If
  11.  
  12. End Sub
Something like that? Please provide a bit more info so I can specify.

Share this Question
Share on Google+
2 Replies


P: 37
Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click()
  2. Dim Query1 as String, Query2 as String
  3. Query1 = "SELECT * FROM Table1;"
  4. Query2 = "SELECT * FROM Table1 WHERE Parameter = True;"
  5.  
  6. If Me.Subform1.Form.RecordSource = Query1 then
  7. Me.Subform1.Form.RecordSource = Query2
  8. Else 
  9. Me.Subform1.Form.RecordSource = Query1
  10. End If
  11.  
  12. End Sub
Something like that? Please provide a bit more info so I can specify.
Apr 23 '10 #2

stonward
100+
P: 145
Hi Robjens,

Thanx for the reply - I've already tried something similar, by calling a string variable (strSQL) then entering the SQL from the query - but the query is a little too complicated for me to unravel accurately (stripping the semicolon, alternating quote marks etc). Gonna check and copy SQL - try your method, and get back to you asap. Thanks again!

stonward
Apr 23 '10 #3

Post your reply

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