Validation Tests

Topic

New Reply

I have a frmReservationEntry whose data source is qryCustomerSorted. Among the fields on the form are CustomerStatus, CustomerID, ReservationNumber (alphanumeric field), ResBegDate, ResEndDate. There are 7 CustomerStatus codes.
When entering data, I want to perform some validation tests.
1. If CustomerStatus is any of the 1st five fields, I want to prevent any entry into the ReservationNumber, ResBegDate, and ResEndDate fields
2. If CustomerStatus is 6, I want to insure that the ReservationNumber is 11 characters long.
3. If CustomerStatus is 7, I want to make ReservationNumber equal to the CustomerID number
4. If ReservationNumber is populated, then dates must be entered into ResBegDate and ResEndDate fields

Any suggestions as to the best place to perform these tests and the syntax would be greatly appreciated.

Tom

Reply | Quote

Replies

I have a frmReservationEntry whose data source is qryCustomerSorted. Among the fields on the form are CustomerStatus, CustomerID, ReservationNumber (alphanumeric field), ResBegDate, ResEndDate. There are 7 CustomerStatus codes.
When entering data, I want to perform some validation tests.
1. If CustomerStatus is any of the 1st five fields, I want to prevent any entry into the ReservationNumber, ResBegDate, and ResEndDate fields
2. If CustomerStatus is 6, I want to insure that the ReservationNumber is 11 characters long.
3. If CustomerStatus is 7, I want to make ReservationNumber equal to the CustomerID number
4. If ReservationNumber is populated, then dates must be entered into ResBegDate and ResEndDate fields

Any suggestions as to the best place to perform these tests and the syntax would be greatly appreciated.

Tom

Tom,

1. After Update event of CustomerStatus control. Syntax should be very simple, to disable the other fields. You may also need an On Current event.
2. Before Update event of ReservationNumber control. If cmbCustomerStatus = 6 then…….If nz(LEN(txtReservationNumber))11 then…
3. As 1.
4. Form’s Before Update event is probably the best place.

Regards,

Reply | Quote

Part of it can be done in the After Update event of CustomerStatus:

Code:

Private Sub CustomerStatus_AfterUpdate()
  Select Case Me.CustomerStatus
	Case 1 To 5
	  Me.ReservationNumber = Null
	  Me.ResBegDate = Null
	  Me.ResEndDate = Null
	  Me.ReservationNumber.Enabled = False
	  Me.ResBegDate.Enabled = False
	  Me.ResEndDate.Enabled = False
	Case 7
	  Me.ReservationNumber.Enabled = True
	  Me.ResBegDate.Enabled = True
	  Me.ResEndDate.Enabled = True
	  Me.ReservationNumber = Me.CustomerID
	Case Else
	  Me.ReservationNumber.Enabled = True
	  Me.ResBegDate.Enabled = True
	  Me.ResEndDate.Enabled = True
  End Select
End Sub

You also need code in the On Current event of the form:

Code:

Private Sub Form_Current()
  Select Case Me.CustomerStatus
	Case 1 To 5
	  Me.ReservationNumber.Enabled = False
	  Me.ResBegDate.Enabled = False
	  Me.ResEndDate.Enabled = False
	Case Else
	  Me.ReservationNumber.Enabled = True
	  Me.ResBegDate.Enabled = True
	  Me.ResEndDate.Enabled = True
  End Select
End Sub

For the rest of the checks, you can use the Before Update event of the form:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.CustomerStatus = 6 And Not Len(Me.ReservationNumber) = 11 Then
	Me.ReservationNumber.SetFocus
	MsgBox "Reservation number should be 11 characters long", vbExclamation
	Cancel = True
	Exit Sub
  End If
  If Not IsNull(Me.ReservationNumber) Then
	If IsNull(Me.ResBegDate) Then
	  Me.ResBegDate.SetFocus
	  MsgBox "Please enter a beginning date", vbExclamation
	  Cancel = True
	  Exit Sub
	End If
	If IsNull(Me.ResEndDate) Then
	  Me.ResEndDate.SetFocus
	  MsgBox "Please enter an end date", vbExclamation
	  Cancel = True
	  Exit Sub
	End If
  End If
End Sub

Reply | Quote

Many thanks. The code works perfectly.

