Problem trying to trap error in AfterUpdate event

[Guest]

I'm having a problem with trying to trap an error on a form that I've
created. I"m a "code newbie", so please keep that in mind.

Here's some background:

The database will contain records of certain diseases. The data will be
entered by county nurses, but before they enter all the data, I'd like to
check to make sure the data hasn't been entered already (sometimes there are
duplicates of the paperwork floating around). We tried putting a big "Save
Record" button on the form, but they were tabbing past it

Once they enter the info that goes in the primary key, I'd like it to try to
write the record to the database. That way, if it's a duplicate, it brings
up an error. My thought was to create an event on the AfterUpdate property
of the last field in the primary key, and write the record then

Here's what's throwing me--if the record adds successfully, it works fine.
If it's a duplicate, the code goes to the error handling section, but it
tells me that DataErr=0
and it never displays an error message.

Here's the code I've bashed together:

Private Sub Last_Name_AfterUpdate()

' checks for duplicate record
Dim DataErr As Integer
Dim Response As Integer
On Error GoTo Err_Add_Case_Click

DoCmd.GoToRecord , , acNewRec 'tries to add record to table
DoCmd.GoToRecord , , acPrevious 'returns to just added record
'to continue data entry

Exit_Last_Name_afterUpdate:
Exit Sub

Err_Add_Case_Click:

Select Case DataErr
Case 3022
MsgBox "This case is already in the system."
Me.Undo
Response = acDataErrContinue

Case Else
Response = acDataErrDisplay

End Select

Resume Exit_Last_Name_afterUpdate
End Sub

Thanks in advance for any help!

 

[Douglas J. Steele]

Just because you've named a variable DataErr doesn't mean that it's going to
hold the value of the error code.

You should be using:

Select Case Err.Number

As well, your checking should be in the BeforeUpdate event of the form: you
really shouldn't assume what order the users will be inputting data. By
putting the check in the BeforeUpdate event, you can take advantage of the
fact that you can cancel the update if you need to (by setting Cancel =
True)

 

[Allen Browne]

Instead of moving record, you can force the save like this:
Me.Dirty = False

Use the Error event of the form to trap the specific error. (The AfterUpdate
event will only tell you that the Dirty property could not be set if the
save fails.)

An alternative approach might be to DLookup() the table and see if the
particular combination exists, rather than see if the save fails.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[almostfm]

(For some reason, the Microsoft portal isn't showing this thread, so
I'm replying via Google Groups)

Instead of moving record, you can force the save like this:
Me.Dirty = False

Use the Error event of the form to trap the specific error. (The AfterUpdate
event will only tell you that the Dirty property could not be set if the
save fails.)

An alternative approach might be to DLookup() the table and see if the
particular combination exists, rather than see if the save fails.

Just because you've named a variable DataErr doesn't mean that it's going to
hold the value of the error code.

You should be using:

Select Case Err.Number

As well, your checking should be in the BeforeUpdate event of the form: you
really shouldn't assume what order the users will be inputting data. By
putting the check in the BeforeUpdate event, you can take advantage of the
fact that you can cancel the update if you need to (by setting Cancel =
True)

Douglas and Allen,

Thank you both very much! I tried the changes you both suggested,
and it works exactly the way I want it to!

take care,
Scott