Is there an "unless" syntax in VBA?

[kyuball]

I have a form (frminitialincome) that tracks different income sources for clients in a social work outreach project through several check boxes (we are limited to tracking only certain kinds of income based on what our funders want to see) that populates a table with same (tblclientincome.) It is not a case where only one option can be listed for a client as any of them can have multiple sources of income (e.g. many have Food Stamps along with Cash Employment income.) However, there are some constant conditions that I wanted to set.

If a client is receiving SSI (basically disability payments for those with little to no work history) and/ or SSDI (disability for those with a bit of a work history) it is assumed (and should be assumed) that they will also receive state medical insurance (Medicaid.) I currently have the relevant check boxes doing this:

Private Sub ssicheck_Click()

If Me.ssicheck = True Then
Me.medicaidcheck = True
Else
Me.medicaidcheck =False
End If

End Sub

AND

Private Sub ssdicheck_Click()

If Me.ssdicheck = True Then
Me.medicaidcheck = True
Else
Me.medicaidcheck =False
End If

End Sub

It would be good if the Medicaid checkbox was only dependent on one field, but as you can tell from the codes, if a person were to click on ssi then were to want to change the answer to ssdi by clicking the ssdi field to true THEN ssi to false, the second action would set the Medicaid checkbox to false. I would like to think that the people entering the data would double check their entries or go through proper steps (i.e. clearing the incorrect checkbox before checking off the correct one), but then I wouldn't have my job.

Is there some kind of syntax that can make an "unless" statement in the code? Something like, "Else, me.checkbox = false UNLESS me.othercheckbox = true"

 

[pbaldy]

How about:

If Me.ssicheck = True OR Me.ssdicheck = True Then

 

[HiTechCoach]

Paul's suggestion will handle what you want.

I would recommend that you consider also making the GUI more user friendly by using an option group. This way they can only pick SSI or SSDI. It will also require less training and data validation coding.

With two check boxes, I would assume that you also have in the form's before update event code to check to be sure that both check boxes are not selected. An otion group would prevent this.

 

[kyuball]

pbaldy:

Where would I insert that code? Would I move it to maybe the before update event of the medicaid checkbox?

HiTechCoach:

I would go with the option group but there actually are clients that receive both (e.g. very scant work history = not enough quarters worked for the SSDI check to come out to much; those tend to get bolstered with partial SSI.. )

Thank you both for your quick replies!!

 

[SOS]

Where would I insert that code? Would I move it to maybe the before update event of the medicaid checkbox? !

I would think you would want the After Update event of both so that you can check if one gets checked and the other unchecked.

 

[gemma-the-husky]

one point - if you dont need to store the medicaid position (since it is determined by receipt of either ssi or ssdi payments,) then all you need to do is set the recordsource of the medicaid checkbox to be

=ssdicheck or ssicheck

and then you dont need any code - it will automatically refresh when either of the other checkboxes are ticked.

However, this now becomes an unbound field, that you cant just set independently of the others - which may or may not be what you want.

 

[MarkK]

Whatever the case, if booleans are involved you can always refactor something as verbose as...

If Me.ssicheck = True OR Me.ssdicheck = True Then
  Me.medicaidcheck = True
Else
  Me.medicaidcheck = False
End If

...to Gemma's much simpler assertion...

Me.medicaidcheck = me.ssicheck OR me.ssdicheck

 

[HiTechCoach]

HiTechCoach:

I would go with the option group but there actually are clients that receive both (e.g. very scant work history = not enough quarters worked for the SSDI check to come out to much; those tend to get bolstered with partial SSI.. )

I would still use an option group, I woudl add a third option for "Both"

 

[kyuball]

Hey all,

Sorry for responding so late, but I have been away at various trainings and conferences. Upon returning, I tried out pbaldy/lagbolt's suggestion and it worked like a charm!

Thanks to everyone who helped me out!