I have added 2 more fields. Can you make some fields mandatory once the are enabled. In the case of Case 6, the original 3 fields plus the 2 new fields would be mandatory. In the case of Case 7, only the original 3 fields would be mandatory.

Tom

Reply | Quote

I’d add code to the Before Update event of the form:

Code:

If Me.CustomerStatus = 6 Then
  If IsNull(Me.NewField1) Then
	Me.NewField1.SetFocus
	MsgBox "Please enter a value for NewField1", vbExclamation
	Cancel = True
	Exit Sub
  End If
  If IsNull(Me.NewField2) Then
	Me.NewField2.SetFocus
	MsgBox "Please enter a value for NewField2", vbExclamation
	Cancel = True
	Exit Sub
  End If
End If

Reply | Quote

Thanks. I see now that the mandatory tests were in the Before Update event in your original code for some fields. Thanks for the additional lesson.

Tom

I’d add code to the Before Update event of the form:

Code:

If Me.CustomerStatus = 6 Then
  If IsNull(Me.NewField1) Then
	Me.NewField1.SetFocus
	MsgBox "Please enter a value for NewField1", vbExclamation
	Cancel = True
	Exit Sub
  End If
  If IsNull(Me.NewField2) Then
	Me.NewField2.SetFocus
	MsgBox "Please enter a value for NewField2", vbExclamation
	Cancel = True
	Exit Sub
  End If
End If

Reply | Quote

If I try to close the form without entering data in either of the two new fields, I get the error message to enter the correct data. When I click OK, I get another error message: You can’t save this record at this time… if you close now data will be lost. Do you want to close anyway? If you answer “No”, the error message disappears. The focus is on the field with the missing data. If correct data is entered, the form closes correctly.

Private Sub Form_BeforeUpdate(Cancel As Integer)
‘The BeforeUpdate event allows you to test to see
‘if a record exists before inserting it a second time.
If Me.NewRecord = True Then
Dim strWhere As String, strMessage As String
strWhere = “LastName = ” & Chr(34) & Me!LastName & Chr(34)
Me.RecordsetClone.MoveFirst
Me.RecordsetClone.FindFirst strWhere
Do Until Me.RecordsetClone.NoMatch
If Me.RecordsetClone!FirstName = Me!FirstName Then
strMessage = “This guest is already in the database. ”
strMessage = strMessage & ” Please verify the information being entered.”
strMessage = strMessage & ” Click OK and the original record will be displayed.”
MsgBox strMessage, vbInformation, “Matching Record”
Cancel = True
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If
Me.RecordsetClone.FindNext strWhere
Loop
End If

If Me.CustomerStatusID = 6 And Not Len(Me.ReservationNumber) = 11 Then
Me.ReservationNumber.SetFocus
MsgBox “Reservation number must be 11 characters long”, vbExclamation
Cancel = True
Exit Sub
End If
If Not IsNull(Me.ReservationNumber) Then
If IsNull(Me.ResBegDate) Then
Me.ResBegDate.SetFocus
MsgBox “Please enter a begining date”, vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.ResEndDate) Then
Me.ResEndDate.SetFocus
MsgBox “Please enter an end date”, vbExclamation
Cancel = True
Exit Sub
End If
End If
If Me.CustomerStatusID = 6 Then
If IsNull(Me.IATANumber) Then
Me.IATANumber.SetFocus
MsgBox “Please enter a valid IATA Number”, vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.ResID) Then
Me.ResID.SetFocus
MsgBox “Please select a Reservation Source from the pull down menu”, vbExclamation
Cancel = True
Exit Sub
End If
End If

End Sub

Thanks. I see now that the mandatory tests were in the Before Update event in your original code for some fields. Thanks for the additional lesson.

Tom

Reply | Quote

You should disable closing the form through the x in the upper right corner, and only allow the user to close the form if the current record has been saved.

Reply | Quote

I have added a Close form button to the form. I also changed the form property “controlbox” to No. Is the most effective way to disabled closing the form through the x in the upper right hand corner?

Tom

You should disable closing the form through the x in the upper right corner, and only allow the user to close the form if the current record has been saved.

Reply | Quote

Setting the Control Box property to No is a good idea.

For extra security you can add a variable at the top of the form module:

Private fClose As Boolean

