Notifying user of duplicate name, Access 2003

[dawnd3]

Ok, here is what I need and I am not sure of the code to use or where to put the code. The user wants a message box to pop up if they happen to enter a duplicate last name. So, if they enter "Smith" for the last name, they would want a message box that shows a list of all the Smith's in the database so that they can be sure it isn't a duplicate. And then be able to cancel or continue. What is the best way to do this? The reason they only want it to compare on last names is because sometimes the first name is written differently (Bob vs Robert) or it is a spouse which would be considered a duplicate for their business. Please hand hold me through this. I am someone that needs step-by-step instructions.

Thank you very much,

Dawn

 

[ClydeData]

Dawn
do not add data direct on a bound form use DAO. this here is a good example

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=374

Also prior to adding data use an if then else with a dcount
[COLOR=blue ]
If DCount("*","YourTableName","[NewSurname] = " & forms!YourForm!NewSurnameTextBox) > 0 Then

message to say surname exists

Else

DAO code to add new person



End If

[/color]



Hope this helps

JImmy

 

[TheAceMan1]

How are ya dawnd3 . . .

I think it would be better to ping on the first & last names together! This way you'll have a better sense of [blue]uniqueness in full names[/blue] and know wether something needs to be done. Also, checking fullnames will occur alot less providing somoother uninterrupted data entry!

[blue]DLookUp[/blue] would be the key function to use here.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeeThru]

I've got a simlar one. It has an unbound text box for the name entry. It splits this into first and last names, uses SoundEx to convert them to alphanumric codes to allow for mis-spellings.

It them compares to the existing table. If no match exists, it creates the new record, dropping the names into hte correct fields. If at least one match exists, it pops up another form with the lists of names - these can be selected (and the main form jumps to that record), or a command button allows the name entered to be added as a new one anyway.

Very basic code....

For the main form - opens in DataEntry, but all the fields re disabled:

strFirstName = firstnamefield
strLastName = lastnamefield
strWHERE = "(FirstName='" & strFirstName & "') OR (LastName='" & strLastName & "')"

'open search form - dialog mode with WHERE Clause to limit
'Add section to check for 0 record here if needed
DoCmd.OpenForm "ContactsSearch", acNormal, , strWHERE, acFormReadOnly, acDialog

'One form has closed (been made invisible) check the value of a hidden field with the action to take
Select Case Forms!contactssearch!txtMode
Case "AddNew" 'add a new record
    DoCmd.GoToRecord , , acNewRec
    Title = ExtractTitle(EnterNewName)
    FirstName = ExtractFName(EnterNewName)
    LastName = ExtractLName(EnterNewName)
Case "Cancel"
    'do nothing
Case "Select"
    'find the selected record and view
    Me.Filter = "ContactRef=" & Forms!contactssearch!ContactRef
    Me.FilterOn = True
Case Else
    MsgBox "An error occurred adding a new record!", vbCritical, "Contact Add"
End Select
DoCmd.Close acForm, "ContactsSearch"
[code]


SeeThru  [url=http://www.synergyconnections.co.uk]
Synergy Connections Ltd - Telemarketing Services[/url]

 

[TheAceMan1]

So what happen to [blue]dawnd3![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]