How do you use multiple checkboxes to filter a form?

[Lynch225]

Hey all,

Say I have a form with 10 different checkboxes (named Check1, Check2,...) that are based on the numbers 1-10. The user will select different checkboxes and then click a button (named Update) that will open a split form (named FormUpdate) based on a query. This form needs to be filtered to where the numbers that were checked equal the same numbers of the Control field named "Number".

For instance, the user checks numbers 2, 3, and 6. Is it possible to filter the form so only the records 2, 3, and 6 of the Column "Number" are showing?

Thanks, I've scrounged the internet and my VBA books and can't seem to find any information that is working for me.

-Ben

[C CSR]

Here's a subroutine that will render a query based on which checkboxes are checked when the button is clicked. This is just one way do do this.

The checkboxes are assigned the Record IDs as "Default Values" in properties. The control value is either true or false depending on whether or not the box is checked. The Control Type for a "checkbox" is 106. If you have other checkboxes that do not relate to the records you descibed for the form then you'll have to modify the code:

Option Compare Database
Option Base 1
 
Private Sub Command6_Click()
Dim Narr() As Variant
i = 0
For Each Ctrl In Form.Controls
    If (Ctrl.ControlType = 106) Then
        ThisChkbox = Ctrl.Value
        If ThisChkbox Then
            i = i + 1
            ReDim Preserve Narr(i)
            Narr(i) = Ctrl.DefaultValue
        End If
    End If
Next
 
If i > 0 Then
    For j = 1 To UBound(Narr) - 1
        RecsChose = RecsChose & "arrChkbox.Rec_ID=" & Narr(j) & " OR "
    Next
    RecsChose = RecsChose & "arrChkbox.Rec_ID=" & Narr(j) & ";"
    sqlP1 = "SELECT arrChkbox.Rec_ID, arrChkbox.TheInfo FROM arrChkbox WHERE "
    sqlStr = sqlP1 & RecsChose
Else
    ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
End If
End Sub
 
 

Then you would assign your other form with the resulting query. Do you know how to do that or need further explanation? Let me know if you hit a snag.

[C CSR]

I took a minute to go ahead and throw in some explanation. The table "arrChkbox" only has 2 fields. 1) Rec_ID (as a number 1,2,3,etc.) , and 2) "TheInfo" (text) which just represents some data you would have associated with each record (you probably have more). For each Checkbox I opened it properties sheet and set the "Default Value" = to the Rec_ID that it represents. That's all.

I selected the command button's "Onclick" event and used the code builder to write my code. You also have to put above the procedure "Option Base 1" because in this case I wanted to set up an array whose 1st index would be 1 instead of 0. I used the forms "Control Collection" to find the checkboxes in the "For Each" loop. If the value (which is different from the Default Value) was true (checked) then I put its Default Value into the "Narr" array, so that when I was finished I would have temporarily stored only the items checked (their Rec_ID's). Then I know exactly how many parameters there would be in the WHERE clause of my query (in this case I just use Rec_ID = this OR that, OR etc., etc.).

Backing up for a second, I originally declared my "Narr" array without specifying its dimension (because I wouldn't know how many boxes would be checked in advance) which gave me the ability to expand it if I found a box that was checked. That's the "Redim" declaration. I used Redim "Preserve" so that as I added a dimension for the next box that was checked it would not erase what I had already put into the array. Redim by itself destroys any existing data already in the array.

Next I checked to see if any box had been checked (if i > 0 because I incremented "i" in the loop above when I found the 1st checkmark) , so that If the Narr array was empty I could cancel out the action on the button (that little piece of the routine still needs for you to code it; right now you just get a message , but no query would be formed).

So, if the array is not empty the code loops through it, shy of the last entry, and compounds the criteria (RecsChose) for your query string, jumping out of the loop to attach the ending piece (last item in the array). Then the 1st Piece of the query (Select ...etc) is stuck on the front and ... ...That's about it.
Notice I didn't make all the declarations as I should have (forgive me) so clean it up. And I'm sure some other wild cowboy has got another way to do this. But finally, just change the Query definition to include the fields you need to reflect the data you intend to show in your subform, splitform, whatever.

I assume at the end of my code you would take the "sqlStr" (which is the final un-dramatic result) and make it into the "WhereClause" for a Docmd OpenForm operation, or something like that to present the results you need. -- So, Have a nice Day?

P.S.

If you're gonna use the sqlStr for the WhereClause in a Docmd.OpenForm, I think you have to break off the first part of that string (look that up).

[Lynch225]

Wow, I thinked I've learned more about VBA in your post than I have in any other post. Thank you!

I am having an issue with the following line in the code:

ThisChkbox = Ctrl.Value

I am getting a Run-time error 91: Object variable or With block variable not set. I tried to set it as an object and I got the same error. Forgive me, I'm still very new to VBA.

[C CSR]

Well, look at the bright side--you get to learn some more stuff.

Let me check a couple of things: 1) Are you using this code in MS Access (the title said "Access"--what version)?