In the On Click code of the command button:

Code:

Private Sub cmdClose_Click()
  fClose = True
  DoCmd.Close acForm, Me.Name
End Sub

and in the On Unload event of the form:

Code:

Private Sub Form_Unload(Cancel As Integer)
  Cancel = Not fClose
End Sub

fClose will initially be False, so even if the user presses Ctrl+F4, the form will not close. When the user clicks the command button, the form will close.

Reply | Quote

I have not tried using the variable yet but have the following code on the close button

Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click

DoCmd.Close acForm, “frmReservationEntry”, acSaveYes

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub

I am still having the problem that I get the correct error message but when I click OK, the form closes without giving me the opportunity to correct the data entry.

Tom

Setting the Control Box property to No is a good idea.

For extra security you can add a variable at the top of the form module:

Private fClose As Boolean

In the On Click code of the command button:

Code:

Private Sub cmdClose_Click()
  fClose = True
  DoCmd.Close acForm, Me.Name
End Sub

and in the On Unload event of the form:

Code:

Private Sub Form_Unload(Cancel As Integer)
  Cancel = Not fClose
End Sub

fClose will initially be False, so even if the user presses Ctrl+F4, the form will not close. When the user clicks the command button, the form will close.

Reply | Quote

Try this:

Code:

Private Sub Close_Form_Click()
  If Me.Dirty Then
	MsgBox "Please save the record before closing the form.", vbExclamation
	Exit Sub
  Else
	DoCmd.Close acForm, "frmReservationEntry"
  End If
End Sub

Reply | Quote

I think the If me.Dirty check to see if the information has been changed but not saved. The code lets me correct the data but then stops. Do I need to have a separate button to save the data? I take it that it can’t be done with just the close button.

Tom

Try this:

Code:

Private Sub Close_Form_Click()
  If Me.Dirty Then
	MsgBox "Please save the record before closing the form.", vbExclamation
	Exit Sub
  Else
	DoCmd.Close acForm, "frmReservationEntry"
  End If
End Sub

Reply | Quote

Yes, Me.Dirty is True if the current record has unsaved changes.

Instead of checking for Me.Dirty, you could repeat the tests from the Form_BeforeUpdate event in the Close_Form_Click event procedure. If a test fails, use Exit Sub instead of Cancel = True (you can’t use Cancel here, because Close_Form_Click doesn’t have a Cancel argument.)
If the record passes all tests, it will be saved automatically when the form is closed.

Reply | Quote

Thanks I will repeat the tests.

Tom

quote name=’HansV’ date=’11-Jul-2009 12:08′ post=’784143′]
Yes, Me.Dirty is True if the current record has unsaved changes.

Instead of checking for Me.Dirty, you could repeat the tests from the Form_BeforeUpdate event in the Close_Form_Click event procedure. If a test fails, use Exit Sub instead of Cancel = True (you can’t use Cancel here, because Close_Form_Click doesn’t have a Cancel argument.)
If the record passes all tests, it will be saved automatically when the form is closed.
[/quote]

Reply | Quote

Here is some code I use to prevent having to duplicate large amounts of checks:

Code:

Private Sub cmdClose_Click()
  Dim Cancel As Integer
  If Me.Dirty Then
	Form_BeforeUpdate Cancel
  End If
  If Not Cancel Then
	DoCmd.Close acForm, Me.Name, acSaveNo
  End If
End Sub

where cmdClose is the name of the command button. The advantage is that if you want to modify the checks, you have to do it in only one place: the Form_BeforeUpdate event procedure.

Reply | Quote

My Code on the Close form button is

Private Sub Close_Form_Click()
If Me.CustomerStatusID = 6 And Not Len(Me.ReservationNumber) = 11 Then
MsgBox “Reservation number must be 11 characters long”, vbExclamation
Me.ReservationNumber.SetFocus
Exit Sub
End If
If IsNull(Me.ReservationNumber) Then
MsgBox “Reservation number must be 11 characters long”, vbExclamation
Me.ReservationNumber.SetFocus
Exit Sub
End If
If Not IsNull(Me.ReservationNumber) Then
If IsNull(Me.ResBegDate) Then
MsgBox “Please enter a begining date”, vbExclamation
Me.ResBegDate.SetFocus
Exit Sub

