Change subform recordsource query on click event?

[stonward]

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?

[robjens]

Private Sub Button1_Click()
Dim Query1 as String, Query2 as String
Query1 = "SELECT * FROM Table1;"
Query2 = "SELECT * FROM Table1 WHERE Parameter = True;"

If Me.Subform1.Form.RecordSource = Query1 then
Me.Subform1.Form.RecordSource = Query2
Else 
Me.Subform1.Form.RecordSource = Query1
End If

End Sub

Something like that? Please provide a bit more info so I can specify.

[stonward]

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