Skip update on 'OnError'

[Ham]

I want to do some special processing when there is an error (duplicate key). I can do this in the 'OnError' event, but then it tries to update anyway. Is there a way to tell Access to just forget the whole thing and not try to update the record? (What I really want to do is bring up the exisitng record with that key.) Thanks. -- Ham Rutledge

 

[jbehrne]

Hi,

You can try something like this:

- Set you your key field's "after update" event:

Dim RecNum
'Lookup table to see if value exists
If DLookup("[KeyField]", "TableName", "[KeyField] = Me!TextboxName&quot = True Then
MsgBox "Duplicate Key Entered", vbCritical, "Duplicate Value"
MsgBox "Going to existing Record!", vbOkOnly

'This requeries form and displays selected record
RecNum = Me!KeyField
Requery
Forms!FormName.RecordsetClone.FindFirst "FormName.KeyField = " & RecNum
Forms!FormName.Bookmark = Forms!FormName.RecordsetClone.Bookmark
Else
MsgBox "New Record.", vbOkOnly, "New Record Entered"
Me!KeyField.SetFocus
End If

Hope this helps,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Ham]

Thanks for the reply. It still tries to update the duplicate record when it does the requery. The form is bound to a bunch of data, which is probably why it's acting like this. I had hoped to be able to do this without unbinding it all and having to do everything in VBA. However, it looks like I'm going to have to bite the bullet and dig in.

Thanks for the help. -- Ham Rutledge

 

[jbehrne]

What is it trying to update?

jbehrne


If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Ham]

It's trying to update the file with the data in the form. The key field already exists in the database, so I get the error. -- Ham

 

[jbehrne]

Ham,

I have been thinking about this and playing with the code... Have you tried creating an unbound form with a combobox that gets its values for the key field? (Basically it's control would be a query like this: Select TableName.KeyField From TableName). Then create a subform on that unbound form that displays the contents of your othe form. Then create a link between the combobox and your keyfield. When the user selects a value from the combobox it would automatically display the record. If the user then enters a new value you would then go to a new record on the subform - but it would avoid the whole issue above. If you would like to see a sample db then send me a e-mail to: jbehrne@hotmail.com

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations