Please can someone help? I have a form frmAddNewCustomer. I have a text box in it called txtCustomerEmail. When the form closes, I want a message to come up if there is no email in the txtCustomerEmail text box. I want to have two options in the msgBox one to cancel the closing of the form if the person forgot to put the email address in. So, they can go and put it in and an option to continue to close the form and go back to the frmOrderForm that will be open as the frmAddNewCustomer opened from the frmOrderForm. The message will be something like click cancel to fill in the email address or click ok to continue. I tried all different things and the best code I could come up with is wrong but I will paste it.
The other problem I have is I already have code in the Close event. I will attach my Database. Craig
Code:
On Error GoTo ErrorHandler
If (IsNull(txtCustomerEmail)) Then
Beep
MsgBox "Don't forget email address if possible.", vbOKCancel, "Warning!"
ElseIf vbOK Then
DoCmd.Close
End If
CleanUpAndExit:
Exit Sub
ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit
Thank you for your help. The code does work but an error message comes up: Error
An Error was encountered
Description: No current record
No: 3021
I also had to take the Me.refresh out of the one bit of code otherwise the message appears straight after I paste in the CustomerFullName text box. I can live with these problems though at least the code does what I wanted. I appreciate the help. Craig
I won't equivocate. If you actually care that bad data doesn't get saved, you do two things.
1. In the table definition, set required fields to required and if the field is text, set AllowZeroLengthString to No.
2. Validate the data in the BeforeUpdate events - primarily the FORM's BeforeUpdate event as I explain later.
You cannot cancel the save in the event you are using to validate. Therefore, you'll get lots of error messages, none of which do anything to stop the record from being saved.
The event model of Access forms is not arbitrary. There is certain flexibility in that code might do what it needs to do in more than one event even though there is a best choice but regarding validation, that isn't the case. Access sees the saving of data as a personal mission and takes it upon itself to save data in places where you might not anticipate. For example, if you have a main form with a subform, when focus moves from the main form to the subform, Access saves the main form record. That would not activate your close event code. So by the time the close event fires, so what! The data has already been saved. The messages are just noise.
The FORM's BeforeUpdate event is the last event that runs before a record gets saved. It cannot be bypassed. It runs regardless of what prompted the save. Think of it as the flap at the end of a funnel. If the flap is open, the record gets saved. If the flap is closed, the record does not get saved. Easy concept but ONLY if you use the correct event. Your validation code in this event, not only displays error messages, it also closes the flap when errors are found by setting the Cancel property of the event.
Code:
If some validation expression = False
Msgbox "errror", vbOKOnly
Cancel = True
Me.Somefield.setfocus
Exit Sub
End If
No one who uses bound forms ever complains that Access isn't saving their data. The complaint is that Access is saving bad data. But that is the fault of the developer who does not understand his tool. You have total control over whether or not a record gets saved but only if you use the correct event. Some one will surely chime in with but I do x,y,z. OK but I do x. I once removed close to 5,000 lines of code from an application written by someone who didn't understand the event model. This person was a good tester and he could see that bad data was being saved and he was determined to stop it by putting validation code in at least 4 events for every control and he even took to "cascading" the validation and trying to control field to field movement on the forms. control1 validated field1. control2 validated field1 and field 2, control 3 validated field 1, field2, and field3, etc. AND repeated all this validation in all the events. But, he never put a single line of validation in any BeforeUpdate event and so he never succeeded is only saving valid data.
Some validation can be done using the control level BeforeUpdate events but those events are awkward for multi-field validation scenarios and not usable at all for ensuring that a field has been populated because the control level events don't run if the control never receives the focus. So, if you want to perform validation closer to data entry and that makes sense for some types of data, it is fine to use these events but with the exception of preventing duplicates of fields like SSN, I only use the Form level event. It is just easier to have all the validation code in one place. But if you want to distribute the validation, that's fine as long as you still validate required data and multi-field relationships in the Form's BeforeUpdate event.
