Access VBA (1 Viewer)

S1LV3RF0X87 · Sep 20, 2019

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 · Sep 20, 2019

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 · Sep 20, 2019

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 · Sep 20, 2019

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.

Code:

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 · Sep 20, 2019

S1LV3RF0X87 said:
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 · Sep 20, 2019

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

Code:

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.

Access VBA (1 Viewer)

S1LV3RF0X87

Registered User.

Attachments

theDBguy

I’m here to help

S1LV3RF0X87

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread