Clear bound fields on form

[mcupito]

I have a form where users can enter a new record (or multiple).

If a user decides to not enter anything, it keeps throwing errors (my exceptions to prevent null fields from being entered).

The first control is a combobox that is the FK in the table, and the rest of the fields are just fields in the table (name, DOB, etc).

If I wanted to clear all fields so the user can exit the form without saving anything to the table, how can I perform this?

I tried Me.Combbox.Clear (clear isn't an available method), Me.Combobox.Text = "", etc.


Here is the code:

Me.EmployeeID = Null
 
Me.EmployeeID.SetFocus
Me.EmployeeID.ListIndex = -1
 
    If IsNull(Me.EmployeeID.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenFirstName.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenLastName.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenSSN.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenStreet.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenCity.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenState.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    ElseIf IsNull(Me.BenZip.Value) = False Then
        MsgBox ("Please delete all fields or press escape.")
    Else
        DoCmd.Close
        DoCmd.OpenForm "Home"
    End If
 

Nothing has worked thus far.

When the user goes to delete the text in a field, it also throws the error, which is very annoying.

Any ideas? Thanks.

[jimatqsi]

mcupito,
Separate the logic for a form close and a save. You don't say where this code is. Maybe it is in the click event of a save button? In the form Close event you could

docmd.Close acForm,me.Name,acSaveNo

That would prevent save logic from being executed, which might prevent your code above from running - depends where it is.

Jim

[jimatqsi]

In the form close you'd probably want to add a message "You're about to throw away your last change" with a chance to cancel.

Jim

[mcupito]

Apologies, Jim.

I have a button that I'm using to trigger the On Click event, to go back to a different form and not save.

I tried your code but the record still saved.
Here's what I tried.

Private Sub Home_Click()
 
Dim Cancel As Integer
Dim alrtMsg As Integer
 
alrtMsg = MsgBox("Do you wish to discard your record(s)?", vbYesNo, "Warning")
 
If alrtMsg = vbYes Then
DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.OpenForm "Home"
Else
Cancel = True
End If
 
End Sub

[mcupito]

So, I found this.

"...the acSaveNo option, means that you don't save any design changes of the form, and has no influence on the record."

[jimatqsi]

Oh, oops, I knew that, I think. What you need is

docmd.undo

I think that's it.

What I've found in the past is that if you have any fields that have default values, the action of the default value being placed in the text box or other object initiates a new record. So anything that would typically save changes tries to save an unfinished record if you don't actually do anything to complete the record.

I'm not really sure Undo will overcome that but it may. You might also change the default process to operate from code within the form's before Insert event.

Jim

[mcupito]

I appreciate it, you helped me out regardless.

I kind of took an unnecessarily "safe" approach.
Any time the record was going to be saved, I prompt the user.
See below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
  If MsgBox("Do you wish to save the record(s)?", vbOKCancel, "Save?") = vbCancel Then
    Me.Undo
  End If
End If
End Sub
 
Private Sub Home_Click()
 
Dim Cancel As Integer
Dim alrtMsg As Integer
 
alrtMsg = MsgBox("Are you sure you want to exit?", vbYesNo, "Warning")
 
If alrtMsg = vbYes Then
Me.Undo
DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.OpenForm "Home"
Else
Cancel = True
End If
End Sub