How to filter data with checkbox - MS Access?

What do your table(s) look like?

@aikimark, what do you mean by what they look like? I just want to know a simple a vba expression. They are one to many relationship.

what do you mean

I don't know what you've named your controls nor what fields (names) are connected to the controls.  I don't know if you have checkbox controls that aren't related to filtering.

I just want to know a simple a vba expression

You're asking for VBA code.  This isn't going to be something as simple as an expression.  Any VBA code will need to iterate controls and construct all, or part of, a WHERE clause.

@airimarks where do I find the name of control?. I understand checkboxes have a control source, that points to the field name in a table. I don't think its necessary to know the names. As you could use an example such as field nameA, fieldnameB, and fieldnameC. I could plug and play later on

OOPS!  Sorry, meant to use code tags....

Also, you'll probably get tips like "use -1 for True and 0 for False", etc.  There are several ways to do the same thing....

Did you create this database and these forms?

In design mode, press F4 and find properties from the resulting dialog.

Thanks Mark, I  will try out your solution right now.

Thank you  Mark Edwards for your help, it worked perfect!

Thank you Mark :)

Glad to help!

@Mark Edwards

Sent you a private message, maybe you can review my code...Thanks

ok I'll take a look at it.

Here's your code:

Private Sub Refilter()

Dim strFilterString As String

'BUILD THE FILTER STRING:
If Form_002_Criteria.CKNFixer = True Then
    strFilterString = strFilterString & " OR [NFixer] = True"
End If

If Form_002_Criteria.CkSun = True Then
    strFilterString = strFilterString & " OR [TolerantSun] = True"
End If

'REMOVE LEADING " OR " ON FILTER STRING:
If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)

'APPLY FILTER STRING TO Form_003_Species.ListSciName:
If strFilterString <> "" Then
    Form_003_Species.Filter = strFilterString
    Form_003_Species.FilterOn = True
Else
    Form_003_Species.Filter = ""
    Form_003_Species.FilterOn = False
End If

End Sub

Select allOpen in new window

What is your filter string look like?  put a breakpoint on "If strFilterString <> "" Then" and see what the value of strFilterString is.  If that doesn't make it obvious, then we could have any number of issues.

@Joseph

CKNFixer and  CkSun are both names of checkbox controls on your form.  See how simple this could have been?  No need for private messages.

@aikimark

I am new to learning Ms access.

I built a test form based on your code (my form and control names) and the code below works for me (both forms are open to start with):

Private Sub Command4_Click()
    Dim strFilterString As String
    If Me.chk1 = True Then
        strFilterString = strFilterString & " OR [FolderSizeDenied]=True"
    End If
    If Me.chk2 = True Then
        strFilterString = strFilterString & " OR [SubfoldersDenied]=True"
    End If
    If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)
    If strFilterString <> "" Then
        Forms("CheckBoxFilterTest2").Filter = strFilterString
        Forms("CheckBoxFilterTest2").FilterOn = True
    Else
        Forms("CheckBoxFilterTest2").Filter = ""
        Forms("CheckBoxFilterTest2").FilterOn = False
    End If
    Forms("CheckBoxFilterTest2").Requery
End Sub

Select allOpen in new window

the last line where the form is requeried is extra.  The  code still works without it.

This doesn't tell me anything.  I need you to check the value of the variable "strFilterString" when it hits that line.  Dop you know how to do that?  If not, add the following message box line before the breakpoint and clear the breakpoint (the message box will do the job of the breakpoint.

MsgBox strFilterString

Select allOpen in new window

 We haven't changed the Filter property of the form yet at this point.

by the way, the sample code I gave you ASSUMES that Form B is already open when you set the filter.

@Mark Edwards

Thank you, I sent you the file to get a better look at, I am new to access.

Why not post the database in this thread so that all the experts can look at it?

o.k.  I see what you are doing.  You are trying to filter a LISTBOX with a query as a rowsource on your form instead of filtering the form itself.  That's a VERY IMPORTANT difference.

Since I don't see any other code affecting the listbox or its rowsource, the code is a changed to set the listbox rowsource instead of the form's filter property.

    If strFilterString <> "" Then
        Forms("003_Species").ListSciName.RowSource = "SELECT * FROM [qSciName] WHERE " & strFilterString
    Else
        Forms("003_Species").ListSciName.RowSource = "qSciName"
    End If

