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 "WhereClaus e" 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?