Cancel button with validation checks

[NewKidontheBlock]

I have a data entry form from which I want the user to be able to abort
without entering records. A message box has been set to ask if the user wants
to abort or save and this is fine. The problem comes when the user wants to
save the data but hasn't entered all of the required items. I have coded the
following sub into the form's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you want to save the data you have entered?", vbYesNo,
"Confirm Changes") = vbNo Then
Cancel = True
Me.Undo
Else
If Len(Trim(Nz(dteDOB))) = 0 Then
MsgBox "Cannot return to main menu without a date of birth." &
vbNewLine & "Please enter date of birth."
dteDOB.SetFocus
ElseIf Len(Trim(Nz(txtNHSNumber))) = 0 Then
MsgBox "Cannot return to main menu without an NHS Number." &
vbNewLine & "Please enter NHS Number."
txtNHSNumber.SetFocus
ElseIf Len(Trim(Nz(numHosp))) = 0 Then
MsgBox "Cannot return to main menu without a Hospital Number." &
vbNewLine & "Please enter a Hospital Number."
numHosp.SetFocus
ElseIf Len(Trim(Nz(txtAdd1))) = 0 Then
MsgBox "Cannot return to main menu without the first line of an
address." & vbNewLine & "Please enter an address line."
numHosp.SetFocus
ElseIf Len(Trim(Nz(txtCity))) = 0 Then
MsgBox "Cannot return to main menu without the town/city being
entered." & vbNewLine & "Please enter a town or city."
txtCity.SetFocus
ElseIf Len(Trim(Nz(txtPostcode))) = 0 Then
MsgBox "Cannot return to main menu without the postcode being
entered." & vbNewLine & "Please enter a valid postcode."
txtCity.SetFocus
ElseIf Len(Trim(Nz(txtHomeTel))) = 0 And Len(Trim(Nz(txtWorkTel))) = 0
Then
MsgBox "Cannot return to main menu without a phone number being
entered." & vbNewLine & "Please enter a valid phone number."
txtHomeTel.SetFocus
ElseIf Len(Trim(Nz(cboKeyDrID))) = 0 Then
MsgBox "Cannot return to main menu without a GP being entered." &
vbNewLine & "Please enter a valid phone number."
txtHomeTel.SetFocus
ElseIf Len(Trim(Nz(txtType))) = 0 Then
MsgBox "Cannot return to main menu without a type being entered." &
vbNewLine & "Please enter a type."
cboType.SetFocus
End If
End If
End Sub

What happens is that the user clicks on the Close Form button. The message
box asks if the data is to be saved or not. The user says, "Yes", and the
message box appears and points out that a date of birth, for example, has not
been entered. As soon as the user clicks on OK the form closes down.

I originally had this coding in the Close Form's OnClick event but, then it
insisted on do all the validation checks irrespective of whether the user
wanted to save the data or not.

Kind regards

Tony

 

[TonyT]

Hi,

You haven't specified the cancel event for each case of failing your
validation rules, ie set Cancel = -1 to prevent the record from being saved
when the user clicks OK.

hope this helps,

TonyT..

 

[missinglinq via AccessMonster.com]

I ran into this exact same problem a while ago trying to do the same thing!
Unfortunately, I can't give really you an easy fix. I solved the problem in
the following manner:

First off, I always remove all of Access' means of navigating thru records as
well as exiting forms, and replace these functions with my own command
buttons.

When my record becomes Dirty (from editing or entering a new data on a new
record) instead of a messagebox I make 2 command buttons become visible, a
save and an abort or undo button. I then Disable all my navigation and exit
buttons. The user now has to either abort save the record.

If the user presses Abort, the Me.Undo runs, the save and abort buttons
become invisible, and the navigation and exit buttons become Enabled again.

If the user presses Save, I run my validation code, any deficiencies are
addressed, then the save and abort buttons become invisible, and the
navigation and exit buttons become Enabled again.

I also use the webpage approach of placing a bright color large asterisk to
the left of all required fields.

It's not perfect, but it's one approach! Maybe someone will have an easier
hack.