Select allOpen in new window

@Mark Edwards,

thanks...I hope it is correct now

Private Sub BtnPotential_Click()

    Dim strFilterString As String

    If Me.CKNFixer = True Then
        strFilterString = strFilterString & " OR [NFixer] = True"
    End If

    If Me.CkSun = True Then
        strFilterString = strFilterString & " OR [TolerantSun] = True"
    End If

    If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)
    
    If strFilterString <> "" Then
        Forms("003_Species").ListSciName.RowSource = "SELECT * FROM [qSciName] WHERE " & strFilterString
    Else
        Forms("003_Species").ListSciName.RowSource = "qSciName"
    
    If strFilterString <> "" Then
        Forms("003_Species.ListSciName").Filter = strFilterString
        Forms("003_Species.ListSciName").FilterOn = True
    Else
        Forms("003_Species.ListSciName").Filter = ""
        Forms("003_Species.ListSciName") = False
    End If
    Forms("003_Species.ListSciName").Requery
    
End Sub

Select allOpen in new window

Please delete the old code as it is no longer applies:

If strFilterString <> "" Then
        Forms("003_Species.ListSciName").Filter = strFilterString
        Forms("003_Species.ListSciName").FilterOn = True
    Else
        Forms("003_Species.ListSciName").Filter = ""
        Forms("003_Species.ListSciName") = False
    End If
    Forms("003_Species.ListSciName").Requery

Select allOpen in new window

@Mark Edwards

I got the following error message

I think I know why, I have to change the current private sub which is a btn to private sub filter()

If the form is not open, then you'll get that error message.

I've got to go to work now.  I'll be back tonight.

I don't receive an error message, but coding is not doing anything at all,

Private Sub Refilter()
    
    Dim strFilterString As String

    'BUILD THE FILTER STRING:
    If Forms("002_Criteria").CKNFixer.ControlSource = True Then
        strFilterString = strFilterString & " OR Forms!002_Criteria.NFixer = True"
    End If
    
    'REMOVE LEADING " OR " ON FILTER STRING:
    If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)
    
    'APPLY FILTER STRING TO 003_Species:
    If strFilterString <> "" Then
        Forms("003_Species").ListSciName.RowSource = "SELECT * FROM [qSciName] WHERE " & strFilterString
    Else
        Forms("003_Species").ListSciName.RowSource = "qSciName"
    End If

End Sub

Select allOpen in new window

o.k. I guess I'm going to have to do it for you.  Send me the file and I'll mess with it for a few minutes, but that's all.

@Mark Edwards

I just emailed you the file...Thank you

o.k.  we need to build a filter string that filters your listbox rowsource query qSciName.  When you check a checkbox on form 002_Criteria, what filter do you want to apply to the fields in the query 002_Criteria.  It should look something like:

If Forms("002_Criteria").CKNFixer.ControlSource = True Then
        strFilterString = strFilterString & " OR  qSciName.[FieldNameHere]='XYZ'"
End If

I don't know what you're trying to look for in your query by checking a checkbox on form 002.

Please post the database in this thread

@Mark Edwards

    If Forms("002_Criteria").CKNFixer.ControlSource = True Then
        strFilterString = strFilterString & " OR qSciName.SciName = True"

Select allOpen in new window

I have the code typed like this, and still does nothing.

I looks like you don't know what you are doing because you are showing that you don't know how to create filtering criteria for your query.
The field [SciName] on your query "qSciName" is a text field and you should be filtering it for something.

Try and describe in your own words what you are wanting to do with the qSciName query data if a checkbox is checked.

@Mark Edwards

pop quiz: What values can a checkbox control have?

Please respect my wish

You did not make that 'wish' known in this thread prior to your recent comment.  That is why I kept asking you to post your database.

o.k. I'm going to try this again...  I've attached an image of the query grid to show you how to add the criteria for your checkboxes.  It's a little harder than usual because you want where NFixer = True if the checkbox is checked, and where NFixer is true or false if the checkbox is False

o.k. that didn't come through very well.  I'll have to figure out hope this app works so I can send you the result.

ok.  In the SQL view of your query, paste the SQL below:

