Thanks for having a look at this, I am getting an Run-Time error 2465 using the code below:
Code:
Private Sub lblTitle_Click()
Dim I As Integer
For I = 1 to 5
Me("chkCheckBox1" & I) = True
Me("chkCheckBox2" & I) = True
Me("chkCheckBox3" & I) = True
Me("chkCheckBox4" & I) = True
Me("chkCheckBox5" & I) = True
Next
End Sub
This is a bit of a pet peeve of mine - .Value is not the default... a reference to the control object itself is what you point to when you don't use .Value. The Value property is what gets resolved if the runtime determines that you're trying to assign a basic type to something, but it is not the default - it is a result of not being explicit and the runtime trying to inherently decide what you wanted.
The problem comes when people get used to omitting the .Value because it works in most cases without out, then try to assign Me.MyCtl to a variant and wonder why they get a type mismatch because they wound up with an object reference instead of a value type, or when they do a <> comparison against a nullable type, or any number of other situations where a .Value is required.
When it comes to programming, I find that the less ambiguity you leave in your code, the less trouble we tend to run into over the long term.
Your runtime 2465 is because you didn't copy Colin's code exactly.
The method he's using is using an integer counter to represent the 1-5 portions of your checkbox names, then looping while incrementing the number each time. It's unnecessary (and incorrect) to put all control names within there.
Try it exactly as he posted and use the debugger to step through the code and you should see how that approach works. It's a very helpful method in many situations.
As Jack has already stated, the error was because you modified the code by combining both versions. As a result the code is looking for 25 checkboxes 11-15, 21-25, 31-35 etc which don’t exist
Use my code or Jack’s but not a mixture of the two
Private Sub lblTitle_Click()
Dim I As Integer
For I = 1 to 5
Me("chkCheckBox" & I) = True
Next
End Sub
I am still getting an error:
Run-time error 2465
Microsoft Access can't find the field 'chkCheckBox1' referred to in your expression.
I used chkCheckBox1, chkCheckBox2, etc as an example. If the checkboxes were had all different names such as chkDog, chkCat, chkFish, etc would the code need to be change to reflect that?
We cannot see your database and can only go on the info you provide.
Yes you do need to update the code to match your checkbox names.
If they are called individual names then jack's code will be better.
(Colin's code is looking for Fields, as that's the default collection of Me... instead reference the Controls collection explicitly. I also prefer explicit trimming and conversion of the integer value, to ensure there's no leading spaces that sometimes occur)
To deselect, you can revert the value (toggle based off the current state of the checkbox):
Code:
Private Sub lblTitle_Click()
Me.chkCheckBox1.Value = NOT Nz(Me.chkCheckBox1.Value, False)
Me.chkCheckBox2.Value = NOT Nz(Me.chkCheckBox2.Value, False)
Me.chkCheckBox3.Value = NOT Nz(Me.chkCheckBox3.Value, False)
Me.chkCheckBox4.Value = NOT Nz(Me.chkCheckBox4.Value, False)
Me.chkCheckBox5.Value = NOT Nz(Me.chkCheckBox5.Value, False)
End Sub
However, note that this will swap each control's value, so if some are checked and some aren't, it will inverse those. If you want to have "make all true" or "make all false" regardless of their current state, you will need separate labels/commands to force that (like a Select All and Deselect All command/label).
For Colin's method, use this:
Code:
Me.Controls("chkCheckBox" & Trim(Str(i))).Value = NOT Nz(Me.Controls("chkCheckbox" & Trim(Str(i))).Value, False)
If you want to toggle them you'll need another piece of code , toggling the existing value won't reset them all. Obviously the code is only currently setting them to true.
I would simply add another set of code to the double click event.
Code:
Private Sub lblTitle_DoubleClick()
Me.chkCheckBox1.Value = False
Me.chkCheckBox2.Value = False
Me.chkCheckBox3.Value = False
Me.chkCheckBox4.Value = False
Me.chkCheckBox5.Value = False
End Sub
I often have trouble with getting separate click & double click event code to fire reliably. Sometime a user double clicks by mistake etc ....
If you want to toggle all checkboxes true or false then I would change the label caption and do one of the following:
Code:
Private Sub lblTitle_Click()
Dim I As Integer
If Me.lblTitle.Caption="Set Checkboxes True" Then
[INDENT] Me.lblTitle.Caption="Set Checkboxes False"
For I = 1 to 5
Me("chkCheckBox" & I) = True
Next[/INDENT]
Else
[INDENT] Me.lblTitle.Caption="Set Checkboxes True"
For I = 1 to 5
Me("chkCheckBox" & I) = False
Next[/INDENT]
End If
End Sub
OR
Code:
Private Sub lblTitle_Click()
Dim I As Integer
If Me.lblTitle.Caption="Set Checkboxes True" Then
[INDENT] Me.lblTitle.Caption="Set Checkboxes False"
Me.chkCheckBox1.Value = True
Me.chkCheckBox2.Value = True
Me.chkCheckBox3.Value = True
Me.chkCheckBox4.Value = True
Me.chkCheckBox5.Value = True[/INDENT]
Else
[INDENT] Me.lblTitle.Caption="Set Checkboxes True"
Me.chkCheckBox1.Value = False
Me.chkCheckBox2.Value = False
Me.chkCheckBox3.Value = False
Me.chkCheckBox4.Value = False
Me.chkCheckBox5.Value = False[/INDENT]
End If
End Sub
So you now have 3 approaches to choose from ...
Please don't combine them again
As for the .Value issue raised by Jack & now by Tony, I never have issues with omitting it but perhaps that's due to the way I write my code.
I NEVER use macros so never need to convert to VBA. It certainly won't do any harm to include .Value
Thanks so much for all your help, I decided not to use the toggle option, I thought that may be a bit confusing for the user. The click and double click options has worked for me, and less confusing for the user.
Code:
Private Sub lblTitle_Click()
Me.chkCheckBox1.Value = True
Me.chkCheckBox2.Value = True
Me.chkCheckBox3.Value = True
Me.chkCheckBox4.Value = True
Me.chkCheckBox5.Value = True
End Sub
Private Sub lblTitle_DoubleClick()
Me.chkCheckBox1.Value = False
Me.chkCheckBox2.Value = False
Me.chkCheckBox3.Value = False
Me.chkCheckBox4.Value = False
Me.chkCheckBox5.Value = False
End Sub
I decided not to use the toggle option, I thought that may be a bit confusing for the user. The click and double click options has worked for me, and less confusing for the user.
Use whatever works for you.
However, for info, I abandoned that approach years ago based on user feedback.
The consensus was they hated separate single & double click events on the same control
