Check current database for an existing record based on last name

How complicated it is to have a database do the following:
when a new contact record is being typed and a last name matches one that's already in the database a dialog box will pop up and read "last name matches another in database" "would you like to check others before saving this record.
then the already designed contact list can pop up so that the person can see if the contact really is in database. then let person go back to original form and continue entering info or exiting that record.
I tried talking them into using a unique field like phone number and the record just doesn't save if the number is duplicated, but boss wants to use last name.. and have a pop up of all the "smiths or jones" I would settle just to call up contact list and let him scroll. :)
thanks for your help, if there is some code and it isn't too much trouble could you highlight the lines or areas that I would have to input my table name or field names, I sometimes spend hours on missing changing the code in one line.
thanks again, I appreciate your help


CherylecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can search a table using a simple DLookup:

If Nz(DLookup("YourIdField", "YourTable" "LastName='" & Me.YourSearchTextbox & "'"), "") <> "" Then
  '/ Found a match
  Msgbox "A match was found for " & me.YourSearchTextbox
Else
  '/ No match found - show your contact list
End If

However, in most cases you can use a simple Combobox and set the LimitToList value = True, and then use the NotInList event to add the record. See this article:

http://support.microsoft.com/kb/197526
0
CherylecAuthor Commented:
thanks,
getting a little error Compile error: expected list separator or )
I've tried several changes: adding ) adding , and removing = sign, no luck

Private Sub TRecruitLastName_AfterUpdate()
f Nz(DLookup("Trecruitlastname", "Qrecruittask" "TrecruitLastName='") , & Me.YourSearchTextbox & "'"), "") <> "" Then
  '/ Found a match
  MsgBox "A match was found for " & Me.YourSearchTextbox
Else
  '/ No match found - show your contact list
End If
End Sub
0
Rey Obrero (Capricorn1)Commented:
try this

private sub TRecruitLastName_AfterUpdate()

if dcount("*","Qrecruittask" "TrecruitLastName='" & Me.YourSearchTextbox & "'") > 0 then

MsgBox "A match was found for " & Me.YourSearchTextbox

else

end if

end sub

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
missing comma


try this

private sub TRecruitLastName_AfterUpdate()

if dcount("*","Qrecruittask", "TrecruitLastName='" & Me.YourSearchTextbox & "'") > 0 then

MsgBox "A match was found for " & Me.YourSearchTextbox

else

end if

end sub
0
CherylecAuthor Commented:
please excuse my inexperience here
but I don't know what the Me.YourSearchTextbox" is referring to and when I finish typing in the last name field I get a compile error and that section is highlighted.
0
Rey Obrero (Capricorn1)Commented:
Cherylec,

you have a textbox where you type the last name, right ? what is the name of the textbox?

you will replaced  "YourSearchTextbox"  with the name of the textbox..

0
CherylecAuthor Commented:
I am typing last name into the "TRecruitLastName" field on the form. I did add that field name but it didn't like that much either. I probably didn't explain this correctly,
I'm entering this info on a form that has its control source set to "Qrecruittask" As I type the last name and tab out of the field, I was hoping the database could look at the Qrecruittask and see if that last name already exists. should it look at the table instead of the query
0
Rey Obrero (Capricorn1)Commented:
try using the beforeUpdate event


private sub TRecruitLastName_BeforeUpdate(cancel as integer)

if dcount("*","Qrecruittask", "TrecruitLastName='" & Me.TRecruitLastName & "'") > 0 then

MsgBox "A match was found for " & Me.TRecruitLastName
Cancel=true
Me.TRecruitLastName.setfocus
exit sub
else

end if

end sub
0
CherylecAuthor Commented:
OK works if there isn't a match!
If there is a match, I get runtime error 2108 "you must save the field before you execute the GOTOControl action, method or setfocus method
should I go back to after update? just a guess on my part
0
Rey Obrero (Capricorn1)Commented:
what exactly the codes that you used.. post them here
0
CherylecAuthor Commented:
Private Sub TRecruitLastName_BeforeUpdate(Cancel As Integer)

If DCount("*", "QRecruittask", "TrecruitLastName='" & Me.TRecruitLastName & "'") > 0 Then

MsgBox "A match was found for " & Me.TRecruitLastName
Cancel = True
Me.TRecruitLastName.SetFocus
Exit Sub
Else

End If

End Sub
0
Rey Obrero (Capricorn1)Commented:
remove this line

Me.TRecruitLastName.SetFocus
0
CherylecAuthor Commented:
OK that finds that there is a duplicate but I need to do the following, as originally posted
1.if there is a duplicate, the person entering data will have to click a button (already on form) to open the recruit list to see if its just a last name match or if it is indeed a duplicate recruit. Most cases we will have identical last names (but this is how they want to search it anyway). the code won't let me get passed the error message box.
2. I need to be able to actually enter more than one of a last name. There may be two smiths. right now the code will not let me progress to enter the record because there is a match.
or maybe the code should just check three fields first and last names and address. either way I need to be able to do something after there is a match.
open to suggestions thanks

0
Rey Obrero (Capricorn1)Commented:
this code will just give you the message, but allow you to continue


Private Sub TRecruitLastName_BeforeUpdate(Cancel As Integer)

If DCount("*", "QRecruittask", "TrecruitLastName='" & Me.TRecruitLastName & "'") > 0 Then

MsgBox "A match was found for " & Me.TRecruitLastName
 
Else

End If

End Sub


you should used the form's before update event to check for the duplicate using the lastName, firstName, address or whatever fields, but you should think about this first on which fields you want to check for duplicates,
and when you thought of what you really want to do, post another question and layout all the requirements




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CherylecAuthor Commented:
Didn't have complete instructions for creating the entire scenario I wanted in my original post. But I will get better at asking for exactly what I need.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.