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

Question

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?

Could eliminate repetition of Me by using With Me ... End With wrapper. Use dot instead bang when referencing controls in VBA. Could use textbox and combobox Conditional Formatting to enable/disable. ID controls should probably always be locked or don't even show ID. sumBalance can never be Null because of Nz() function setting control to empty string - an empty string is < 0. In my test, textbox is empty string when subform does not have records. Have you step debugged? — June7
– June7, Commented Mar 6, 2024 at 5:23
Simplify code. Set a Boolean variable and reference that in setting Locked property. booLock = .sumBalance = 0 then 4 lines to lock/unlock like .frm_0_accs_fin_trans_journ_subf.Locked = booLock — June7
– June7, Commented Mar 6, 2024 at 5:37
I did some testing. When I set a breakpoint and step through code it works properly, but without breakpoint it goes haywire. I don't have solution. — June7
– June7, Commented Mar 6, 2024 at 6:35

Gustav · Accepted Answer · 2024-03-06 07:03:17Z

2

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

answered Mar 6, 2024 at 7:03

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

June7 Over a year ago

Great idea. Nz() is probably not necessary since if there are records, sumBalance should always have a value. But it doesn't hurt.

Lasse Staalung Over a year ago

Worked like a charm, also without Nz(). Thanks, a lot.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related