Before Update Event Conundrum Access VBA

Question

Alright I have some interesting interactions going on and I want some advice from my seasoned VBA cats to help me see if there is a way more elegant way to approach my situation.

Details:

Have a check box on a Form. This check box is Bound to a table.
Have a combo box on a subform on the original form.
On the beforeupdate event on the main form, there is data validation that checks if Checkbox =True then combobox cannot be null. This is intended to keep the user from switching records without performing needed updated.

So the issue is, I need to be able to enforce the data integrity as well as not hinder UI flow as much as possible. It's worth noting that in the beforeupdate event I cannot use Cancel = True because when you try to switch focus to the subform the beforeupdate event fires, which obviously this creates an INF lock in UI Flow. Another issue is once the update event fires, the checkbox value gets written to a table. Unchecking the checkbox ends up creating another loop of sorts.

My solution so far:

in the before update event

If Checkbox = True and IsNull(Me!ComboBox) = True Then
    lastRecord = Me.CurrentRecord
    chkBool = True
end if

in the Form_Current Event

currRecord = Me.CurrentRecord

If currRecord <> lastRecord And chkBool = True Then
    DoCmd.GoToRecord acDataForm, "form", acGoTo, lastRecord
    GoTo Exit_Form_Current
ElseIf currRecord = lastRecord And chkBool = True Then
    'do stuff
    vbSql = "Update Statement"
    DoCmd.RunSQL vbSql
    chkBool = False
    Me.Refresh
End If

Is there a better way to approach this? Or is the wonky work around kind of how i have to approach it? Sadly I cant redesign to circumvent it due to the subform needing to be a continuous subform.

Gustav Gustav · Accepted Answer · 2017-05-06T20:14:49

First, you need to check for Null correctly:

If Me!Checkbox = True And IsNull(Me!ComboBox) Then
    lastRecord = Me.CurrentRecord
    chkBool = True
End If

Then evaluate how this works together with the second piece of code.

Before Update Event Conundrum Access VBA

2 Answers