[SOLVED] MS Access form; only lock the controls when value is 0 and not when the value is empty

MS Access form; only lock the controls when value is 0 and not when the value is empty

I need to lock all controls on the main form and a subform when the total of the sub form is 0. The unbound control on the main form holding the value is named sumBalance with the control source picking up the sum of the sub form =Nz([frm_0_accs_fin_trans_journ_subf]![JournalTotal],"")

I have made two alternative VBA codes in the current event on the main form:

If IsNull(Me!sumBalance) Then
        Me!fin_trans_id.Locked = False
        Me!fin_trans_tstamp.Locked = False
        Me!fin_trans_description.Locked = False
        Me!frm_0_accs_fin_trans_journ_subf.Locked = False
    ElseIf Me!sumBalance < 0 Then
        Me!fin_trans_id.Locked = False
        Me!fin_trans_tstamp.Locked = False
        Me!fin_trans_description.Locked = False
        Me!frm_0_accs_fin_trans_journ_subf.Locked = False
    ElseIf Me!sumBalance > 0 Then
        Me!fin_trans_id.Locked = False
        Me!fin_trans_tstamp.Locked = False
        Me!fin_trans_description.Locked = False
        Me!frm_0_accs_fin_trans_journ_subf.Locked = False
    ElseIf Me!sumBalance = 0 Then
        Me!fin_trans_id.Locked = True
        Me!fin_trans_tstamp.Locked = True
        Me!fin_trans_description.Locked = True
        Me!frm_0_accs_fin_trans_journ_subf.Locked = True
End If

If Me!sumBalance = 0 Then
        Me!fin_trans_id.Locked = True
        Me!fin_trans_tstamp.Locked = True
        Me!fin_trans_description.Locked = True
        Me!frm_0_accs_fin_trans_journ_subf.Locked = True
    Else
        Me!fin_trans_id.Locked = False
        Me!fin_trans_tstamp.Locked = False
        Me!fin_trans_description.Locked = False
        Me!frm_0_accs_fin_trans_journ_subf.Locked = False
End If

I would have expected both of the VBA codes would result in the main forms controls and the sub form would be unlocked in every other condition than when the control sumBalance = 0.

But when the sumBalance field is empty due to no records in the sub form, the form is also locked preventing from making new records.

What am I missing?

Solution

It cannot check records not being there, so first count the records:


Dim Locked As Boolean

If Me!NameOfYourSubformCONTROL.Form.RecordsetClone.RecordCount > 0 then
    Locked = (Nz(Me!sumBalance.Value, 0) = 0)
End If

Me!fin_trans_id.Locked = Locked
Me!fin_trans_tstamp.Locked = Locked
Me!fin_trans_description.Locked = Locked
Me!frm_0_accs_fin_trans_journ_subf.Locked = Locked