Filter a Form on a Field in Subform

[Daryl]

I developed a command button that filters a form based on users input of 2-digit territory ID (in this case USA states). My problem is that when the user is done with the filter they want to remove it. I'm at a loss on how to show all records. Do I need to add another command button that just turns on the original recordsource or is there another way? Your help, as always, is appreciated. Here is the code I used for the command button.


Private Sub cboStateFilter_Click()
'Purpose: Change the form's Recordsource to only forms from the selected state
Dim sSQL As String
Dim bWasFilterOn As Boolean
'Save the FilterOn state. (It's lost during RecordSource change.)
bWasFilterOn = Me.FilterOn
'Change the RecordSource
If IsNull(Me.ActiveControl) Then
If Me.RecordSource <> "tblForms" Then
Me.RecordSource = "tblForms"
End If
Else
sSQL = "PARAMETERS State Text; SELECT tblForms.* FROM tblForms " & _
"INNER JOIN tblFormValidity ON (tblForms.Form_vdt = tblFormValidity.Form_vdt) " & _
"AND (tblForms.Form_nm = tblFormValidity.Form_nm) " & _
"WHERE (((tblFormValidity.State)=[State]))"
Me.RecordSource = sSQL
End If
'Apply the filter again, if it was on
If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If

End Sub

 

[Chris RR]

I like a "Show all records" button. The code isn't hard:
' set whatever form fields you want to null,
' makes it obvious to the user that their
' selections were wiped out
Me.cboSomefield = Null

' then just do this
DoCmd.ShowAllRecords

 

[Daryl]

Sometimes we overlook some of the simplest solutions (I didn't recall the ShowAllRecords command).
As an FYI, I still had problems ... I was still being prompted for state when I selected the ShowAllRecords button. I resolved this by adding the following line of code before the DoCmd

Me.RecordSource = "MyFormsTable"

Once again, thanks for the help.