WHERE (([Forms]![002_Criteria].[NFixer]=-1 and [Criteria].[nfixer]=-1) OR ([Forms]![002_Criteria].[NFixer]=0 and ([Criteria].[NFixer]=-1 or [criteria].[nfixer]=0)))
AND ((PlantPractices.Plan)=[Forms]![002_Criteria]![cboPlan])
AND ((PlantSpecies.AOI)=[Forms]![001_Selection].[ListAOI]);

The criteria simply says if the checkbox on the form is checked, then the yes/no field is True (checked or -1) or if the checkbox on the form is unchecked, then the yes/no field is 0 or -1 (False or True)

I got it to work on your file.

@Mark

What is the numeric value of vbChecked?

@Mark Edwards

I did the where SQL statement you stated. When I add the other check boxes, it causes the list box to not display the data even when it is unchecked.

@aikimark

Thank you for assisting me :)

Joseph.  Are you running now ok?

No still same issue. It works ok with the first check box, but as soon you add in more fields into query to interact with the other check box, it substracts from the scientific name until nothing is showing in the list box.

I'm assuming that you used the same SQL format on the other checkboxes.  The issue may be with how the criteria works together.  Possibly a misuse of "and" or "or".  Post your WHERE SQL statement of your query and I'll take a look (or the whole SQL of your query from the query SQL view if that's easier)

The other check boxes aren't code, trying to work on getting the first check box setup. I just been doing what you have instructed me so far.

The sql statement would be in the query of qSciName

"The other checkboxes aren't code"  What does this mean?
I forgot to mention that we don't need the code anymore because we are putting the filtering in the query.
The WHERE statement I provided above should work perfectly in the query.  Replace the WHERE clause in the SQL view of your query with the one above.

Do you know how to copy and paste the WHERE clause above into the SQL view of your qSciName query?
I've got your app working and filtering as you wanted with it.  Don't the other checkboxes work the same way?

I tried that method you provided, it worked perfectly for only check box. It substracts, as soon as you add more fields in the query. There are a total of 545 species, it only shows 5 species. Even when nothing is unchecked. Even without the code, that is one of the first methods I tried to do, and I was like maybe it needs vba

Copy and paste the SQL of your query as you have it now in a post here and I'll try to get it to work on the copy of your query that I have here.

@Mark Edwards

Thank you again for your help :)

SELECT PlantSpecies.PlantSpeciesID, PlantSpecies.SciName, PlantSpecies.ComName, PlantPractices.PlantRate, PlantPractices.PlantUnit

FROM (Criteria INNER JOIN (ConservationPlan INNER JOIN PlantPractices ON ConservationPlan.PlanNumber = PlantPractices.Plan) ON Criteria.SciName = PlantPractices.SciName) INNER JOIN PlantSpecies ON Criteria.SciName = PlantSpecies.SciName

WHERE (

(([Forms]![002_Criteria].[NFixer])=-1) AND ((Criteria.nfixer)=-1) 
AND (([Forms]![002_Criteria].[TolerantSun])=-1) AND ((Criteria.TolerantSun)=-1) 
AND (([Forms]![002_Criteria].[TolerantShade])=-1) AND ((Criteria.TolerantShade)=-1) 
AND (([Forms]![002_Criteria].[TolerantWind])=-1) AND ((Criteria.TolerantWind)=-1) 
AND (([Forms]![002_Criteria].[TolerantSalt])=-1) AND ((Criteria.TolerantSalt)=-1) 
AND (([Forms]![002_Criteria].[Pollinator])=-1) AND ((Criteria.Pollinator)=-1) 
AND (([Forms]![002_Criteria].[Native])=-1) AND ((PlantSpecies.Native)=-1) 
AND (([Forms]![002_Criteria].[Introduce])=-1) AND ((PlantSpecies.introduce)=-1) 
AND ((PlantPractices.Plan)=[Forms]![002_Criteria]![cboPlan]) AND ((PlantSpecies.AOI)=[Forms]![001_Selection].[ListAOI])) 

