Filter subform using a button

[Tupacmoche]

This seems straight forward enough but, I keep getting a Run-time error 3131 message: Syntax error in From clause.

Here is the code:

Private Sub btnFilter_Click()
Dim strSQL As String
strSQL = "Select * from qryOGA_Val where" & _
"Forms![OGA Management].Form![SubFilterTest].rec_Type = 'P" & "'"

DoCmd.RunSQL strSQL

End Sub

All, I've done is place a subform on a form added a query as the data source. The data source has codes type P, H, and J. What the filter button does is simple set it to one of these values. Can anyone see why, I getting this error any assistance is appreciated.

Additionally, is there a way to use this same button as a toggle so that, I can use it to un filter the subform?

 

[MrHans]

You should debug.print the SQL without executing it... You miss the space after the where part

 

[Orthodox Dave]

MrHans is right about the space after "Where". Also I see no semicolon ";" at the end of the SQL. I also see no point in
"...rec_Type = 'P" & "'"
why not
"...rec_Type = 'P'" There may be other errors in the SQL.

But more to the point, your sub doesn't actually update anything - it just runs a Select query. To change the record source of the subform, instead of DoCmd.RunSQL, you need something like:

Forms![MainForm]![SubForm].Form.RecordSource = strSQL

The parts in [square brackets] are for you to complete of course.

Then you need to requery the subform for this to take effect.

The toggle is fairly straightforward to do. First you declare and populate 2 SQL string variables - one for the record source as it is normally saved, and the other for the revised record source. Then you simply test to see which one is the current record source and change it to the other. Like this:

if Forms![MainForm]![SubForm].Form.RecordSource = strSQL1 then
Forms![MainForm]![SubForm].Form.RecordSource = strSQL2
else
Forms![MainForm]![SubForm].Form.RecordSource = strSQL1
end if