473,883 Members | 1,712 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do you use multiple checkboxes to filter a form?

43 New Member
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.

Jan 25 '12 #1
28 8088
144 New Member
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Base 1
  4. Private Sub Command6_Click()
  5. Dim Narr() As Variant
  6. i = 0
  7. For Each Ctrl In Form.Controls
  8.     If (Ctrl.ControlType = 106) Then
  9.         ThisChkbox = Ctrl.Value
  10.         If ThisChkbox Then
  11.             i = i + 1
  12.             ReDim Preserve Narr(i)
  13.             Narr(i) = Ctrl.DefaultValue
  14.         End If
  15.     End If
  16. Next
  18. If i > 0 Then
  19.     For j = 1 To UBound(Narr) - 1
  20.         RecsChose = RecsChose & "arrChkbox.Rec_ID=" & Narr(j) & " OR "
  21.     Next
  22.     RecsChose = RecsChose & "arrChkbox.Rec_ID=" & Narr(j) & ";"
  23.     sqlP1 = "SELECT arrChkbox.Rec_ID, arrChkbox.TheInfo FROM arrChkbox WHERE "
  24.     sqlStr = sqlP1 & RecsChose
  25. Else
  26.     ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
  27. End If
  28. 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.
Jan 25 '12 #2
144 New Member
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?


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).
Jan 25 '12 #3
43 New Member
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 25 '12 #4
144 New Member
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.
Jan 25 '12 #5
144 New Member
Also, try making an "Explicit" reference to the forms controls like this:

Expand|Select|Wrap|Line Numbers
  1. For Each Ctrl In Forms!MyFormName.Controls
  2. 'instead of 
  4. 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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "MySplitFormName", acNormal, sqlStr, , acFormReadOnly, acWindowNormal
Put it at the bottom in the same Sub.
Jan 25 '12 #6
43 New Member
Yes, it is an access form and I updated the button, form, and field names as such:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_Click()
  3. Dim Narr() As Variant
  5. i = 0
  6. For Each ctrl In Forms!Check.Controls
  7.     If (ctrl.ControlType = 106) Then
  8.         ThisChkbox = ctrl.Value
  9.             If ThisChkbox Then
  10.             i = i + 1
  11.             ReDim Preserve Narr(i)
  12.             Narr(i) = ctrl.DefaultValue
  13.             End If
  14.     End If
  15. Next
  17. If i > 0 Then
  18.     For j = 1 To UBound(Narr) - 1
  19.     RecsChose = RecsChose & "Trailers_Unloading_LA.Door=" & Narr(j) & " OR "
  20.     Next
  21.     RecsChose = RecsChose & "Trailers_Unloading_LA.Door" & Narr(j) & ";"
  22.     sqlP1 = "SELECT Trailers_Unloading_All.Door, Trailers_Unloading_All.Trailer_Number FROM arrChkbox WHERE "
  23.     sqlStr = sqlP1 & RecsChose
  24.     Else
  25.     ErrMess = MsgBox("Nothing Checked", vbOKOnly, "Status")
  26. End If
  28. DoCmd.OpenForm "UnloadNow", acNormal, , sqlStr
  30. End Sub
Any input will be helpful, thanks again!
Jan 25 '12 #7
43 New Member
Oops Line 22 should be:

Expand|Select|Wrap|Line Numbers
  1.     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
Jan 25 '12 #8
144 New Member
Let's do this to cover the bases. Under the Narr declaration in the top of the sub:

Expand|Select|Wrap|Line Numbers
  1. Dim Ctrl As Object, i, j, RecsChose, sqlP1, sqlStr, ThisChkbox, ErrMess
  2. 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....
Jan 25 '12 #9
144 New Member
Note: in the line below, "Check" should be the name of the form, not a control or option group control.

Expand|Select|Wrap|Line Numbers
  1. For Each ctrl In Forms!Check.Controls 
Your not using an "Option group" for your checkboxes right?
Jan 25 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: jeffsnox | last post by:
Hi, I have multiple checkboxes on the same form as follows: <input type='checkbox' name='cbtype' value='1'> <input type='checkbox' name='cbtype' value='2'> <input type='checkbox' name='cbtype' value='3'> I'm wanting to re-display the same checkboxes, but if the user previously checked one of them then I want it automatically checked on
by: ramapv | last post by:
can i highlight a checkbox from a group of checkbox with particular name which is given as a search key. I am having a list of checkboxes and i have to select some of them and form a group.but i'm having a huge list of names , so i want to keep a function so that i can go to (scroll) the name which starts with a particular alphabet & i can check it for selecting components to form a group. both are in different frames. Thanks in...
by: favor08 | last post by:
I have a continious subform that 9000 + records and I have serveral check marks on the subform. so it ties to that record. Does anyone have any examples were they use multiple checkboxes in a continous form and how they lined them up. Just looking for an example. Currently I have some to the far left and others to the far right and have them move over based on what option they select.
by: dream2rule | last post by:
Hello All, I am trying to validate multiple checkboxes whose values are stored in an array using php. I have been trying from a really long time but nothing's working out. Can anyone help? Here's the code: <form id="create_user" name="create_user" method="post" action="creating_database.php" onsubmit="return validate_create_user_form();">
by: serghei | last post by:
I have already the insert form and the database. It's working to insert multiple data with multiple checkboxes. But I can't retrieve multiple data.
by: Ned Balzer | last post by:
Hi, Can anyone point me in the direction of a solution for validating multiple checkboxes in an asp.net 2.0 page? 1) This is not a checkboxlist, it is a number of separate checkboxes 2) I do not need to ensure that they are checked, what I am trying to do is lock down the page to prevent possible code injection. So, the checkboxes can be either checked or unchecked, I don't care.
by: santoshjsh | last post by:
hello everyone, i have a gridview in which i have multiple checkboxes in a single column. means for every row in the gridview i have four checkboxes in one column e.g Add, Delete, Print, Modify. now i need to bind these check boxes from database table according to the four different fields in the table in which the value is 0 and 1. if the column value is 1 than the checkbox should be displayed checked otherwise no. i dont know how...
by: raamay | last post by:
hey experts, please advise me what is the best way to save multiple checkboxes value in a database. I have 6 checkboxes and i came across storing the values in a single column of a table which i dont want to implement as the checkbox values may be used for search purpose in latter stage. Apart from that i also came across creating individual columns for each checkbox which i feel would really make my table large. Is there any other way that i...
by: kimmelsd33 | last post by:
I am adding to my software. I have placed about 30 checkboxes on a form. Based on which checkboxes are selected, I want to print the values to a tab delimited text file. For instance, the first checkbox is labeled "Depth" and corresponds to a named variable of 'depth'. If this checkbox is select, I want to print column A, or 'depth' to the file. This part is easy. I am looking for the simplest way to write to a tab delimited text file, if there...
by: BabyLucifer666 | last post by:
Hello All, (using Access2000) I have a form with multiple unbound checkboxes. What I would like to do is have the user check whoever needs to take a specific training course. My database is normalized, I have no Yes/no fields in my tables, what I have is VB code that inputs an IDnumber into the field that matches the specific job title for the employee that needs to take that course: Private Sub chkMFGAssoc_Click() If Me.chkMFGAssoc =...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.