A little Access VBA help? Validating against duplication of a non key field?

Question

I am adding a part name to the database using a form, What code do I put behind the Add part button to validate against duplicate part names? (part number is the primary key) I think I need another recordset to search and compare the table but i'm a bit lost, any help would be great.

Private Sub btn_add_Click()
rs_parts.AddNew

With rs_parts
!partrno = lbl_partno.Caption
!Name = txt_name
rs_parts.update
end with

Answer 1

Should you add a unique index to the part name field?

If you want, you can create a query on your part table with one column for the name and a parameter for the criteria they've entered in txt_name. Then pass the value they entered as a parameter to the query and see if you get any results.

Answer 2

You could use the DCount() function to see whether txt_name is already present in your table. Use your existing code to add the Name when DCount() = 0. Otherwise, inform the user, and bypass the old code.

Private Sub btn_add_Click()
    If DCount("[Name]", "YourTable", "[Name] = """ & Me.txt_name & """") = 0 Then
        rs_parts.AddNew
        With rs_parts
            !partrno = lbl_partno.Caption
            !Name = txt_name
            rs_parts.update
        end with
    Else
        'show the user a MsgBox '
    End If
End Sub

I think Beth's point is a good one: add a unique index to the Name field if you don't want duplicate values.

If your textbox control, txt_name, is only used for new Name entries, use the DCount approach to check its value in the control's After Update event. That could be better than waiting until they click btn_add to tell them they've entered a duplicate Name value.

Answer 3

I've discussed my approach to this before, and given an example form for adding a new record.

I use an unbound form to collect the information needed to create the new record, and have it check for duplicates, and present a list of them to the user so the user can decide what to do.

In this case, it sounds like a unique index is in order, so you won't really need to worry about close matches. I would probably still use an unbound form to capture the new value and run the check before even attempting to add it. In that case, I'd just notify the user that it's a dupe.