How do you use multiple checkboxes to filter a form?

[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_Unload ing_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 "DefaultVal ue" in your checkboxes(EXAM PLE: 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.prin t" 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.Prin t" 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_Unloa ding_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_Unloa ding_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_Unloa ding_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_Unloa ding_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 straightforward ly 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.