Restore a form field's value

[Jeff H]

I want to ensure that users don’t accidentally delete patient names in a form then save the record. I’ve added the following code to the Form_BeforeUpdate event, but it does not restore the original values to the fields. I have an Undo Changes button that uses an embedded macro with the UndoRecord command to restore all previous values, and it works fine. But in this case I only want to restore the First and Last name fields.

Code: Select all

If Not IsNull(Me.PID) Then
    If IsNull(Me.FirstName) Or IsNull(Me.LastName) Then
        MsgBox "First and Last Names are required."
        Me.FirstName.Undo
        Me.LastName.Undo
        Me.FirstName.SetFocus
        DoCmd.RepaintObject acForm, "frmPatients"
        blnQuit = True
        Cancel = True
        Exit Sub
    End If
End If

In the instance above, I’ve added blnQuit in order to bypass the Close command initiated by the cmdClose button which triggers the Form_BeforeUpdate event.

Code: Select all

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
blnQuit = False

Exit_cmdClose_Click:
Me.Refresh
If blnQuit Then Exit Sub
DoCmd.Close
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub 

[HansV]

Should the user be able to edit the first and last name? If not, you could lock the associated controls.

[Jeff H]

Yes, they should be able to edit the records in this form, including names.

As a side note, I had forgotten that I'd used the embedded macro for the Undo Changes button until I was troubleshooting this problem today. But I vaguely remember that I went with the macro in that case because the vba I tried to use to revert to a form's original values didn't work but the UndoRecord macro command did.

[HansV]

As far as I can tell, you cannot undo changes to individual fields in the Before Update event of the form, so I fear you cannot do what you want.
I'd set the Required property of the FirstName and LastName fields to Yes.

[Jeff H]

Hmm. I got it from Microsoft Docs here. The article is "TextBox.Undo" and for the syntax it says expression is a "variable representing a TextBox object". However, they give one example as Me!TextBox.Undo in the textbox's Change event, and another as Me.Undo in a form's BeforeUpdate. That's why I thought it would work on a textbox in the form's BeforeUpdate.

Anyway, the control's Required property is probably a better way to go anyway. I will try that.

Thanks,
- Jeff

[HansV]

You can use the Undo method of a control in that control's Before Update event, and you can use the Undo method of a form in that form's Before Update event.

[Jeff H]

Unfortunately I don't have time to try these out now, but as soon as I get a chance I will.

Meanwhile, I tried to do a quick test of using the required property in the table, but when I went back to the form it allowed me to delete both names and close the form without any error. It saved the records with no names.

I'll need some time to play with this.

[Jeff H]

Setting the Required (yes) and Allow Zero Length (no) properties works, but I don't see how to catch that error so I can display my own message and restore the original value right away. I tried the textbox BeforeUpdate, Change, and Exit events. Without restoring the value I can't do any other edits or close the form.

[Jeff H]

... On the other hand, I just tried a simple

Code: Select all

If IsNull(Me.FirstName) Then Me.Undo

in the textbox BeforeUpdate event, as per your other post, and that seems to work fine.

I'm going to do some tests with that to be sure it will work in practice.

As always, Hans, you get me unstuck. Thanks!

- Jeff