OR (

(([Forms]![002_Criteria].[NFixer])=0) AND ((Criteria.nfixer)=-1 Or (Criteria.nfixer)=0) 
AND (([Forms]![002_Criteria].[TolerantSun])=0) AND ((Criteria.TolerantSun)=-1 Or (Criteria.TolerantSun)=0) 
AND (([Forms]![002_Criteria].[TolerantShade])=0) AND ((Criteria.TolerantShade)=-1 Or (Criteria.TolerantShade)=0) 
AND (([Forms]![002_Criteria].[TolerantWind])=0) AND ((Criteria.TolerantWind)=-1 Or (Criteria.TolerantWind)=0) 
AND (([Forms]![002_Criteria].[TolerantSalt])=0) AND ((Criteria.TolerantSalt)=-1 Or (Criteria.TolerantSalt)=0) 
AND (([Forms]![002_Criteria].[Pollinator])=0) AND ((Criteria.Pollinator)=-1 Or (Criteria.Pollinator)=0) 
AND (([Forms]![002_Criteria].[Native])=0) AND ((PlantSpecies.Native)=-1 Or (PlantSpecies.Native)="0") 
AND (([Forms]![002_Criteria].[Introduce])=0) AND ((PlantSpecies.introduce)=-1 Or (PlantSpecies.introduce)="0") 
AND ((PlantPractices.Plan)=[Forms]![002_Criteria]![cboPlan]) AND ((PlantSpecies.AOI)=[Forms]![001_Selection].[ListAOI]));

Select allOpen in new window

Joe:  Your missing the table and field name that goes with CkSoil.  I'm going to leave that out for now in anticipation that it's not suppose to be in the query.

If it is, let me know the table.field

@Mark Edwards

Thanks for asking yes is suppose be left out for now, later on I will check that. Same thing for Water and Irrigation.

Leave off water and irrigation for now?

@Mark Edwards

Yuppie, I have to locate the correct and enter data for those three.

ok here it is.  paste this into your query (after you make a backup) and test it.  I did and it filtered perfectly!  You can add the yes/no fields to the end of the query SELECT and watch the results of the querying.  I copied it directly from the SQL view of the query.

SELECT PlantSpecies.PlantSpeciesID, PlantSpecies.SciName, PlantSpecies.ComName, PlantPractices.PlantRate, PlantPractices.PlantUnit
FROM (Criteria INNER JOIN (ConservationPlan INNER JOIN PlantPractices ON ConservationPlan.PlanNumber = PlantPractices.Plan) ON Criteria.SciName = PlantPractices.SciName) INNER JOIN PlantSpecies ON Criteria.SciName = PlantSpecies.SciName
WHERE ((PlantPractices.Plan)=[Forms]![002_Criteria]![cboPlan])
AND ((PlantSpecies.AOI)=[Forms]![001_Selection].[ListAOI])
AND (
(([Forms]![002_Criteria].[NFixer]=0) OR ([Forms]![002_Criteria].[NFixer]=-1 AND Criteria.NFixer=True))
AND
(([Forms]![002_Criteria].[CkSun]=0) OR ([Forms]![002_Criteria].[CkSun]=-1 AND Criteria.TolerantSun=True))
AND
(([Forms]![002_Criteria].[CKShade]=0) OR ([Forms]![002_Criteria].[CKShade]=-1 AND Criteria.TolerantShade=True))
AND
(([Forms]![002_Criteria].[CKWind]=0) OR ([Forms]![002_Criteria].[CKWind]=-1 AND Criteria.TolerantWind=True))
AND
(([Forms]![002_Criteria].[CKSalt]=0) OR ([Forms]![002_Criteria].[CKSalt]=-1 AND Criteria.TolerantSalt=True))
AND
(([Forms]![002_Criteria].[CKIrrigation]=0) OR ([Forms]![002_Criteria].[CKIrrigation]=-1 AND Criteria.TolerantWaterLog=True))
AND
(([Forms]![002_Criteria].[CkPollinator]=0) OR ([Forms]![002_Criteria].[CkPollinator]=-1 AND Criteria.Pollinator=True))
);

The only thing I don't know is if it should be "AND" or "OR" between the checkbox filters.  The way it is written, it will add additional criteria filtering to a record.  Change to "OR" if you want any record that has at least one of the fields checked.

Forgot to mention, that is with the code I copy I pasted.

Joe:  How are you getting your screenshots in your posts?

When you say "code" I assume you mean "SQL" that you have in the SQL view of your query, not any code in your Click event - that should be deleted - no code.

