can't save record error

[Guest]

On looking through this discussion board I've found a number of questions
relating to this problem, but the solutions don't seem to work in my
situation.
I've got a continuous form called send_receive_list which includes two
checkboxes for received and sent and text boxes for dates received and sent.
I require the 'date sent' field to be completed if the corresponding check
box is ticked, and the same logic for the 'date received' field. The user can
close the form by clicking on either the 'go to record' button or 'return to
main menu' button - each of which use macros to close & open forms.
On the form update event, I've entered the following code to display an
error message:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!CC2005rec = True And IsNull(Me!CC2005daterec) Then
MsgBox "Please confirm the date card received", vbOKOnly
Me!CC2005daterec.SetFocus
Me!CC2005daterec = Date
Cancel = True
Else

If Me!CC2005sent = True And IsNull(Me!CC2005datesent) Then
MsgBox "Please confirm the date card sent", vbOKOnly
Me!CC2005datesent.SetFocus
Me!CC2005datesent = Date
Cancel = True
End If
End If

End Sub

This logic all works fine in terms of detecting the error, but after the
user selects 'okay' on the message box they get the 'you can't save this
record at this time' error. If yes is selected the changes aren't saved and
the person leaves the form. If no is selected, then get the action failed
dialog box with the option to halt the macro. This then returns the user to
the date field to confirm that the current date is correct, or modify it if
necessary.

After that long introduction, my question is how can I stop the macro to
close the form continuing and force the user to enter the date as required.
I've tried various things like removing the 'cancel = true' and Set.Warnings
= False but they only create new and different problems.

Your help is greatly appreciated, because I'm really confused.

PS. I'm a real novice at Access and esp. vb so please keep any suggestions
simple or spell out the process step by step!!!

thanks

 

[Michel Walsh]

Hi,


It seems that it is closing the form that generates the error, not the
Before_Update. Since you try to close the form while the record was dirty
and could NOT be saved, the error is catch in the Form_Error event handler.
The DataErr value is probably 2169 and use the Response argument to specify
a desired behavior for Access:

=============================( from Access 2003 help file)
acDataErrContinue Ignore the error and continue without displaying the
default Microsoft Access error message. You can supply a custom error
message in place of the default error message.

acDataErrDisplay (Default) Display the default Microsoft Access error
message.

=============================


Hoping it may help,
Vanderghast, Access MVP