Conditional Checkbox

[dbowens]

In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:

If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if

This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true). Thanks for any help as I am still learning!

 

[bob fitz]

You your code will work if you put it in the After Update event of all other "non master" checkboxes. It would be better IMHO to put your code in a sub proceedure and call that sub in the after update events.

 

[dbowens]

Ok great. I will give it a shot. What exactly do you mean regarding using the sub procedure? Still fairly new at coding language and practice. It also seems you have advanced knowledge in coding. Could you recommend some resources for learning more VBA? Thanks!

 

[vbaInet]

The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false.

If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if

The above can be written as one of the following:

If Me.Checkbox1 And Me.Checkbox2 Then
    me.completed=True
ElseIf Not Me.Checkbox1 And Not Me.CheckBox2 Then
    me.completed=False
End if

Dim varNonMaster As Variant
varNonMaster = Nz(Me.Checbox1,0) + Nz(Me.Checkbox2,0)

If varNonMaster = -2 Then
    me.completed=True
ElseIf varNonMaster=0 Then
    me.completed=False
End if

... and I would run this in the Before Update event of the form or After Update event of each Non Master checkbox. Like bob fitz said, better to create a function for this.

However, there's no point saving this value if you can calculate it.

 

[dbowens]

Thanks. So if I have more than two non master checkboxes could I continue like this:

If Me.Checkbox1 And Me.Checkbox2 And me.Checkbox3 and me.Checkbox4 Then
me.completed=True
ElseIf Not Me.Checkbox1 And Not Me.CheckBox2 and not me.Checkbox3 and not me.checkbox4 Then
me.completed=False
End if

And you recommend that this be done in the before update event under form properties?

Thanks again.

 

[bob fitz]

Thanks. So if I have more than two non master checkboxes could I continue like this:

If Me.Checkbox1 And Me.Checkbox2 And me.Checkbox3 and me.Checkbox4 Then
me.completed=True
ElseIf Not Me.Checkbox1 And Not Me.CheckBox2 and not me.Checkbox3 and not me.checkbox4 Then
me.completed=False
End if

Yes

And you recommend that this be done in the before update event under form properties?

You could but my preference would be to put you code in a private sub in the forms code module and then call the sub from the After Update event of each check box.

 

[dbowens]

Yes
You could but my preference would be to put you code in a private sub in the forms code module and then call the sub from the After Update event of each check box.

Bob, I have never really used this function before, but I would like to if you believe that it will be more reliable. Would the private sub name be a beforeupdate? I appreciate the guidance on this. Still learning.

 

[bob fitz]

Bob, I have never really used this function before, but I would like to if you believe that it will be more reliable. Would the private sub name be a beforeupdate? I appreciate the guidance on this. Still learning.

No. That is the name given to an event procedure of an object like a form or a control. Take a look at the attached db which shows how I would handle this situation. Post back if you have any questions.

 

[vbaInet]

Here's an OOP's way to do it:-

No class Tony?

 

[dbowens]

No. That is the name given to an event procedure of an object like a form or a control. Take a look at the attached db which shows how I would handle this situation. Post back if you have any questions.

Bob this works great! Thanks so much for the lesson! You are the man .

 

[vbaInet]

I see you meant Access objects.

 

[vbaInet]

No Uncle, please educate me. But I have a feeling "they've" done it so as to disambiguate from other libraries that use the same context.

I wonder who "they" are Uncle Giz!

 

[bob fitz]

Bob this works great! Thanks so much for the lesson! You are the man .

Thank you for your kind words. Lets us know if you need any explanation of the code or methods used.

 

[vbaInet]

I'll keep schtum Uncle