handling a duplicate key error from field to field

[Guest]

I have a form with three fields. The first is a text field where the user
names the item they are working on - this is a primary key, no dupes allowed.
The next field is a date field they fill in. On this date field I have an
"after update" event procedure to save the record (using DoCmd.RunCommand
acCmdSaveRecord) then runs an update query to populate the third field with
some data. It is necessary to do this at this point so the user sees the
result of the update. It doesn't work if the record is not saved first. The
problem is that after the "save record" code procedure, if the text they have
typed in the first field is a duplicate key they get the long-winded default
duplicate key message. I know how to create an error handling procedure that
after you get to the end of the form and try to move to the next record you
can have a custom error message and return them to the field that needs to be
changed. But this only attaches to the "on error" property for the form. I
have tried to create an error handler that will do the same kind of thing
after the field in question is updated, but with no success. I have tried to
follow some examples, but the result is always the same - the default
message. How can I accomplish what the form "on error" handling routine
accomplishes back after the field update?

Thanks,
Jeff

 

[J]

Private Sub txtItem_AfterUpdate()
If Dlookup("[IDField]","Table","[IDField]='" & txtItem & "'") <> 0
then
txtItem = ""
txtItem.Setfocus
Endif
End Sub


Why are you saving the record to update the field with a query? Why
don't you just set the control itself to what you want it to be? It's
a lot less taxing on the system if you use:
txtThirdField = Expression

As opposed to:
DoCmd.RunSQL "UPDATE Table "... blah blah blah

on the second field's afterupdate event.


Hope that helps,
~J

 

[Guest]

You would be better off checking the validity of the 1st text field in it's
after_Update event, and using a message box there to warn to user that they
must change the item name, similarly with the date field, then swap
docmd.runcommand...... with;
If me.Dirty Then
Me.Dirty = False
End If

TonyT..

 

[Guest]

The dlookup worked fine. I actually simplified the expanation in my post as
to why I needed to run the update query, but this solves the problem. Thank
you!

Jeff

Private Sub txtItem_AfterUpdate()
If Dlookup("[IDField]","Table","[IDField]='" & txtItem & "'") <> 0
then
txtItem = ""
txtItem.Setfocus
Endif
End Sub


Why are you saving the record to update the field with a query? Why
don't you just set the control itself to what you want it to be? It's
a lot less taxing on the system if you use:
txtThirdField = Expression

As opposed to:
DoCmd.RunSQL "UPDATE Table "... blah blah blah

on the second field's afterupdate event.


Hope that helps,
~J

I have a form with three fields. The first is a text field where the user
names the item they are working on - this is a primary key, no dupes allowed.
The next field is a date field they fill in. On this date field I have an
"after update" event procedure to save the record (using DoCmd.RunCommand
acCmdSaveRecord) then runs an update query to populate the third field with
some data. It is necessary to do this at this point so the user sees the
result of the update. It doesn't work if the record is not saved first. The
problem is that after the "save record" code procedure, if the text they have
typed in the first field is a duplicate key they get the long-winded default
duplicate key message. I know how to create an error handling procedure that
after you get to the end of the form and try to move to the next record you
can have a custom error message and return them to the field that needs to be
changed. But this only attaches to the "on error" property for the form. I
have tried to create an error handler that will do the same kind of thing
after the field in question is updated, but with no success. I have tried to
follow some examples, but the result is always the same - the default
message. How can I accomplish what the form "on error" handling routine
accomplishes back after the field update?

Thanks,
Jeff

 

[Guest]

As I just posted, that is basically what I am doing using the dlookup
suggested above. Thanks for answering!

Jeff