Command button to filter records

[KLR]

Is it possible to create a simple command button that will change the
underlying query of a forms record source. E.g. click on the button
and display all records where status is "In Progress", click another
and it goes back to all records?

 

[raskew via AccessMonster.com]

Hi -
Try placing an option group in the form's header. Options might be 'All',
'Complete' and 'Incomplete'.
In the group's AfterUpdate event, copy/paste the following code, modified to
fit your scenario. Done is a Yes/No field in the underlying table.

Private Sub optSelectedBy_AfterUpdate()
Dim strSQL As String

strSQL = "Select qryTblDevProcess.* from qryTblDevProcess "

Select Case optSelectedBy
Case 1 'all
'strSQL = strSQL & "WHERE Residential=True "
Case 2 'complete
strSQL = strSQL & "WHERE Done = True"
Case 3 'incomplete
strSQL = strSQL & "WHERE Done = False"
End Select

strSQL = strSQL & ";"
Me.RecordSource = strSQL
Me![Item].ForeColor = IIf(Me![Done], 0, 255)
Me![Remarks].ForeColor = IIf(Me![Done], 0, 255)

Me.Requery

End Sub

HTH - Bob