No Current Record

[gaugust]

In Access 2003 I would like to validate an incomplete form when then user
closes the form and allow the user to correct missing field data. What is
happening is that the "No Current Record" error appears when the DoCmd.Close
is triggered in the click event for the form's close button. In the form's
unload event I have the following code:

'Validate date and time
If IsNull(Me.Date) Then
If MsgBox("Unload - Date is missing, if not corrected data will not
be saved, Do you want to enter a date?", vbYesNo, "Missing Date") = vbYes Then
Cancel = True
Me.Date.SetFocus
End If
ElseIf IsNull(Me.Time) Then
If MsgBox("Unload - Time is missing, if not corrected data will not
be saved, Do you want to enter a time?", vbYesNo, "Missing Time") = vbYes Then
Cancel = True
Me.Time.SetFocus
End If
End If


If the Date field on the form is missing and the user selects "Yes" in the
message box the cancel works and the focus is placed on the Date field, but
the error message "No current Record" also shows. How can I get rid of the
error message?

Thanks!

 

[Stuart McCall]

In Access 2003 I would like to validate an incomplete form when then user
closes the form and allow the user to correct missing field data. What is
happening is that the "No Current Record" error appears when the
DoCmd.Close
is triggered in the click event for the form's close button. In the form's
unload event I have the following code:

'Validate date and time
If IsNull(Me.Date) Then
If MsgBox("Unload - Date is missing, if not corrected data will not
be saved, Do you want to enter a date?", vbYesNo, "Missing Date") = vbYes
Then
Cancel = True
Me.Date.SetFocus
End If
ElseIf IsNull(Me.Time) Then
If MsgBox("Unload - Time is missing, if not corrected data will not
be saved, Do you want to enter a time?", vbYesNo, "Missing Time") = vbYes
Then
Cancel = True
Me.Time.SetFocus
End If
End If


If the Date field on the form is missing and the user selects "Yes" in the
message box the cancel works and the focus is placed on the Date field,
but
the error message "No current Record" also shows. How can I get rid of the
error message?

Thanks!

The words "Date" and "Time" are Access reserved words and will cause trouble
sooner or later. This may or may not be the cause of the error, but it's
advisable to change the field/control names in any case.

 

[Allen Browne]

You are using the wrong event here. By the time the form's Close event
fires, the record has already been saved, and the form's Unload event has
fired, which is why there's no current record when the Close event is run.

Move your code into the BeforeUpdate event of the *form* (not control.) This
event runs just before the record is saved, which is the last possible
moment you can validate it.

Your close button will then need to contain:
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name
The first line will give an error if the record cannot be saved (so be sure
to include error handling.) For an explanation how how important that line
is to avoid a bug in Access, see:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html