opinions on best way to prevent duplicate data

[mic907]

need help setting combo box-based 'search form' - example attached

Hi all: I currently have a database with a list of clients, and it is to be accessible to others to enter information on clients and to add new ones. However, I want to make it so that the user who wants to add a client can easily search the database for that particular client so that they won't enter a duplicate.

I have created a search function on my form, but then it occurred to me: what if there are two people with the same last name's? At first, I wanted a search operation to display a list of client names, along with their respective ID numbers and possibly a link next to each that would take the user to the corresponding record.

However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way!

 

[mhartman]

Hello:

Please see the enclosed link.
http://support.microsoft.com/kb/209479/en-us

Regards
Mark

 

[mhartman]

One more:

http://office.microsoft.com/en-us/access/HP051875641033.aspx

 

[mic907]

thanks mhartman for the great links.

I successfully set my database to disallow duplication of first and last names. however, upon doing so I discovered I need a couple more things (which of course I have no idea how to do):

1) generating a customized message upon entering a duplicate name immediately after entry (ie: in the form, when one enters a first or last name (which are 2 separate fields, and the entire name turns out to be a duplicate, I need a custom error message to come up immediately afterward to inform the user of the error.) Currently, only a default access message comes up, and only when a new record is attempted to be created (or when trying to navigate to another entry).

2) I want to create a search Form that allows a user to search the database for users to see whether or not they are already in the system. I have found an example of this, but have no idea how to create this for my database. I attached an ideal example below

 

[mic907]

someone help please!

 

[mic907]

anyone have any ideas?

 

[gemma-the-husky]

in the beforeupdate event of both txtfirstname and txtlastname boxes call a common function (i called this nameexists) to test whether the name exists, and cancel the update if it does.

you probably already have a form beforeupdate event to ensure both the first name and last names have been entered

'make sure you have two names entered first
if len(txtfirstname)>0 and len(txtlastname)>0 then
   if nameexists(txtfirstname, txtlastname) then
       call msgbox("The selected name is a duplicate ...")
       cancel=vbcancel
   end if
end if

'now you use the nameexists function - this function tests the indexref from the name table of the matching item. If it finds one (ie the dlookup is not 0) the name has already been used and its a duplicate. This assumes your table has a numeric index ref. Change the refs and names etc as appropriate

function nameexists(firstname as string, lastname as string) as boolean
nameexists = dlookup("nameid","nametable","[namefirstname] = " & chr(34) & firstname & " and [namelastname] = " & chr(34) and lastname & chr(34))>0
end function

 

[mic907]

thanks for the help gemma....but you have any idea how that database i attached works?