MS Access Form VBA Check Checkboxes, Before Checking Others?

Question

I am working on an Access application, with a SQL back-end.

I have a form for people, where each person can need to have different things checked or unchecked. There are the following fields and corresponding controls on the form:

No Options (integer - boolean - checkbox)

Option A (integer - boolean - checkbox)

Option A Amount (money)

Option B (integer - boolean - checkbox)

Option B Amount (money)

Option C (integer - boolean - checkbox)

Option C Amount (money)

Option D (integer - boolean - checkbox)

Option D command button (opens popup form with option type drop down and amounts for multiple values to be entered).

My concern is that if someone checks one box, it can conflict with another checkbox or money fields.

If someone checks No Options, and one of the other options is checked, I have to uncheck those. I also need to 0 out their corresponding money field. If there are Option D "Other Option" records, in a linked table, then I need to delete those too, after I confirm with the user. I also want to disable the checkboxes and money/ command button controls for options A - D.

If I am unchecking No Options, then I need to enable all of that.

You can start to see how every time I check any of the No Options, or Options A - D, I have to check the state of No Options, and the option's corresponding money amount, to confirm the user wants to make this change.

To do that, I set up the following code for the No Options Before update:

Private Sub NoOptions_BeforeUpdate(Cancel As Integer)
Dim Msg, Style, Title
Dim delOLiensSQL

If Me.NoOptions = False Then
    If (Me.OptionA = True Or Me.OptionB = True Or Me.OptionC = True Or Me.OptionD = True) Then
        Msg = "You have chosen No Options, but one or more option is checked." & vbCrLf & _
        "Choosing No Options will require removing all Lien amounts." & vbCrLf & _
        "Would you like to change this Person to No Options?"
        Style = vbYesNo
        Title = "All Options Will Be Reset to 0 and False."
        Response = MsgBox(Msg, Style, Title)

        If Response = vbYes Then
            If Nz(DLookup("ID", "tblPersonOtherOptionsD", "FKPerson = " & Me.ID), 0) Then
                delOLiensSQL = "Delete From tblPersonOtherOptionsD Where FKPerson = " & Me.ID
                DoCmd.RunSQL delOoptionssSQL, dbSeeChanges
            End If
            Me.OptionA = False
            Me.OptionAAmount = 0
            Me.OptionB = False
            Me.OptionBAmount = 0
            Me.OptionC = False
            Me.OptionCAmount = 0
            Me.OptionD = False
            OptionsAllowPubFunc (False)
        Else
            Me.Undo
            MsgBox "OK, we will leave everything as it is.", vbOKOnly, "Better Safe Than Sorry"
        End If
    Else
        Me.NoOptions = True
    End If
Else
    Me.NoOptions = False
End If
End Sub

OptionsAllowPubFunc (False) is a public function which is the following:

Public Function PlaintiffLiensAllowed(Liens As Boolean)
    Forms!frmPerson.OptionAAmount.Enabled = Liens
    Forms!frmPerson.OptionBAmount.Enabled = Liens
    Forms!frmPerson.OptionCAmount.Enabled = Liens
    Forms!frmPerson.OptionDAmount.Enabled = Liens
End Function

I also set up Before Update public function for OptionA, OptionB, OptionC, OptionD as follows:

Public Function ChangeAOption(OptionCheck As Control, OptionAmount As Control, OptionName As String)
    Dim Msg, Style, Title
    Dim Msg2, Style2, Title2

    If OptionCheck = True Then

        If Nz(OptionAmount, 0) > 0 Then
            Msg = "There is a " & OptionName & " Option amount. Unchecking " & OptionName & " Option, will require the amount to be 0." & vbCrLf & _
            "Would you like to uncheck " & OptionName & " Option and make the amount 0?"
            Style = vbYesNo
            Title = "Confirm No " & OptionName & " Option."
            Response = MsgBox(Msg, Style, Title)

            If Response = vbYes Then
                OptionAmount = 0
                OptionCheck = False
            Else
                OptionCheck.Undo
                MsgBox "Ok, we will leave it as is.", vbOKOnly, "Better Safe Than Sorry."
            End If
        Else
            OptionCheck = False
        End If
    Else
        If Forms!frmPerson.NoOptions = True Then
            Msg2 = "No Options is Checked. Checking " & OptionName & " Options will require no Options to be unchecked." & vbCrLf & _
            "Would you like to uncheck no Options?"
            Style2 = vbYesNo
            Title2 = "Confirm No Options False."
            Response2 = MsgBox(Msg2, Style2, Title2)
            If Response2 = vbYes Then
                OptionsAllowPubFunc (True)
                Forms!frmPerson.NoOptions = False
                OptionCheck = True
            Else
                OptionCheck = True
            End If
        Else
        OptionCheck = True
    End If
End Function

I'm testing this, and when I try to check the No Options checkbox, changing it from false to true, I get a run-time error '-2147352567 (80020009)':

The macro or function set to BeforeUpdate or ValidationRule property for this field is preventing the [People application] from saving data in the field.

Anyone know what I'm doing wrong? Is there a more simple way to do this?

Thank you!!!

Answer 1

From my recent experience, when the Before Update event is triggered it uses the new value of the field in any calculations. So I'd expect you to want to start that main If statement like If Me.NoOptions = True Then. I also don't believe you need the Else OptionCheck = True bits just before the End Ifs.

What may be causing a problem is you've called OptionsAllowPubFunc, but the function you've included in this question is actually called PlaintiffLiensAllowed - unless you have an identical function called OptionsAllowPubFunc, this'll give you an error.

Finally, I've never found me.undo to be especially helpful in this kind of context, but the Before Update gives you an out - just tell it to cancel using Cancel = True (or anything that isn't 0) and it'll not update the field and exit the sub.

You could also add in a bit to re-enable the option fields if they were un-checking the box.

Try running it as:

Private Sub NoOptions_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As VbMsgBoxStyle, Title As String
Dim Response As VbMsgBoxResult, delOLiensSQL As String

If Me.NoOptions = True Then
    If (Me.OptionA = True Or Me.OptionB = True Or Me.OptionC = True Or Me.OptionD = True) Then
        Msg = "You have chosen No Options, but one or more option is checked." & vbCrLf & _
        "Choosing No Options will require removing all Lien amounts." & vbCrLf & _
        "Would you like to change this Person to No Options?"
        Style = vbYesNo
        Title = "All Options Will Be Reset to 0 and False."
        Response = MsgBox(Msg, Style, Title)

        If Response = vbYes Then
            If Nz(DLookup("ID", "tblPersonOtherOptionsD", "FKPerson = " & Me.ID), 0) Then
                delOLiensSQL = "Delete From tblPersonOtherOptionsD Where FKPerson = " & Me.ID
                DoCmd.RunSQL delOoptionssSQL, dbSeeChanges
            End If
            Me.OptionA = False
            Me.OptionAAmount = 0
            Me.OptionB = False
            Me.OptionBAmount = 0
            Me.OptionC = False
            Me.OptionCAmount = 0
            Me.OptionD = False
            PlaintiffLiensAllowed(False)
        Else
            MsgBox "OK, we will leave everything as it is.", vbOKOnly, "Better Safe Than Sorry"
            Cancel = True
        End If
    End If
Else
    PlaintiffLiensAllowed(True)
End If
End Sub