Your procedure name should reflect the name of the button name (i.e.; "YourButtonName_Click()") and this should be in the Form's Class Module, which should have opened automatically when you used the buttons event property line to open the code builder.

Cut and paste your code back to me (put it inside the blocks using the # button in the Reply toolbar, like you just did). I just want to dbl-chk it.

Anyway, something's fishy because the code works good here.

PS. Forgot to mention that the error is indicating that we didn't declare and set a reference to the Form Object. I need to find out why it works here and not there.

[C CSR]

Also, try making an "Explicit" reference to the forms controls like this:

For Each Ctrl In Forms!MyFormName.Controls
'instead of 
 
For Each Ctrl In Form.Controls

Here's a docmd line that'll work and open the form in read-only (you can change the read-only part). It uses the whole query "sqlStr" instead of just the WHERE Clause.

DoCmd.OpenForm "MySplitFormName", acNormal, sqlStr, , acFormReadOnly, acWindowNormal

Put it at the bottom in the same Sub.

[Lynch225]

Yes, it is an access form and I updated the button, form, and field names as such:

Private Sub Update_Click()
 
Dim Narr() As Variant
 
i = 0
For Each ctrl In Forms!Check.Controls
    If (ctrl.ControlType = 106) Then
        ThisChkbox = ctrl.Value
            If ThisChkbox Then
            i = i + 1
            ReDim Preserve Narr(i)
            Narr(i) = ctrl.DefaultValue
            End If
    End If
Next
 
If i > 0 Then
    For j = 1 To UBound(Narr) - 1
    RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & Narr(j) & " OR "
    Next
    RecsChose = RecsChose & "Trailers_Unloading_LA.Door" & Narr(j) & ";"
    sqlP1 = "SELECT Trailers_Unloading_All.Door, Trailers_Unloading_All.Trailer_Number FROM arrChkbox WHERE "
    sqlStr = sqlP1 & RecsChose
    Else
    ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
End If
 
DoCmd.OpenForm "UnloadNow", acNormal, , sqlStr
 
End Sub
 

Any input will be helpful, thanks again!

[Lynch225]

Oops Line 22 should be:

    sqlP1 = "SELECT Trailers_Unloading_All.Door, Trailers_Unloading_All.Trailer_Number FROM Trailers_Unloading_All WHERE "
 

And after fixing line #6 with your newest post, I am getting a run-time error 2424: The expression you entered has a field, control, or property name that Microsoft Access can't find.

This is occurring on the same line as stated earlier

[C CSR]

Let's do this to cover the bases. Under the Narr declaration in the top of the sub:

Dim Ctrl As Object, i, j, RecsChose, sqlP1, sqlStr, ThisChkbox, ErrMess
ThisChkbox = 0
 

Everything is declared now (especially "Ctrl as Object"). Also, your "sqlStr" is in the wrong spot (make it the 3rd parameter in the docmd line, Not the 4th).

I've tested it here with and without some of the changes we're making and I can't break it to the Error Message you're getting (but I know what its supposed to mean). Try again....

[C CSR]

Note: in the line below, "Check" should be the name of the form, not a control or option group control.

For Each ctrl In Forms!Check.Controls 
 

Your not using an "Option group" for your checkboxes right?

[Lynch225]

I'm still getting the same run-time error 2464 with the following line highlighted:

ThisChkbox = Ctrl.Value

I'm using Access 2007 (don't know if this makes a difference). Is there any other verbage I can use?

Also, Check is the name of the form as well. I'm not sure what the option group is for checkboxes, but I'm pretty sure I'm not using them.

[C CSR]

We've got some other issue. Are you "saving" and then "compiling" the code? If not, make sure you code looks good and click on "Debug" on the menubar, the click "Compile...[your dbname]". If no error, then try the form again.

We may need to check the "References" from inside the Visual Basic window. On the same "Menubar" click on Tools>References. You should at least have checked at the top:

1)"Visual Basic for Applications"

2)"Microsoft Office 12.0 Object Library"

3)"OLE Automation"

4)"Microsoft Office 12.0 Access engine database Object Library"

Another question: I assume you have one form with the checkboxes all by itself, and you want to open a separate SplitForm. Correct?

Let me know. We'll get it fixed--but its not the code. We could rewrite it another way, but we're avoiding an issue that'll come back at you. I'm looking into any special circumstance relating to the "Value" property, as a property, and issues regarding access to that.

Again, It all works here (I'm in Access 2007). Let me know.

[C CSR]

Not related to above error, but you are missing an "=" sign in the following line:

    RecsChose = RecsChose & "Trailers_Unloading_LA.Door" & Narr(j) & ";" 
 
 

Put "Door="

[Lynch225]

Did the above suggestions and am still getting the same error on the same line. Is there a way to use something other than Thischkbox? And yes, you are right about the 2 different forms.

[C CSR]

There's always a less than pretty method that'll work, but you'll lose flexibility for adding or taking away controls. I'll rethink it. I did some research and the use of the "control.value" for checking forms is constantly used for other applications, and it has issues that can usually be resolved. Do a couple of other things while I recompose. We've hacked away on this thing, so clear your mind and first read all of the following and then tediously proceed as follows:

1) Most importantly: Originally you got a [Run-time error 91: "Object variable or With block variable not set"] on the "Ctrl.Value" line. You made a change to [For Each Ctrl In Forms!Check.Controls] and then you said the error changed to [run-time error 2424: "The expression you entered has a field, control, or property name that Microsoft Access can't find"]. Put the following line in instead of the others and retest:

 For Each Ctrl In Me.Controls
 

Make sure the "Ctrl" variable is declared (i.e., Dim Ctrl as Control, NOT as Object). [Save, Compile & Test]. NOTE the error message if any for this exact condition.

2) Compact and Repair the Database: Go to the Office Button (top left), click "Manage" and "Compact and Repair." [test code]

3) If we're still broke, Change the name of the Main form (and reassure me that its not a subform of some other form); change it to "Check1" for example (and make sure that no table or query has the same name). The actual name of the form should NOT appear anywhere in the code. [save, compile and test]

4) The Checkbox controls should not be bound to an underlying table: In Form Design View click each check box (not the label), look at its properties sheet, look on the "Data" tab; make sure the "Control Source" line is Blank. [save and test].


5) Lastly, make sure that the "References" you looked up from the module design toolbar are checked, but do NOT say "MISSING."

I'll hack out a work-around for fun. At least that way you can proceed with other design issues. Whew!

[Lynch225]

Haha even after all those changes/ suggestions, I am still getting the same error on the same line. When I was first looking into doing this, I was experimenting with a code that is similar to this:

Private Sub Update_Click()
Dim Narr() As Variant
Dim i as Integer
Dim ctrl as Control
i = 1
For Each ctrl In Me.Controls
    If Me.("Check" & i) = True Then
          ReDim Preserve Narr(i)
          Narr(i) = ctrl.DefaultValue
          i + 1 = i
    End If
Next
 
If i > 0 Then
    For j = 1 To UBound(Narr) - 1
    RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & Narr(j) & " OR "
    Next
    RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & Narr(j) & ";"
    sqlP1 = "SELECT Trailers_Unloading_All.Door, Trailers_Unloading_All.Trailer_Number FROM Trailers_Unloading_All WHERE "
    sqlStr = sqlP1 & RecsChose
    Else
    ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
End If
 
DoCmd.OpenForm "UnloadNow", acNormal, sqlStr
 

This exact code isn't working for me right now, but I was wondering if something similar could be used to loop through the controls.

[Lynch225]

Oh boy C CSR, I was sitting here banging my head against the table when something hit me to try and redo everything on a new form. As I was doing this, I remember that my original form was for some reason opened as a modal dialog form. Now everything seems to be workimg alright until I get to the actual open form command. Now I am getting a Run-time erro 3464: Data type mismatch in criteria expression with final line highlighted:

DoCmd.OpenForm "UnloadNow", acNormal, sqlStr, , acFormReadOnly, acWindowNormal
 

[Lynch225]

The form UnloadNow is a split form based on the query Trailers_Unloading_LA. If the sql portion of the code, would I have to reference the original tables? Or is that not the issue?

Again forgive my ignorance and sorry for wasting all of your time earlier when you were right with the first code

[C CSR]

Hey Lynch, I knew the code was good, but what's really fantastic is that you went one last extra step and found the missing configuration monster! Kudo's! You just inherited the Earth.

Now back to work. I was trying to avoid that string loop at all cost because it's just bad practice. Modifications can be a nightmare if you ever have to make them.

I'll look the openform issue and get back to you [tied up]. Again, Congratulations!

[Lynch225]