End If
If IsNull(Me.ResEndDate) Then
Me.ResEndDate.SetFocus
MsgBox “Please enter an end date”, vbExclamation
Exit Sub

End If
End If
If IsNull(Me.ResID) Then
Me.ResID.SetFocus
MsgBox “Please select a Reservation Source from the pull down menue”, vbExclamation
Exit Sub

End If
If Me.CustomerStatusID = 6 And Me.ResID 11 Then
Me.IATANumber.SetFocus
MsgBox “Before Update”
Exit Sub
End If

DoCmd.Close acForm, “frmReservationEntry”
End Sub

For test purposes I entered the Msg “Before Update” in the last test for the IATANumber so I would know where the msg was coming from.
With CustomerStatusID=6 and ResID = 5, when I click on he Close Form button, the error message “Before Update” appears. When I click on the Close Form Button again, the same error message appears and the form will not close.

If I change the settings to CustomerStatusID = 6 and ResID = 11, the form closes.

Any suggestions?

Tom

Yes, Me.Dirty is True if the current record has unsaved changes.

Instead of checking for Me.Dirty, you could repeat the tests from the Form_BeforeUpdate event in the Close_Form_Click event procedure. If a test fails, use Exit Sub instead of Cancel = True (you can’t use Cancel here, because Close_Form_Click doesn’t have a Cancel argument.)
If the record passes all tests, it will be saved automatically when the form is closed.

Reply | Quote

If Me.CustomerStatusID = 6 And Me.ResID 11 Then
Me.IATANumber.SetFocus
MsgBox “Before Update”
Exit Sub
End If

DoCmd.Close acForm, “frmReservationEntry”
End Sub

For test purposes I entered the Msg “Before Update” in the last test for the IATANumber so I would know where the msg was coming from.
With CustomerStatusID=6 and ResID = 5, when I click on he Close Form button, the error message “Before Update” appears. When I click on the Close Form Button again, the same error message appears and the form will not close.

If I change the settings to CustomerStatusID = 6 and ResID = 11, the form closes.

Er, what is the problem? I’d say the code does exactly what you tell it to do…

Reply | Quote

What I am trying to accomplish is that when CustomerStatusID is 6 and ResID is 11 then IATANumber can be blank. If CustomerIDStatus is 6 and ResID is anything other than 11, then IATANumber must be populated.

Tom

Er, what is the problem? I’d say the code does exactly what you tell it to do…

Reply | Quote

You don’t test the IATANumber. Does this do what you want?

Code:

If Me.CustomerStatusID = 6 And Me.ResID  11  And IsNull(Me.IATANumber) Then
  Me.IATANumber.SetFocus
  MsgBox "Please enter an IATA Number"
  Exit Sub
End If

Reply | Quote

I have re-written the last 2 If Statements but still can not get them to execute properly.
What I am trying to accomplish is
1. If the ResID is 11, there can be not IATA Number and the form closes when the close button is pressed
2. If ResID 11, there must be an IATA Number and it must be 8 characters long and the form need to close when the close form button is pressed

Testing has produced the following results
1. If ResID = 11 and there is no IATA Number, it clears the IATA Number but the Error message repeats each time the close button is pressed. The form never closes.
2. If ResID 11, it does not test to see if the IATA Number is 8 characters long or that there is an IATA Number. The form closes when the Close button is pressed

Private Sub Close_Form_Click()
If Me.CustomerStatusID = 6 And Not Len(Me.ReservationNumber) = 11 Then
MsgBox “Reservation number must be 11 characters long”, vbExclamation
Me.ReservationNumber.SetFocus
Exit Sub
End If
If IsNull(Me.ReservationNumber) Then
MsgBox “Reservation number must be 11 characters long”, vbExclamation
Me.ReservationNumber.SetFocus
Exit Sub
End If
If Not IsNull(Me.ReservationNumber) Then
If IsNull(Me.ResBegDate) Then
MsgBox “Please enter a begining date”, vbExclamation
Me.ResBegDate.SetFocus
Exit Sub

End If
If IsNull(Me.ResEndDate) Then
Me.ResEndDate.SetFocus
MsgBox “Please enter an end date”, vbExclamation
Exit Sub

