Determine the existence of a record being added

[Cedarguy]

I have a table called FAMILY which is at the top of the relationship chain. When I add a new FAMILY via the form associated with it, I need to determine that the family doesn't already exist. Which control can I use (and how) on say the NAME field, that would list for me all the names that match the partial entry I make. For instance, if I'm adding David Brown and enter "Da", all names starting with "Da" should be listed so I can visually verify whether or not David Brown exists in the table.

I know all this is a lot to ask but I'm also certain that this is nothing new, so if I can be referred to an existing example, that would be awesome.

Thanks

 

[bob fitz]

Is "David Brown" stored in one field or two fields (FirstName, LastName).

I need to determine that the family doesn't already exist.

What if you get a second "David Brown".

 

[Cedarguy]

Thanks Bob; I'm using Access to assign unique keys so I shouldn't have a duplication problem but I take your point and should/will separate first and last for no other reason than to avoid future pitfalls that I'm unaware of.
Any thoughts on the best way to determine the existence of the name(s) at data entry stage?
Regards,

 

[jdraw]

Have you considered a Dcount(..." where PersonName = incoming PersonName ">0 in the BeforeUpdate event on a form?

It isn't uncommon to have 2 0r 3 Jim Smith or Dave Brown, so you may need more than Name to differentiate Person 1 from Person 2 (duplicates).

 

[bob fitz]

If you store the First and Last names in two fields of each record and might allow the name "David Brown" to be enterd more than once (because the user knows this is another person with the same name), then I think I would use the form's Before Update event to check if a record for a name already exists and if it does, then ask the user to confirm that a new record with duplicate name is to be made.

If there are no circumstances where you will allow the entry of a duplicate name the this can be done at table level by setting the Index of the two fields together.