Well I got the form to open without an error message (finally) by messing around with the filter part of the open form command. While looking at different references, I didn't see any that included the SELECT portion of the sql statement. I removed some of the code so lines 18-27 of your first code now look like:

If i > 0 Then
    For j = 1 To UBound(Narr) - 1
        RecsChose = RecsChose & "Door=" & Narr(j) & " OR "
    Next
    RecsChose = RecsChose & "Door=" & Narr(j) & ";"
    sqlStr = RecsChose
    Else
    ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
End If
 

The only issue is that it's not filtering haha. Soo, that's where I'm at now...

[C CSR]

Here again, the following Docmd works great for me. Let's start back with this.

DoCmd.OpenForm "Check1split", acNormal, sqlStr, , acFormReadOnly, acWindowNormal
 

In my table I only have 2 fields:

1) Recs_ID, type: number (which I used for the "Default Value," 1 thru 10 for each of my checkboxes).

2) TheInfo, type: text

So when I build the query in the code, after the "WHERE", my criteria would be the "Default Value" of the checked boxes that I looped through in the Array, and they correspond to the tables Recs_ID. Here's my sqlStr result from the loop through the Array and all the concatenation:

SELECT Check1.Rec_ID, Check1.TheInfo FROM Check1 WHERE Check1.Rec_ID=1 OR Check1.Rec_ID=3 OR Check1.Rec_ID=4;
 

I only used 4 checkboxes and left box 2 unchecked, so you see 3 conditions instead of 4 in the WHERE Clause above. My Split form is preset to Check1 in the property sheet for the "Row Source." The fields in my result (Recs_ID and ThisInfo) match the fields in the my Check1 table that I want to appear in the split form. *** If I open the Split Form all by itself it opens to all the records in Check1.

Here's a way to test the query built in the code to determine what it should look like after the loops and concats.

