databasedev.co.uk - database solutions and downloads for microsoft access

Protecting data on a Microsoft Access form from accidentally being changed:

You may want to protect certain data in controls on your Microsoft Access forms from being changed/edited accidentally. We can provide a mechanism for achieving this and provide the user with a message box to prompt for verification when attempting to enter data into specified controls. By clicking Yes in the message box we can unlock the control, and allow our users to type.

Consider we have the following controls on our Microsoft Access form, where we may want to prevent our users from accidentally changing information in particular controls:

Part of the Microsoft Access form, showing various data entry controls.

In this example, we will demonstrate how to protect the Notes text box shown above from accidentally being edited. To do this we will open the form in Design View. Once there, go to the View menu and Click on the Code menu:

Choosing the Code view from the View menu.

In the code window for the form, we declare the following:

For the OnCurrent property of our form we add the following event procedure:

Private Sub Form_Current()
'Lock the memPropertyNotes control for each record.
    Me!memPropertyNotes.Locked = True
    fEditPropertyNotes = False
End Sub

Now, select the property sheet for the memPropertyNotes field text box, and change the Locked property to Yes:

Setting the Locked property of the text box to Yes

Finally, for the OnKeyDown property of the memPropertyNotes text box add the following event procedure:

The OnKeyDown event procedure

Private Sub memPropertyNotes_KeyDown(KeyCode As Integer, Shift As Integer)
'If the field is not flagged for editing, prompt the user
'for confirmation about unlocking the control.
    If fEditPropertyNotes = False Then
        If MsgBox("Do you want to change the Notes for this property?", _
                  vbYesNo + vbQuestion + vbDefaultButton2, _
                  "Please Confirm:") = vbYes Then

            'If the response is Yes, unlock the field
            'and flag it as being edited.
            Me!memPropertyNotes.Locked = False
            fEditPropertyNotes = True
        End If
    End If
End Sub

To test this procedure, switch to Form view and try to change the contents of the Notes text box.

A message box appears asking whether we want to change the Notes for the chosen Property. If we click Yes, the Notes text box is unlocked and we can edit the contents for the particular record. If we click No, the Notes text box remains locked:

When attempting to edit the Notes field.

The above message appears when we attempt to edit the Notes text box, and asks for confirmation of our action.