Trapping Data Errors

[MikeMcKeown]

Hi,

I am trying to catch errors for null primary key or duplicate keys. I have put the following code into the forms OnError event. I am using an adp project with a SQL Server database. I think I am catching two error the SQL Server one and Access one and only displaying one message.

If (DataErr = 2627 Or DataErr = 2169) Then
Response = acDataErrContinue
End If



If (DataErr = 515) Then
If MsgBox("Are you sure you want to exit and not save the current record?", vbOKCancel) = vbOK Then

Cancel = False
Response = acDataErrContinue ' telling Access not to show default message



Else

Cancel = True
Response = acDataErrContinue ' telling Access not to show default message
Me.Form.Undo


End If




End If

This code successfully only displays the message box once and if the user selects ok, the form closes and does not save anything. Else if the user selects cancel, I want them to be able to return to the form and correct their mistakes. However the form just closes as well.

Any ideas

 

[Trendsetter]

I would not be happy with "Null Primary Key or Duplicates"

Your code later on must have some form of closimg code which is forcing the form to close - you need to bypass that code if the user selects no!

If (DataErr = 515) Then
If MsgBox("Are you sure you want to exit and not save the current record?", vbOKCancel) = vbOK Then
Cancel = False
Response = acDataErrContinue ' telling Access not to show default message
Else
Cancel = True
Response = acDataErrContinue ' telling Access not to show default message
Me.Form.Undo
Exit Sub ' will jump past any other code in this section.
End If
End If

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[MikeMcKeown]

I have pressed to close the form and then the error comes up. I therefore want to cancel the close command is this possible?

 

[danvlas]

Close cannot be canceled. However, you can use the Unload event which has the Cancel argument.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PHV]

BTW, you don't have any validation procedure in the BeforeUpdate and/or the BeforeInsert event procedure of the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikeMcKeown]

I have tested for null fields ub BeforeUpdate, however the form still proceeds to close. I have used the following code:-

ElseIf IsNull(StreetName) = True Then
MsgBox ("Please fill in the STREET NAME")
Me.StreetName.BackColor = vbCyan
Cancel = True

How do you prevent the form from closing? Am I missing something?

 

[PHV]

Perhaps a DoCmd.CancelEvent ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244