Dave,
if this form allows data entry, you need to save the data before you allow
them to open the next form.
You would check for duplicates in the before update event of the form.
This is where you can cancel the update if you find duplicates.
To check for duplicates, use a DCount on the table where you store the
last name.
If DCount("[last_name]", "TheTable", "[last_name] = """ &
me.txtSearchLastName & """") >0 then
Cancel = True
'msgbox to tell user about the problem
Else
End if
Be aware that you could have the situation where 2 different people do in
fact have the same last name.
If you do a search in the newsgroups on duplicate names, I am sure you
will find many posts - this is a common problem.
Use similar DCount code to check duplicates for the phone numbers.
Jeanette Cunningham
Dave said:
Yep - that solved it.
Thanks again.
Now - if you want to hang with me one step further (I might not know
much - but I do know to take coding, one step at a time).............
How should I deal with duplicates?
Duplicate Last names
Or
Duplicate Phone numbers (maybe 2 room mates used the same phone)
I am assuming instead of the search button taking me to the the form with
the persons data I would first need to go to a "continuous form" That
would list all the duplicates.
I just can't get my head around how I would code that.
Thanks very much - Again
dave
Jeanette Cunningham said:
Dave,
this small piece of code
& "'"
needs to be added between
Me![txtSearchPhones] & " Or [last_name]=
It is tricky to get it right - we all struggle with counting the quotes
and matching them up.
You can count them, a bit like counting If's and End If's.
Add 1 for each quote in front of textbox reference and subtract 1 for
each quote after a text box reference.
If you get back to 0 at the end of each text box reference, you know you
have paired them up correctly.
Jeanette Cunningham
Jeanette,
Thanks again for the reply but I am still doing something wrong.
This is what I have (ignoring for a moment the possibility of an
apostrophe in the last name)
stLinkCriteria = "[phone_1]= '" & Me![txtSearchPhones] & "'" & " Or
[phone_2]='" & Me![txtSearchPhones] & "'" & " Or [phone_3]='" &
Me![txtSearchPhones] & " Or [last_name]='" & Me![txtSearchLastName] &
"'"
the Me![ ] did not seem to solve the issue
dave
Dave,
the quotes look fine in this one, however the Me! in front of each
textbox is missing
stLinkCriteria = "[phone_1]= '" & Me![txtSearchPhones] & "'" & " Or
[phone_2]='"
& Me![txtSearchPhones] & "'" & " Or [phone_3]='" &
Me![txtSearchPhones] & " Or
[last_name]='" & Me![txtSearchLastName] & "'"
If there is a possibility the any Last name might have an apostrophe
in it, use
"" & Me![txtSearchLastName] & """
expanded for clarity that is
" " & Me![txtSearchLastName] & " " "
Jeanette Cunningham
OK - now I want to take it one step further and add a second
potential
search field:
But this did not work - and this time I don't thik it is a quote
error
stLinkCriteria = "[phone_1]= '" & txtSearchPhones & "'" & " Or
[phone_2]='"
& txtSearchPhones & "'" & " Or [phone_3]='" & txtSearchPhones & " Or
[last_name]='" & txtSearchLastName & "'"
what have I done wrong?
Thanks again
dave
Access 2003
search form frmSearchDonors will have 1 field "txtSearchPhones"
it will also have one button that is meant to open a form with the
donor record containing that phone number in any one of its 3 Phone
fields (Lets assume for a minute that no phone number can have
duplicate donors).
Phone fields are:
phone_1
phone_2
phone_3
This is what I have but it is not working
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmEditDonor"
stLinkCriteria = "[phone_1]=" & "'" & Me![txtSearchPhones] & " ' "
Or "[phone_2]=" & "'" & Me![txtSearchPhones] & "'" Or "[phone_3]=" &
"'" & Me![txtSearchPhones] & " ' "
DoCmd.OpenForm stDocName, , , stLinkCriteria
willing to bet I got quote issues but I can't get it right
Any help here will be appreciated.
Thanks in advance
dave