I'm getting all 545 records in the listbox with everything unchecked.

WHOA!  That's a lot of SQL!  (Access likes to change SQL) But it should work.  I get all 545 records in the listbox on form 003.
Wish I knew how to add screenshots like you do....

Can you send me the file?

I'll have to figure out how to do that.
You did delete the code on the button click event on form 002, right?

ok I sent it.... :-)

If the way Access rewrites the SQL for the query is an issue (and it looks like it could very well be - what a mess!  a lot more involved that the original few lines, right?

Now you see why I like SQL and not the Query Grid crutch.  The grid re-writes your SQL.

We can use code instead of SQL, if that will make things simpler.

Yup I deleted that code, going to see if your file does the same to me

Weird same issue on my end with your file, must be access 2007

does the form give you all 545 records in the listbox with everything unchecked?

If you are talking about the way Access's Query Grid rewrites SQL, mine did it too after I closed, saved, and reopened the query - but it ran perfectly.

I tried that multiple times

so you are saying you get NO records with everything unchecked with the same file that I get all 545 records with everything unchecked.... right?

If you are getting NO records with everything unchecked, send me the file and I'll try it here and see if anything has changed.
If I get all records and you get no records with the same file, then that's going to be interesting.....

I'm using Access 2013 from my Office 365 account.

It works perfectly on 2016, thank you....I wonder why it wont work on my computer

@Mark for the rain and elevation, would this be correct?

The text box suppose work for example, say there a value of 15 to 80.
if someone enters 20 is 20 >= 15? T or 20 <= 80? T
If someone enters 100 is 100 >= 15? T or is 100 <= 80? F


Sub ListSciNamet()
    'Rain fall range criteria
    If Not "" & Form_002_Criteria.TxtRain.Value = "" Then
        BuildFilter sFilter, "(([Criteria].[RainMax]=" & Form_002_Criteria.TxtRain.Value & " OR [Criteria].[RainMin]=" & Form_002_Criteria.TxtRain.Value & "))"
    Else
   
    End If
   
    'Elavation feet
    If Not "" & Form_002_Criteria.TxtElv.Value = "" Then
        BuildFilter sFilter, "(([Criteria].[ElevMax]=" & Form_002_Criteria.TxtElv.Value & " OR [Criteria].[ElevMin]=" & Form_002_Criteria.TxtElv.Value & "))"
    Else
   
    End If
End Sub

I have no idea, it's not my project and I don't know what you're requirements are.

It looks like the amount of filtering is more than the Access query grid can handle as it says "too complex" when adding more criteria.
If you want to add more filtering, then we are going to have to scrap the filtering in the query SQL and go with a VBA code solution that builds a filter string that we can apply to the listbox query.

I can't write your project for you.  I'll get the filtering code set for what we have done so far, then we're done once your listbox works.

I'll get that to you tomorrow,

Thank you, that's what I thought as well. It has to be done through vba. I have been looking for tutorials all over online. I did learn how to point to fields in vba in this experience.

I've almost got it done... :-)
I'm adding the rain fall and elevation criteria, but I have no idea what the first part was:

The text box suppose work for example, say there a value of 15 to 80.
 if someone enters 20 is 20 >= 15? T or 20 <= 80? T
 If someone enters 100 is 100 >= 15? T or is 100 <= 80? F

If that was just a generic question, you'll see how it is done when you get the file from me.

File is on its way to you...

Thank you so much for your help, Mark :)

Observations about [002_Criteria] form:

* The resulting SQL doesn't reference field names in the table, just controls on the form.
* Looks like you are hard-coding the checkbox names, rather than iterate the controls.
* The checkbox controls are bound to fields in the form's recordsource.  If you click a checkbox, it will change the underlying table.

Joe:

I took the file I sent you and ran it on my virtual machine with WindowsXP and Office 2007 (I don't have O2007 on a Win7 or Win10 machine).
It ran perfectly.

If you are copying code and SQL from the file I sent into your copy of the file, that may be where your issues are coming in - you're missing something.

I'll send a complete list of the changes I made to you via message.

No I used your file, and crashed on me. It must be my access.

It must be my access.

Try reinstalling the version of Access that is failing.  Also, not all accdb formats are compatible with Access2007.

It works perfectly on my other, not a big deal at this point. Thank you again everyone