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

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 woul开发者_如何学God 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

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.

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.