Locking fields on a form in Microsoft Access

(Updated 2010-09-27)

[ Access Tips | Access | Main ]

In some situations you may want to lock the fields on a form unless the user explicitly choose to update the transactions.   While you could set the forms Allow Edits property to false this doesn't work for subforms.  Other wierdnesses happen but I can't recall exactly what right at the moment.  This solution shows how a call to a public subroutine passing a few parameters handles this situation.

The below form is a continuous form showing all the transactions.  You can either create new transactions or view already existing transactions.  The View button opens the Transaction Header form to the record chosen using standard wizard created code.  The New button opens the Transaction Header form with empty fields.   The code behind the New button passes in an arbitrarily chose "New" value to the forms Open Arguments property 

Private Sub cmdNewTransaction_Click()
On Error GoTo Err_cmdNewTransaction_Click

    DoCmd.OpenForm "Transaction Header", , , , acFormAdd, , "New" ' <<<<<<<<<

Err_cmdNewTransaction_Click:
    MsgBox Err.Description

End Sub

This form shows how the form looks when you are viewing an already existing transaction.  The default behavior is to lock all the fields on the form.

The code is in the forms Open Event.   The argumements passed to the EnOrDis_AbleControlsOnForms routine are:

• Me is the current form object.  This is not the same as the form name.
• tglLocked is the toggle control on the form.
• the True or False is the override on locking the fields

Private Sub Form_Open(Cancel As Integer)

On Error GoTo tagError

    If Me.OpenArgs = "New" Then
        Call EnOrDis_AbleControlsOnForms(Me, tglLocked, True)
        Me.tglLocked.Value = True
    Else
        Call EnOrDis_AbleControlsOnForms(Me, tglLocked, False)
    End If

    On Error GoTo 0
    Exit Sub

tagError:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure Form_Open of VBA Document Form_Transaction Header"
Exit Sub

End Sub

On the below form the user has clicked the toggle button and unlocked the fields on the form.

The code behind the toggle button is:

Private Sub tglLocked_Click()

On Error GoTo tagError

    Call EnOrDis_AbleControlsOnForms(Me, tglLocked, tglLocked.Value)

    On Error GoTo 0
    Exit Sub

tagError:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure " & _
        tglLocked_Click of VBA Document Form_Transaction Header"
Exit Sub

End Sub

The below screen show shows the value in the controls Tag property

This screen shot shows the default value of the Toggle control

Finally the routine which does all the work.  This routine is placed in module and can be called by any form.

Public Sub EnOrDis_AbleControlsOnForms(frm As Form, tgl As Control, _
    Optional Override As Boolean)
' Enable & unlock or disable and lock controls as required. Controls must have Lock in the tag
' tgl is assumed to be a toggle control. Note that the caption will be overwritten
' If Override value is true then the field will be unlocked

Dim ctl As Control, ctlsbf As Control
Dim Locked As Boolean

On Error GoTo tagError

    If Not IsMissing(Override) Then
        Locked = Override
    Else
        Locked = tgl.Value
    End If

    For Each ctl In frm.Controls
        If InStr(ctl.Tag, "Lock") > 0 Then
            ctl.Enabled = Locked
            ctl.Locked = Not Locked
        End If
        If ctl.ControlType = acSubform Then
            For Each ctlsbf In ctl.Form.Controls
                If ctlsbf.Tag = "Lock" Then
                    ctlsbf.Enabled = Locked
                    ctlsbf.Locked = Not Locked
                End If
            Next ctlsbf
        End If
    Next ctl

    If Locked Then
        tgl.Caption = "Unlocked"
    Else
        tgl.Caption = "Locked"
    End If

    On Error GoTo 0
    Exit Sub

tagError:

    Select Case Err.Number
    ' This message happens on a subform control for unknown reasons
    ' something to do with the subform <shrug>
    Case 2164 ' You can't disable a control while it has the focus.
        Resume Next ' ignore
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & _
            ") in procedure EnOrDis_AbleControlsOnForms of Module tt_utils"
    End Select
    Exit Sub
    Resume

    On Error GoTo 0
    Exit Sub

End Sub

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available