Access VBA

[S1LV3RF0X87]

Hi People, i hope you can help me out.

I got an issue where i want to write some logic in vba where a user is unable to save the form that they are on until a set criteria is met.

On my form i have a bunch of tick boxes which a user can select. When completing the form the user needs to click 1 tick box only.

I need the code to be able to count how many tick boxes equal true and only pass if no more than 1 tick box equals true.

Is this possible?

I will attach a screenshot of my form and i have put a red box around the tick boxes the user are able to select but i only want 1 box ticked.

Thanks in advance

 

[theDBguy]

Hi. Welcome to AWF! You won't need any code if you put your tickboxes in an Option Group. Also, if you're only allowing one option to be selected at a time, then the more common approach is to use a Radio Button instead of a Checkbox.

 

[S1LV3RF0X87]

Hi the issue i have is the data goes back for years and its always been check boxes, i need to try and get this to work without changing the table as it will effect a load of reports

 

[arnelgp]

agree, use Option Group instead. It only allows 1 tick/check.
so you only need to test if an item on the group has been checked or none was checked.

private sub form_beforeupdate(cancel as integer)
cancel = (me.optionGroupName.Value = 0)
if cancel
    msg "you need to tick one..."
end if
end sub

 

[theDBguy]

Hi the issue i have is the data goes back for years and its always been check boxes, i need to try and get this to work without changing the table as it will effect a load of reports

Hi. An Option Group is a Form control, meaning, it's just the User Interface. It shouldn't affect how your data is stored in the table (at least I think it shouldn't).

 

[arnelgp]

ok, you need to identify the names of each checkbox you grouped:

private sub form_beforeupdate(cancel as integer)
cancel = ([chkSales] + [chkWarranty] + [chkInsurance] + …"other checkboxes") <> -1
if cancel then _
    msgbox "you need to tick only 1 on the red box"
end sub

mr.db, maybe each check is bound to sepa fields.