Greetings:
I have a single table of about 20 fields.
One field, fed by a combo box, is named "Status" and has four available statuses
1-Watch
2-Active
3-Closed
4-Dormant
My form is based on a query with zero filters/criteria, it shows all the data. It is either a continuous form or a tabular form, how can I tell? Does it matter?
What I want:
User should be able to click a checkbox on and off and the FORM would refresh/requery and just show the rows of any combination of the four statuses.
For example, Just the Records with STATUS of 1-Watch, Records with 3-Closed and 4-Dormant, All records etc.
It seems pretty straightforward to me but I haven't been able to find much of anything on the webz to help me how to do this.
I do have some code I found but, of course, "It Doesn't Work"
The code is just for three checkboxes, but it fails on the second to last line
Compile Error: Method or data member not found.
- Me.ListBox.RowSource = mainSQL & whereSQ
The "LISTBOX" part is highlighted by the error.
The code is on the first checkbox only, on the Event "After Update".
Can you help a poor man out who has run out of ideas and beer?Code:Private Sub Check52_AfterUpdate() Dim mainSQL As String Dim check1SQL As String Dim check2SQL As String Dim check3SQL As String Dim whereSQL As String whereSQL = "" If Me.Check52 = true Then check1SQL = "Status is 1-Watch" whereSQL = "WHERE " & check1SQL Else whereSQL = whereSQL End If If Me.Check54 = true Then check2SQL = "Status is 2-Active" If whereSQL = "" Then whereSQL = "WHERE " & check2SQL Else whereSQL = whereSQL & " AND " & check2SQL End If Else whereSQL = whereSQL End If If Me.Check52 = True Then check2SQL = "Status is 3-Closed" If whereSQL = "" Then whereSQL = "WHERE " & check3SQL Else whereSQL = whereSQL & " AND " & check3SQL End If Else whereSQL = whereSQL End If mainSQL = "Select * From qry_all " 'this will show a message box of the SQL so you can verify it MsgBox mainSQL & whereSQL Me.ListBox.RowSource = mainSQL & whereSQ Me.ListBox.Requery End Sub
Thank you for considering my problem!