Multiple Checkboxes- click one to select all (Access 2002)

Topic

New Reply

I have a form on which I have several checkboxes that may or may not apply to the data I am entering. Most of the time all of the checkboxes need to be checked. I want to create a “checks all” check box that, when checked, will select all of the checkboxes.

I know that this sort of thing can be done, because I use a proprietary database for ad order entry that has many such lists where you can check one or more items, or you can simply “check all” with one click.

Any help or direction is greatly appreciated.

Reply | Quote

Replies

In the OnClick Event of your checkbox try Me!MyControlName = True

You can either list each control name or set up a loop.

Reply | Quote

If your check boxes have arbitrary names, you’ll have to list them (unless they are the only check boxes on the form, but that is a dangerous assumption, you might need other check boxes later):

Private Sub chkCheckAll_Click
If chkCheckAll = True Then
chkFirst = True
chkSecond = True
…
chkLast = True
End If
End Sub

If you have named your check boxes (for example) chkCheck1 to chkCheck8, you can use

Private Sub chkCheckAll_Click
Dim i As Long
If chkCheckAll = True Then
For i = 1 To 8
Me.Controls(“chkCheck” & i) = True
Next i
End If
End Sub

Reply | Quote

This seems to work, as far as getting the yes/no selection into the underlying table, but it doesn’t cause a “check mark” to appear in the check box. Do you know of a way to do this as well?

Thanks for your help. I no longer feel like

Reply | Quote

If you use the names of the check box controls in the code, they should be checked automatically, and if the controls are bound to fields, the underlying data should be updated too.

Reply | Quote

Hans:
I have never written code before. Could you give me a sample? My checkbox names are ‘ck1’, ‘ck2’, ‘ck3’, ‘ck4’ and ‘ck5’. They are bound to fields named ‘matcklist’, ‘slvcklist’, ‘graphcklist’, ‘matdeadline’ and ‘spcdeadline’, respectively.

Again, any help you can offer is greatly appreciated. I am not exactly a newbie, but sometimes I think I know just enough to think I know what I’m doing, but not enough to breeze through a task.

srs

Reply | Quote

OK, so you have a form bound to a table; it contains (among others), 5 check boxes named ck1 to ck5, bound to fields in the table. These fields are of type Yes/No.

You have a sixth check box on the form that is not bound to any field in the table (its Control Source property is empty). Let’s say you named it ckAll.

• With the form open in design view, select ckAll by clicking on it.
• Activate the Events tab of the Properties window.
• Click in the box labeled On Click.
• Select Event Procedure from the dropdown list of the On Click event.
• Next, click the builder button (the three dots … to the right).
• You will be taken to the Visual Basic Editor. You will see the code module belonging to your form, with the first and last line of the event procedure already created for you, and the blinking insertion cursor in the empty line between them:

  Private Sub ckAll_Click()

  End Sub

  (if you used a different name than ckAll, you will see that followed by _Click)

• Enter the following code between the Sub … and End Sub lines. I like to indent code by using the Tab key, but that’s a matter of taste/convention. Lines starting with an apostroph ‘ are comments, they are meant to explain bits of code but they are not part of the code.

  ‘ Declare a variable
  Dim i As Long
  ‘ Test if user ticked the ckAll check box
  If ckAll = True Then
  ‘ Loop through the five check boxes
  For i = 1 To 5
  ‘ Tick the i-th check box by setting its value to True
  Me.Controls(“ck” & i) = True
  Next i
  End If

• Switch back to Access (using the Windows task bar or Alt+Tab)
• Close the form and save changes.
• Open the form in form view to test it. If you tick ckAll, all check boxes ck1 through ck5 should become checked too. This will be saved in the corresponding fields in the table when you move to another record or close the form.
  [/list]There are some bells and whistles you could add, but first try to get this working correctly.

Reply | Quote

Hans:

I’ve been out of the office for several days, and only just tried the code writing exercise you gave me. Unfortunately, I get an error 438: ‘object doesn’t support this property or method’. When I click debug , it highlights “Me.controls (“ck” & i) = True”. Any ideas on what I’m doing incorrectly?
srs
PS- I’m not a ‘he’

