Need to filter a form with 4 checkboxes against one field

[JimBeer]

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".

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

Can you help a poor man out who has run out of ideas and beer?

Thank you for considering my problem!

[Micron]

I think you mean continuous form or datasheet? Only way to tell for sure if you can't recognize the difference is to put in in design mode and check the property sheet to see what the default view is, as long as you are not using code to control that. A cf form can be made to look like a ds but the reverse isn't true. If it looks like a spreadsheet, it likely is, but like I said, it could be a cf masquerading as a ds.

I haven't studied your code yet because I'm thinking you're on the wrong path. Can you not just select the single status from a combo and filter your form on that status? You don't need much code to filter a form loaded with all records - just about 3 to 5 lines. Or if you're saying you want to filter on multiple status' then use a listbox with multi-select set to true, then you build that sql from the choices and apply it.

[ssanfu]

What a crisis!!! Out of beer. Been there, done that, hated it.

You said you were trying to filter a form, but the code is trying to set a list box record source.

Instead of trying to change the form's record source, I usually set a form filter.
Any chance you would post your dB with a few records for testing? I don't want to spend the time and probably wouldn't be able to create what you have.

You might look at this article and the code.

[Gicu]

Is this what you have in mind?
Not much coding required other than Me.Requery.

Cheers,
Vlad

[Micron]

Lots of free fish at the market today.
There's so much wrong with the posted code, and so much to not learn.

[Gicu]

@Micron, I assume you're talking about mine, care to explain please?

Cheers,
Vlad

[Micron]

Don't know what you mean. I didn't download your file if that's what you're asking. When I said posted code, I mean what's in post 1. You didn't post code, you posted a db. Hope that helps.

[Gicu]

Sorry for the misunderstanding, I saw you mentioned you didn't study the OP's code and I never heard anyone questioning Allen's code posted by Steve so the only one left was mine. Anyway, I'd be curious to hear if you think my approach is valid, seems to work OK but I never actually tried it like this before within the IN() clause.

Cheers,
Vlad

[Micron]

I had a good look at the code between posts so that's why the confusion I guess.

Personally I don't like real long (mind bending) expressions although I'm guilty of having created a few but those days are over for the most part. However, if I knew that you could use IN in a form filter, I had forgotten. I've attached what I'd probably do if sticking with the checkboxes. To explain the form part -

I start the function, then select all four checks in form design view and for the click event I enter the function name once. Same for the tag property. These properties are thus set for multiple controls all at once.
Write the function, remove the criteria you had on the query, test and correct the code as I go.
I also removed the form open (or was it load?) code in favour of just setting the form recordsource property.
Seems to work fine. I'd say your way is simpler, mine more flexible (maybe). If process requires 2 more statuses some day, I'd rather not have to tweak that already long query expression. I'd rather add the tag and click event properties to the 2 new checks and be done.
Comments or questions welcome.

Example4CheckboxFilter.accdb

[Gicu]

Looks good, I tend to use query criteria to select records in forms instead of filters as I find that filters can be a bit trickier to maintain (due to their flexibility and availability to users), for example if I apply a filter to see only statuses 1 and 2 then manually filter for something else (in a different field) when I manually remove the filter on the bottom it goes back to show all records instead of staying with 1 and 2.

Cheers,

[Gareth-Rowell]

I may be missing the point here. A completely different way to think about this. Couldn't you use the checkboxes to create a binary number and assign that number to a specific outcome? Then use that outcome to filter your table?

[Micron]

I find that filters can be a bit trickier to maintain

Can't argue with your experiences for sure. However, one could just as easily set the form recordsource from a sql statement using that method and then apply it. My personal take is that since I'd have to alter form design to add checks anyway, that's when I could just assign the tag and click event call and I'm done.

[JimBeer]

Thanks everyone for looking at this. I think you all think I am smarter than I am!

Quote: " Couldn't you use the checkboxes to create a binary number and assign that number to a specific outcome? Then use that outcome to filter your table?"

Yes I expect I could, except I don't know how. That's what my question is lol.

I don't understand much about me.query but I'm reading that article now and will see if I can apply something.

[JimBeer]

Is this what you have in mind?
Not much coding required other than Me.Requery.

Cheers,
Vlad

That's EXACTLY what I want! Trying to understand it and apply... will inform

[JimBeer]

GICU has solved the terrible issue.

The query needs an if statement to account for the on/off status of the Checkboxes. So in the STATUS field criteria box we put in this "In" statement:

In (IIf([Forms]![frmExample]![chkWatch],"1-Watch","No"),IIf([Forms]![frmExample]![chkActive],"2-Active","No"),IIf([Forms]![frmExample]![chkClosed],"3-Closed","No"),IIf([Forms]![frmExample]![chkDormant],"4-Dormant","No"))

Then each of the buttons just needs a Me.requery under the ""After Update" code (just showing one checkbox. There's a bit after that also.

Private Sub ChkWatch_AfterUpdate()
Me.Requery
End Sub

Private Sub Form_Load()
Me.RecordSource = "qry_all"
End Sub

Thank you GICU for such a simple answer!

The case is Sol-ved