By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Clear bound fields on form

P: 294
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:

Expand|Select|Wrap|Line Numbers
  1. Me.EmployeeID = Null
  3. Me.EmployeeID.SetFocus
  4. Me.EmployeeID.ListIndex = -1
  6.     If IsNull(Me.EmployeeID.Value) = False Then
  7.         MsgBox ("Please delete all fields or press escape.")
  8.     ElseIf IsNull(Me.BenFirstName.Value) = False Then
  9.         MsgBox ("Please delete all fields or press escape.")
  10.     ElseIf IsNull(Me.BenLastName.Value) = False Then
  11.         MsgBox ("Please delete all fields or press escape.")
  12.     ElseIf IsNull(Me.BenSSN.Value) = False Then
  13.         MsgBox ("Please delete all fields or press escape.")
  14.     ElseIf IsNull(Me.BenStreet.Value) = False Then
  15.         MsgBox ("Please delete all fields or press escape.")
  16.     ElseIf IsNull(Me.BenCity.Value) = False Then
  17.         MsgBox ("Please delete all fields or press escape.")
  18.     ElseIf IsNull(Me.BenState.Value) = False Then
  19.         MsgBox ("Please delete all fields or press escape.")
  20.     ElseIf IsNull(Me.BenZip.Value) = False Then
  21.         MsgBox ("Please delete all fields or press escape.")
  22.     Else
  23.         DoCmd.Close
  24.         DoCmd.OpenForm "Home"
  25.     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.
Apr 2 '14 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,240
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
Expand|Select|Wrap|Line Numbers
  1. 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.

Apr 3 '14 #2

Expert 100+
P: 1,240
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.

Apr 3 '14 #3

P: 294
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Home_Click()
  3. Dim Cancel As Integer
  4. Dim alrtMsg As Integer
  6. alrtMsg = MsgBox("Do you wish to discard your record(s)?", vbYesNo, "Warning")
  8. If alrtMsg = vbYes Then
  9. DoCmd.Close acForm, Me.Name, acSaveNo
  10. DoCmd.OpenForm "Home"
  11. Else
  12. Cancel = True
  13. End If
  15. End Sub
Apr 3 '14 #4

P: 294
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."
Apr 3 '14 #5

Expert 100+
P: 1,240
Oh, oops, I knew that, I think. What you need is
Expand|Select|Wrap|Line Numbers
  1. 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.

Apr 3 '14 #6

P: 294
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.Dirty Then
  3.   If MsgBox("Do you wish to save the record(s)?", vbOKCancel, "Save?") = vbCancel Then
  4.     Me.Undo
  5.   End If
  6. End If
  7. End Sub
  9. Private Sub Home_Click()
  11. Dim Cancel As Integer
  12. Dim alrtMsg As Integer
  14. alrtMsg = MsgBox("Are you sure you want to exit?", vbYesNo, "Warning")
  16. If alrtMsg = vbYes Then
  17. Me.Undo
  18. DoCmd.Close acForm, Me.Name, acSaveNo
  19. DoCmd.OpenForm "Home"
  20. Else
  21. Cancel = True
  22. End If
  23. End Sub
Apr 3 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.