Prevent Dupilcate Record Entry 1

[khwaja]

I have created a form which has an auto number as primary key but data entry is ordered by Store numbers (LocNo). So I declared this as a 'Required' field and Indexed Yes no duplicates.

When I enter data, I lookup the LocNo using drop down field and continue with remaining data entry. However, interesting enough, when I select a LocNo, it does not stop me selecting one even though I might have entered data for the same location before. I expect to be prevented from moving to next field if record for duplicate LocNo already existed but this did not happen and I continued entering the full record only to get an error message when I moved to next record or if I saved the record just after moving focus. Surely, there must be some way to stop entering data beyond LocNo field. I tried update before and after and On Exit options, but these don't work.

Could someone provide an insight?

Regards

 

[JimHorton]

kh,
You could try a few things. Sounds like you've tried update--this should work unless you have other required fields. If you don't have other required fields, (or *do* have other required fields but have set default values for them), you can trigger the access dup message by running the following in the AfterUpdate of the LocNo field:
DoCmd.RunCommand acCmdSaveRecord

This tries to save, and when it can't due to the dup, then you get the message, which is the desired effect. However......in cases (normal, I assume) where the LocNo is not a dup, you are now left with a saved record without the use of the standard .Undo method. This can be 'undone', but now you're getting into spaghetti.

Perhaps the better way is again to use code on the afterupdate--just create a recordsetclone and do a FindFirst on the newly entered LocNo. If found, show an error message, if not, all is fine.
--Jim

 

[khwaja]

Much much appreciate your help. It works. Just a question being newbie, instead of getting the run time error message, is there any way one can show a customised error message for the user? I created an event procedure and wonder if I can add a couple of more l;ines to get error messages composed by me instead of Access.

Regards

AK

 

[JimHorton]

AK,
In the Form Error event, put an If block that says something like

If DataErr = 3022 Then
msgbox "Hey bud...it's a duplicate!"
Response = acDataErrContinue
End If

--Jim

 

[khwaja]

Thanks Jim. I am now getting two error messages - one as soon as I enter a duplicate LocNo and the other when I get rid of the runtinme error message and close the form which is generated by IF command. I think it is due to the fact that On Error is a form property whereas Update After where we added the test was a control property.

Have I made any mistake? Could we somehow have a single error message?

Cheers

 

[JimHorton]

Ak,
If you have the FindFirst code in the After Update also, then yes, you'll get two messages, I forget to tell you to remove that one. So in After Update of the control just have the acCmdSaveRecord thing, that forces the save and causes the Form Error event to fire if it's a duplicate.

Now, it becomes a decision of what to do with the saved record in the case it's not a duplicate but the user didn't want it anyway. Normally, you could use the .undo method, but now the record is saved. Many different ways to do this, let me know if you need suggestions here.
--Jim

 

[khwaja]

Thanks Jim. I never addedd the FindFirst code. So effectively, the curent situation is similar to what you have decribed. As such, I should not receive first error message but I am. Unless I am making a mistake, I should not end up with two messages!

Cheers

 

[JimHorton]

Ak,
Sorry, I overlooked something. The error trap for the control event should be in the control event--(you were on the right track when you mentioned that above!)

In the AfterUpdate event of LocNo
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Err.Number = 3022 Then
MsgBox "Duplicate"
End If


--Jim

 

[khwaja]

Many thanks for your help.

Kind regards

AK