Close your forms and Create a new Query based on the Check1 table (or whatever the name of your table is). Put all the fields you want in your result in the query-grid, and set the criteria of your "Recs_ID" (or whatever fieldname you're using that contains the value refered to as the "DefaultValue" in your checkboxes(EXAMPLE: 1 OR 2 OR 4 OR 9); do you see what I mean. Then run the query and make sure it works to provide you the output you'd want if you had checked certain boxes in the form.

Now, in the query's Design View, select at the top the tab that says "Design", then click the down arrow on the toolbar icon at the top left that says "Views" and select SQL. A window opens; Copy that query string to NotePad or something.

Back in your code you need to put in a "Debug.print" command somewhere at the bottom before the Docmd line like this:

Debug.Print sqlStr
DoCmd.OpenForm "Check1Split", acNormal, sqlStr, , acFormReadOnly, acWindowNormal
 

[Note here that "sqlStr" in the Debug command is the variable I assigned all that concatenation to and created my query string]. Then, put your cursor in front of the Docmd line and hit F9. This sets a "break" so we can test the code in action. Last, save the file, compile it, and close it.

A couple of more steps: Open your "Check1" form, check a box or two, click the button and the code should pop up on the "break" we just set. NOW. hit Ctrl-G. This opens the "Immediate" window and your SQL string will be sitting right there as output from the "Debug.Print" line above the break. Copy and past it into the NotePad and compare the two query strings, one from the query you designed and this one from the code.

Note that Access may have added some extra parenthesis, but just look at it carefully as compare it to mine (herein above) for syntax redundancies. Or copy them both to a Reply and let me look at them. I'm standing by at this moment.

[Lynch225]

Hey C CSR, I am still having the same issue. I should have mentioned that the form "UnloadNow" is based on the query called "Trailers_Unloading_LA" and not a table by the same name. I was looking into the sql code yesterday when I was experimenting with how the code should look. When I added the criteria 9 OR 10 OR 11 into design view of the query "Trailers_Unloading_LA" and opened in sql view, I got the following:

SELECT Dock_Status.Door, Dock_Status.Trailer_Number, Sorter.LA01, Sorter.LA02, Sorter.LA03, Sorter.LA04, Sorter.LA05, Sorter.LA06, Sorter.LA07, Sorter.LA08, Sorter.LA09, Sorter.LA10
FROM (Board_Unload_Doors LEFT JOIN Dock_Status ON Board_Unload_Doors.Door = Dock_Status.Door) LEFT JOIN Sorter ON Dock_Status.Trailer_Number = Sorter.TRLR_NBR
WHERE (((Dock_Status.Door)="9" Or (Dock_Status.Door)="10" Or (Dock_Status.Door)="11"))
ORDER BY Board_Unload_Doors.[dock order];
 

I tried adjusting the sqlStr and sqlPl code you gave me to look exactly like this (without the ORDER BY clause), but I got an error referring to the FROM clause with the DoCmd line highlighted again.

My next experiment was to open a new query based on "Trailers_Unloading_All" so I wouldn't have to refer to other tables and queries in the sqlPl. This also didn't work.

After doing what you recommended with the Debug line and the code in the Immediate window, I got the following code:

SELECT Trailers_Unloading_LA.Door, Trailers_Unloading_LA.Trailer_Number, Trailers_Unloading_LA.LA01, Trailers_Unloading_LA.LA02, Trailers_Unloading_LA.LA03, Trailers_Unloading_LA.LA04, Trailers_Unloading_LA.LA05, Trailers_Unloading_LA.LA06, Trailers_Unloading_LA.LA07, Trailers_Unloading_LA.LA08, Trailers_Unloading_LA.LA09, Trailers_Unloading_LA.LA10 FROM Trailers_Unloading_LA WHERE Trailers_Unloading_LA.Door=9 OR Trailers_Unloading_LA.Door=10 OR Trailers_Unloading_LA.Door=11;
 

I noticed that the door numbers of the latter code did not have quotation marks, could that be a reason for the error?

Thanks again, I feel it necessary to say that after each post!

[Lynch225]

FYI, when I did create the new query "CheckQuery" I was discussing that has the exact same properties as "Trailers_Unloading_LA" and a new form "Checkform" based on that query, I got the sql code:

SELECT Trailers_Unloading_LA.Door, Trailers_Unloading_LA.Trailer_Number, Trailers_Unloading_LA.LA01, Trailers_Unloading_LA.LA02, Trailers_Unloading_LA.LA03, Trailers_Unloading_LA.LA04, Trailers_Unloading_LA.LA05, Trailers_Unloading_LA.LA06, Trailers_Unloading_LA.LA07, Trailers_Unloading_LA.LA08, Trailers_Unloading_LA.LA09, Trailers_Unloading_LA.LA10
FROM Trailers_Unloading_LA
WHERE (((Trailers_Unloading_LA.Door)="9" Or (Trailers_Unloading_LA.Door)="10" Or (Trailers_Unloading_LA.Door)="11"));
 

This looks almost identical to the sqlStr code in the immediate window. I'm so lost...

[C CSR]

If the numbers are "number" types in the field definition of the table then they should not have quotation marks. If they are "text" then they would need single quotation marks '9'. I'm going to check your query composition and get back to you.

[C CSR]

When you got the error in "FROM" clause that meant a syntax error, and you just needed to tweak that part of the query string. The FROM clause refers to the list of Tables your pulling from OR something else further down in the string that won't allow the FROM section to terminate properly.

Don't get in a hurry to start changing things like the Table or the nature of the Split Form, etc. If the query works in Design View in the Query Builder", it'll work in the Docmd line. We just have to correct the pieces in there. Still need to look at it.

[C CSR]

Hey Lynch, what's your status?

[Lynch225]

Hey C CSR, still nothing to report. Its been a crazy weekend, but I'll be doing more work on it tomorrow morning if I can find out anything else. I'll be trying some different things to see why it's not working and I'll repost. Thanks

[Lynch225]

Well after scouring the internet and posting a new topic, I got a response that was very simple. All I had to do was add quotation marks to the sqlStr and sqlPl lines as follows:

If i > 0 Then
    For j = 1 To UBound(Narr) - 1
        RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & "'" & Narr(j) & "'" & " OR "
    Next
    RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & "'" & Narr(j) & "'" & ";"
...
 

Thank you so much for all of your help over the past week over the past week!

[NeoPa]

Ben, You'll probably find that this is a result of the difficulties of communication. Asking questions on a forum is something that most people have a lot of problem with as they only practice talking with people, and that is rarely on technical matters. Getting enough information across clearly is a target that very few attain. From what I've seen of C CSR already, I'm sure they would have been able to help more straightforwardly if all the relevant info had been available. It's something we all have to struggle with, as it's just not easy for most people to get right. We have to understand that (as it can be frustrating at times). Clearly you've done your best, and not stinted on the effort involved, it's just not an easy thing to achieve.

PS. Another, slightly tidier, way to write that could would be :

If i > 0 Then
    RecsChosen = "(Trailers_Unloading_LA.Door In())"
    For j = 1 To UBound(Narr)
        RecsChosen = Replace(RecsChosen, "))", Narr(j) & ",))")
    Next j
    RecsChosen = Replace(RecsChosen, ",))", "))")
...

NB. RecsChose doesn't mean anything.