I have a continuous form with, amongst others, has 3 fields, Date field, Name field and Time field. If one of these 3 fields has had data entered, then to other 2 fields require data. I achieved this by using the following code on the forms’ Before Update property which works fine;
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull([Date]) And IsNull([Name]) Then
[Name].SetFocus
MsgBox "The name must be entered in the Name’ field."
Cancel = True
Else
If Not IsNull([Date]) And IsNull([Time]) Then
[Time].SetFocus
MsgBox "The time must be entered in the 'Time' field."
Cancel = True
Else
If Not IsNull([Name]) And IsNull([Date]) Then
[Date].SetFocus
MsgBox "The date must be entered in the 'Date' field."
Cancel = True
Else
If Not IsNull([Name]) And IsNull([Time]) Then
[Time].SetFocus
MsgBox "The time must be entered in the 'Time' field."
Cancel = True
Else
If Not IsNull([Time]) And IsNull([Date]) Then
[Date].SetFocus
MsgBox "The date must be entered in the 'Date' field."
Cancel = True
Else
If Not IsNull([Time]) And IsNull([Name]) Then
[Name].SetFocus
MsgBox " The name must be entered in the Name’ field."
Cancel = True
End If
End If
End If
End If
End If
End If
End Sub
To prevent a date in the future being entered in the Date field, I have used the following code in the fields’ Exit property
Private Sub Date_Exit(Cancel As Integer)
If Date.Value > Date Then
MsgBox "You cannot enter a date in the future!", vbExclamation
Cancel = True
End If
End Sub
The issue I have is that if a user enters a date in the Date field then realises that he/she has entered data in the wrong record and deletes the date entered then tries to moves the cursor to another field the message "You cannot enter a date in the future!" keeps appearing.
How do I work around this?
Many thanks