Reply | Quote

Let’s see if we can find out what’s going on. If you’re in the Visual Basic Editor, what is the caption (title) of the window that contains the code you entered (among which the line on which the error occurs)?

PS: the assumption about your gender was not mine

Reply | Quote

sorry about the gender assumption

Reply | Quote

It’s ok. I just thought it was funny, and wanted to set the record straight. If we were speaking in the abstract, ‘he’ would be just fine with me!
Any more ideas?
srs

Reply | Quote

does he need to refresh the form at the end of the code ?

Reply | Quote

In my test form, that wasn’t necessary.

Reply | Quote

Okay, I’m having the same question as the initial poster — I would like to click one check box and have all of the check boxes select. However, I am in a slightly different situation than the original poster — I just have one checkbox in the “detail” section of my form and depending on how many records are returned in the query, the number of check boxes is going to vary, so I’m not sure if a simple loop will work in that case. I tried doing the:

Private Sub ckCheckAll_Click()
If ckCheckAll = True Then
ckChecked = True
End If
End Sub

But it only checks the first checkbox. Can this be done?

Thanks,
Kindra

Reply | Quote

Is this a continuous form?

Reply | Quote

The Default View says “Single Form” as opposed to “Continuous Form” — is that what you’re asking?

Reply | Quote

Yes, that was what I was asking. So, you want to tick a checkbox in all records in the form, not just in the currently displayed record. Is that correct?

Reply | Quote

Yes, I believe that’s what I want. Basically a user executes a query by clicking on a button and this form displays the results. If the user wants to see a record, they need to check on the check boxes to display the full record in another form. This check box is tied to a field in the table called “SELECTED” so that they can select individual records.

Reply | Quote

Note the the record source of the form – probably the name of a table or query. Let’s say it is qrySomething. Change the On Click code for the ckCheckAll check box to:

Private Sub ckCheckAll_Click()
Dim strSQL As String
If ckCheckAll = True Then
strSQL = “UPDATE [qrySomething] SET [Selected] = True”
‘ DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
‘ DoCmd.SetWarnings True
End If
End Sub

Note: I have deliberately commented out two lines by putting an apostrophe ‘ in front of them. During testing, you will be warned that you are about to execute an action query, and that you are about to update n records. This is useful, because it will give you an indication of what the code does. In the “production version”, those warnings are irritating; you can avoid them by uncommenting the two lines (remove the apostrophes.)

Reply | Quote

Hans, thank you so much, that worked perfectly!

Kindra

Reply | Quote

(Edited by HansV to provide link to post – see Help 19)

Okay, maybe not so perfectly.

Listed below is the query that we’re running to get our results to display — I had posted in a different thread about it last summer. The interesting thing about this query is that it returns all of the records, but the form only displays some of them (i.e., if I execute the form and the query, the form shows some of the records, but the query shows all of them). It’s a continuous form.

When I click the checkbox, it’s sending back all of the records, rather than just the records of the criteria that I’ve chosen. How can I limit it to just the records that the form displays?

post 283430

Reply | Quote

Sorry, I don’t understand. The question you were asking in this thread was about a single form, now you conjure up a continuous form, and you mention a post in another thread that doesn’t seem to refer to any check box.

You will have to be a lot clearer if you want help.

Reply | Quote

Hans,

You’re right — it was unclear. My apologies.

The thread to which I was referring was the query that the form in question was linked to.

I must have been looking at the wrong form when you had asked me earlier in this thread about the single vs. continuous form because it’s a continuous form. My apologies.

When I click in my form the “Check All” checkbox, it selects ALL of the records — even though I’m only searching on Order Type, for example. So, if there are 200 records that have a “Not Ordered” status (the query is designed to only pull those records that are not ordered) and I search on “Rush Orders” for example, and get 15 records back and click on the “Check All” button, 200 records get returned instead of the 15. Does that make sense?

Thanks,
Kindra

Reply | Quote

The query in the other thread you refer to doesn’t mention an Order Status or an Order Type, so I have no idea what this is all about.

Reply | Quote

The query in the other thread you refer to doesn’t mention an Order Status or an Order Type, so I have no idea what this is all about.

Reply | Quote

If the checkbox is unbound, you will see exactly the behavior you describe on a continuous form. Since there is really only *one* set of controls on a continuous form, checking one that is not bound to a field in the underlying recordset results in the same value appearing in the “checkboxes” in all the records.

Reply | Quote

Thank you Charlotte — I was afraid that was the answer.

Kindra

Reply | Quote

Thank you Charlotte — I was afraid that was the answer.

Kindra

Reply | Quote

If the checkbox is unbound, you will see exactly the behavior you describe on a continuous form. Since there is really only *one* set of controls on a continuous form, checking one that is not bound to a field in the underlying recordset results in the same value appearing in the “checkboxes” in all the records.

Reply | Quote

Hans,

You’re right — it was unclear. My apologies.

The thread to which I was referring was the query that the form in question was linked to.

I must have been looking at the wrong form when you had asked me earlier in this thread about the single vs. continuous form because it’s a continuous form. My apologies.

When I click in my form the “Check All” checkbox, it selects ALL of the records — even though I’m only searching on Order Type, for example. So, if there are 200 records that have a “Not Ordered” status (the query is designed to only pull those records that are not ordered) and I search on “Rush Orders” for example, and get 15 records back and click on the “Check All” button, 200 records get returned instead of the 15. Does that make sense?

Thanks,
Kindra

Reply | Quote

Sorry, I don’t understand. The question you were asking in this thread was about a single form, now you conjure up a continuous form, and you mention a post in another thread that doesn’t seem to refer to any check box.

You will have to be a lot clearer if you want help.

Reply | Quote

(Edited by HansV to provide link to post – see Help 19)

Okay, maybe not so perfectly.

Listed below is the query that we’re running to get our results to display — I had posted in a different thread about it last summer. The interesting thing about this query is that it returns all of the records, but the form only displays some of them (i.e., if I execute the form and the query, the form shows some of the records, but the query shows all of them). It’s a continuous form.

When I click the checkbox, it’s sending back all of the records, rather than just the records of the criteria that I’ve chosen. How can I limit it to just the records that the form displays?

post 283430

Reply | Quote

Hans, thank you so much, that worked perfectly!

Kindra

Reply | Quote

Note the the record source of the form – probably the name of a table or query. Let’s say it is qrySomething. Change the On Click code for the ckCheckAll check box to:

Private Sub ckCheckAll_Click()
Dim strSQL As String
If ckCheckAll = True Then
strSQL = “UPDATE [qrySomething] SET [Selected] = True”
‘ DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
‘ DoCmd.SetWarnings True
End If
End Sub

Note: I have deliberately commented out two lines by putting an apostrophe ‘ in front of them. During testing, you will be warned that you are about to execute an action query, and that you are about to update n records. This is useful, because it will give you an indication of what the code does. In the “production version”, those warnings are irritating; you can avoid them by uncommenting the two lines (remove the apostrophes.)

Reply | Quote

Yes, I believe that’s what I want. Basically a user executes a query by clicking on a button and this form displays the results. If the user wants to see a record, they need to check on the check boxes to display the full record in another form. This check box is tied to a field in the table called “SELECTED” so that they can select individual records.

Reply | Quote

Yes, that was what I was asking. So, you want to tick a checkbox in all records in the form, not just in the currently displayed record. Is that correct?

Reply | Quote

The Default View says “Single Form” as opposed to “Continuous Form” — is that what you’re asking?

Reply | Quote

Is this a continuous form?

Reply | Quote

Okay, I’m having the same question as the initial poster — I would like to click one check box and have all of the check boxes select. However, I am in a slightly different situation than the original poster — I just have one checkbox in the “detail” section of my form and depending on how many records are returned in the query, the number of check boxes is going to vary, so I’m not sure if a simple loop will work in that case. I tried doing the:

Private Sub ckCheckAll_Click()
If ckCheckAll = True Then
ckChecked = True
End If
End Sub

But it only checks the first checkbox. Can this be done?

Thanks,
Kindra

Reply | Quote