Checking for duplicate before inserting

[TimE]

I would like to check if a value already exists in a table BEFORE I insert it (using VBA). Can it be done (and recommended)?

 

[RuralGuy]

Absolutely, usually in the BeforeUpdate event of the control with the value, using either the DCount() or DLookup() function. If a duplicate is detected then set Cancel = True to hold the user in the control and then give them a message about the duplicate.

 

[rainman89]

is this to avoid duplicates?
if so u can set the option to no duplicates in the table design!

 

[RuralGuy]

is this to avoid duplicates?
if so u can set the option to no duplicates in the table design!

Ray,
What you say is true but then you have to catch the 3022 error in your error handler and to be quite frank, I'm not sure what event to catch that error if you are not using SQL or a RecordSet.

 

[TimE]

Thank you for your quick responses.

I want to avoid duplicates and notify the user up front. The main form has 1 field to enter an item #. Below that is a selection list that is generated from the results of SQL statement that includes info about the item #. When the item in the selection list is double clicked, it is added to a subform table (temp table) at the bottom. So, when double clicked, I want it to verify that it does not exist in the temp table and does not exist in the main table.

 

[rainman89]

Ray,
What you say is true but then you have to catch the 3022 error in your error handler and to be quite frank, I'm not sure what event to catch that error if you are not using SQL or a RecordSet.

Yeah i saw your post right after i sent mine in.. what u said was going to be my next suggestion, if the table design was not an option
Thanks RG

 

[rainman89]

so if dcount(temptable) OR dcount(mainTable) > 0 throw the error message

 

[TimE]

I quess I do not quite understand. Using one table for now.

    If DCount("[ITEM_NUM]", "tblTEMP", Me.ITEM_NUM) > 0 Then
        MsgBox "Item # is already listed.", vbOKOnly + vbCritical, _
            "DUPLICATE FOUND"
        
        Exit Sub
    
    End If

I guess what is throwing me off is my lack of access vba knowledge and passing the value of the ITEM_NUM on the form to the VBA code.

 

[RuralGuy]

Private Sub ITEM_NUM_BeforeUpdate(Cancel As Integer)

If DCount("[ITEM_NUM]", "tblTEMP", "[ITEM_NUM] = '" & Me.ITEM_NUM & "'") > 0 Then
   MsgBox "Item # is already listed.", vbOKOnly + vbCritical, _
              "DUPLICATE FOUND"
   Cancel = True
End If

End Sub

If ITEM_NUM is a Text field.

 

[TimE]

Thanks to you both. I found another thread with the code needed. boblarson also responded to another one of my issues on how to pass the value. Someday I will actually "get" it.