End If
End If
If IsNull(Me.ResID) Then
Me.ResID.SetFocus
MsgBox “Please select a Reservation Source from the pull down menu”, vbExclamation
Exit Sub

End If

If Me.CustomerStatusID = 6 And ResID = 11 Then
Me.IATANumber.SetFocus
MsgBox “There can be no IATA Number with a Genares Reservation source”, vbExclamation
Me.IATANumber = Null
Exit Sub
End If

If IsNull(Me.IATANumber) And Not Len(Me.IATANumber) = 8 Then
Me.IATANumber.SetFocus
MsgBox “IATA Number must be 8 characters long.”, vbExclamation
Exit Sub
End If

DoCmd.Close acForm, “frmReservationEntry”
End Sub

What I am trying to accomplish is that when CustomerStatusID is 6 and ResID is 11 then IATANumber can be blank. If CustomerIDStatus is 6 and ResID is anything other than 11, then IATANumber must be populated.

Tom

Reply | Quote

Please read my previous reply.

Reply | Quote

Try this for the last part of the code:

Code:

...
  If Me.CustomerStatusID = 6 Then
	If Me.ResID = 11 Then
	  Me.IATANumber = Null
	ElseIf IsNull(Me.IATANumber) Then
	  Me.IATANumber.SetFocus
	  MsgBox "Please enter an IATA number", vbExclamation
	  Exit Sub
	ElseIf Not Len(Me.IATANumber) = 8 Then
	  Me.IATANumber.SetFocus
	  MsgBox "The IATA number should be 8 characters long", vbExclamation
	  Exit Sub
	End If
  End If
  DoCmd.Close acForm, "frmReservationEntry"
End Sub

Note that there is no Exit Sub in the part where ResID = 11.

Reply | Quote

I got the code to work but as usual you solution is more compact. Thank so much for your assistance. I will use your new code.

Tom

Try this for the last part of the code:

Code:

...
  If Me.CustomerStatusID = 6 Then
	If Me.ResID = 11 Then
	  Me.IATANumber = Null
	ElseIf IsNull(Me.IATANumber) Then
	  Me.IATANumber.SetFocus
	  MsgBox "Please enter an IATA number", vbExclamation
	  Exit Sub
	ElseIf Not Len(Me.IATANumber) = 8 Then
	  Me.IATANumber.SetFocus
	  MsgBox "The IATA number should be 8 characters long", vbExclamation
	  Exit Sub
	End If
  End If
  DoCmd.Close acForm, "frmReservationEntry"
End Sub

Note that there is no Exit Sub in the part where ResID = 11.

Reply | Quote

I am try to To check a field to determine if it is
1. Null
2. the Len of the field is between 5 and 8 characters.

My code is

Private Sub IATANumber_LostFocus()
If IsNull(Me.IATANumber) Or Len(Me.IATANumber) 8 Then
Me.IATANumber.SetFocus
MsgBox “Please enter a valid IATA Number Between 5 and 8 characters”, vbExclamation
Cancel = True
Exit Sub
End If
End Sub

When you exit the field, the code does not perform the tests. Any suggestions?

Tom

I got the code to work but as usual you solution is more compact. Thank so much for your assistance. I will use your new code.

Tom

Reply | Quote

1. You should use Or instead of And (the length can’t be less than 5 and more than 8 at the same time)

If IsNull(Me.IATANumber) Or Len(Me.IATANumber) 8 Then

2. You can’t use the On Lost Focus event to set focus to the control you’re leaving.

3. Cancel is not an agument of the On Lost Focus event procedure.

4. Exit Sub doesn’t do harm, but it is useless here since there is no executable code after it.

You should use the Before Update event:

Code:

Private Sub IATANumber_BeforeUpdate(Cancel As Integer)
  If IsNull(Me.IATANumber) Or Len(Me.IATANumber)  8 Then
	MsgBox "Please enter a valid IATA Number Between 5 and 8 characters", vbExclamation
	Cancel = True
  End If
End Sub

or the Before Update event of the form.

Reply | Quote

I don’t see any change in the second On Click code.

Tom

I think the If me.Dirty check to see if the information has been changed but not saved. The code lets me correct the data but then stops. Do I need to have a separate button to save the data? I take it that it can’t be done with just the close button.

Tom

Reply | Quote