Trying to use an indexed field to prevent duplicates, want custom error message

[catluvr]

Hi all!

I have looked at lots of websites with potential answers and none of them fit what I'd like to do.

I have a table of installation specs. There is also a table of part numbers and a another table that links the two together.

The installation specs have an autonumber ID but they also have a unique drawing number. The drawing numbers have already been created by the company and I can't change those.

The form I created to enter the specs is a main form where the user can enter the drawing number and a description. A sub-form allows the entry of part numbers and quantities.

This is all pretty standard .... your basic invoice number and invoice detail except the user is entering the invoice number.

To prevent duplicate drawing numbers, I set the field to index with no duplicates. it works just fine ... except ...

The error about the duplicate value doesn't appear until after the record is saved, which means the user has entered the drawing number, description and a bunch of parts before finding out that the drawing number is a duplicate.

The error message that appears would completely confuse the users.

I used VBA for the lost focus event of drawing number field to save the record ... at least the error pops up before more data is entered.

Now, how do I have a custom error message for just the duplicate index error?

The default message doesn't give me an error number, so I can't use an if-then error handler based on an error number.

Does anyone know the specific error number? I found an old website that said the error was 3022 ... but, I think that was for the JET engine and doesn't Access use ACE now? (if that even matters)

I've tried putting code in the lost focus for the drawing number field based on error number 3022 ... no dice. I tried in in the overall form error event, again didn't work.

Can someone point me in the right direction?

Thank you!



Susie
Kansas

[June7]

Try BeforeUpdate event of drawing number textbox to validate data. Use DLookup to see if value already exists.

[orange]

I agree with June. The BeforeUpdate event is where validation should be done -- last step before value is saved.
You could also use an DCount instead of DLookup
If DCount(your criteria) >0, then a records with that value exists.
So create a Msgbox to inform user of the situation.
Good luck.

[catluvr]

Thank you! Using the BeforeUpdate worked perfectly!

Then, I got ambitious and then I got stuck.

Here is my BeforeUpdate code:

Private Sub Install_Spec_BeforeUpdate(Cancel As Integer)

Dim InstDetCount As Integer
Dim txtInstDet As String


txtInstDet = Me.Install_Spec.Value


InstDetCount = DCount("Install_Detail", "tbl_Inst_Detail", "Install_Detail = '" & Me.Install_Spec & "'")


If InstDetCount > 0 Then
    If MsgBox("This Installation Detail number has already been entered." & vbCrLf & vbCrLf & "Would You like to go to that Installation Detail?", _
        vbExclamation + vbYesNo, "Duplicate Installation Detail") = vbYes Then
        Me.Undo
        DoCmd.FindRecord txtInstDet
    End If



End If


Me.Undo


End Sub

The idea is to go to the record of that installation detail. (or not and clear the field)

The OnCurrent event for the form is a macro that goes to the Install_Spec control (a text box).

The reason I set focus to the Install_Spec control is because, if the focus is in the parts subform when the user goes to another record or starts a new record, the focus remains in the parts subform. I wanted the focus to be on the main form.

When the macro is present, I get a Macro Single Step error. It's error #2108 and the Action Name is GoToControl.

If I remove the GoToControl macro from the OnCurrent event of the form, the code works perfectly.

If I add VBA code (rather than a macro) to the OnCurrent event, I get the same error number and it says the field must be saved before I can execute the SetFocus or GoToControl method.

How can I have my code and eat it, too?

How do I stick with my code AND always have the focus on the Install_Spec text box control when a current record is loaded?


Susie
Kansas

[June7]

Need Cancel = True line before Undo. That's what the Cancel argument is for.

Why not use variable in the DCount expression?

[catluvr]

June7,

Thank you! Code works exactly as I had hoped!

But what did you mean by:

Why not use variable in the DCount expression?

Susie
Kansas

[June7]

InstDetCount = DCount("Install_Detail", "tbl_Inst_Detail", "Install_Detail = '" & txtInstDet & "'")