Duplicate Record Handling

[SQLSister]

I have a continuous form for vendor part data. When users are adding parts I would like the system to check the model number to see if it exists for that vendor and then tell tham it is a duplicate record.

At this point I want the message box to give them the option to edit the record or go to the existing model number.

I have everything working except the part about going to the existing record. I had intended to use the find record box to have the person go to the record. However, it will not work as it wants me to save my current record which it can't do because it violates the primary key.

Now I figured, fine, I'll just use the esc key sequence to get to the point where the potential record has been escaped from, then it should go to the find form, no problem. Nope. It will esc all right, then even though I can go to the find form directly from the form, I can't make it do so in code. SO my escape sequence works just fine at returning the form to the right state, it is just that the command to run the find form,

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

seems to pull the value that is no longer in the record (because I escaped out of it) and tries to save it before opening the form.

So I say, I'll try running the undo record command.

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Nope. It tells me that the record can't be undone in this state. Grrrr.

Now all this is happening in the field BeforeUpdate event. Is there a better event to try or a better method for doing this or should I just be satisfied with clearing the record and let the user do a manual find if he or she wants to go to the existing part to edit it?

Any thoughts would be helpful as I am out of ideas to try. If anyone has succeeded in doing this, I would be interested in knowing the approach they took.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[PHV]

Have you tried the ComboBox wizard ?

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

 

[RoyVidar]

Just did a very crude test on something like this, having the controls before update event looking something like this:

[tt]dim lngPK as long
lngPK=me!txtPK.value
if (not isnull(dlookup("id", "mytable","id=" & lngPK))) then
me.undo
me.recordsetclone.findfirst "id = " & lngPK
end if[/tt]

Which worked

Roy-Vidar

 

[SQLSister]

Roy, that looks interesting, of course modelnumber isn't a numeric value and I won't have the autogenerated number in a blank record, so I'll have to play with it some, but it looks like it might solve my problem.

PHV, I'm not using a combo box, but I'm curious as to why you think the wizard will help me.

Thank you both for the time to help me out as I was just stuck. This front end stuff is just not my thing.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[PHV]

Sorry SQLSister, I've missread your post.
I though you wanted the code to retrieve a record.