Access Form Multiple Checkbox's auto-populate the 3rd based off the 1st and 2nd Checkbox

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
I have 3 Checkboxes on my form and I'm trying to make the third checkbox automatically populate if the first 2 are checked, see below...Any suggestions on how this can be completed? For the Third checkbox Field C1 on my Form, I need it to auto-populate with True or Yes or -1 based off the 1st 2 being checked and if one of the 1st 2 is not then leave it blank...

Does this need to be in the Control Source, Default Value, or an expression built into the query for the form in order to get Field C1 to populate to -1?

IIf([A1]="Yes" And [B1]="Yes",[C1]=-1,"")
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
251
Office Version
365, 2016
Platform
Windows
The logic will be:

Checkbox3=Checkbox1 and Checkbox2

So on you userform for the click event of all of the checkboxes you need to have the code above. Which on the userform code area you should have this.

VBA Code:
Private Sub CheckBox1_Click()
CheckBox3 = CheckBox1 And CheckBox2
End Sub

Private Sub CheckBox2_Click()
CheckBox3 = CheckBox1 And CheckBox2
End Sub

Private Sub CheckBox3_Click()
CheckBox3 = CheckBox1 And CheckBox2
End Sub
The code for Checkbox 3 is there to stop the state changing, if 1 and 2 are not true. If you want to be able to independently set it, then remove the logic out of Checkbox3
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,118
Office Version
365
Platform
Windows
Let's say that you have three Checkboxes, named "Check1", "Check2", and "Check3" on your form. Then, you can VBA code to the "AfterUpdate" Event of the Check1 checkbox that looks like this:
VBA Code:
Private Sub Check1_AfterUpdate()
    If Me.Check1.Value = -1 And Me.Check2.Value = -1 Then
        Me.Check3.Value = -1
    End If
End Sub
Then add the same code to the Check2 checkbox.

This should do what you want.
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
I have a question, I have a Macro Event for each Checkbox to include the 3rd Checkbox but since they are not actually clicking the Checkbox, will the Macro Event still Trigger...I don't think I can use CountTepes code based on this and Joe I see the update in my query for Checkbox 3 but it's not populating the form for some reason...

On Click:
SetValue
Item =[CompletedDate]
Expression = Date()

SetValue
Item =[CompletedBy]
Expression = [Forms]![HomePage]![Subform_User].[Form]![User]
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
Sorry Joe, I just notice other rows in the query where Checkbox 1 and Checkbox 2 is checked but it didn't update checkbox 3 with your code as thought...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,118
Office Version
365
Platform
Windows
So, you are using the Macro Builder instead of Event Procedure code on these buttons (ugh, sometimes I hate the new-fangled options they added in the newer versions - I guess that makes me a dinosaur!)? Does it at least show the check boxes as checked after you click them?
Also, do you have the names of these checkbox objects?

I think you need to use or the other, either the Macro settings or the Event Procedure code. I don't think you can use both at the same time for a single checkbox.
Quite frankly, I do not like the Macro option. It may be easy/convenient, but I think you are limited in what you can do. For more advanced VBA code, I think you will want to use the Event Procedure VBA code option.

So, for something like this:
SetValue
Item =[CompletedDate]
Expression = Date()
you will want to add a line to the AfterUpdate Event Procedure VBA code that looks like this:
VBA Code:
Me.CompletedDate = Date()
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
Fixing old code...lol

Does it at least show the check boxes as checked after you click them? Yes

Also, do you have the names of these checkbox objects? I'm substituting the check1, check2, and check 3 for there original names....

Private Sub Moved_AfterUpdate()
If Me.Moved.Value = -1 And Me.Paid.Value = -1 Then
Me.Completed.Value = -1
End If
End Sub
 
Last edited:

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
Do I add:

SetValue
Item =[CompletedBy]
Expression = [Forms]![HomePage]![Subform_User].[Form]![User]

Me.CompeletedBy = [Forms]![HomePage]![Subform_User].[Form]![User]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,118
Office Version
365
Platform
Windows
Do I add:

SetValue
Item =[CompletedBy]
Expression = [Forms]![HomePage]![Subform_User].[Form]![User]

Me.CompeletedBy = [Forms]![HomePage]![Subform_User].[Form]![User]
Seems like it should work, as long as you fix the typo in the field name. ;)
Try it and see if it works!
 

Forum statistics

Threads
1,089,642
Messages
5,409,494
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top