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?

[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>Reference s. 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.va lue" 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.Con trols] 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!