Search and Duplicate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All - I have created a form to capture customer details. When an end user
tries to add the data and put the phone number, I want my application to
first find if the record exists. If it does then it should display the
record. If the record doesn't exist then it should allow user to add the
data. I was able to detect the record exists by below code but not able to
display the entire record for e.g. customer name, address etc.

Private Sub Customer_Phone_AfterUpdate()
Dim mydb
Dim myrecset
Dim SQLString As String

SQLString = "Select * from tblCustomer where Customer_Phone = " & "'" &
Me.Customer_Phone & "'"
Set mydb = CurrentDb
Set myrecset = mydb.OpenRecordset(SQLString)


If myrecset.RecordCount = 0 Then
MsgBox "No Record Found", vbCritical, "CCS Implementation"
Else
MsgBox "Record Found", vbCritical

End If


End Sub

Any gurus out there who can provide some insight I would really appreciate it.
 
Is it possible the 2 customers could share the same phone number? For
example, if an adult child is a customer in their own right, or friends
sharing an address? If so, it makes sense to warn the user of the duplicate,
rather than blocking it.

Of course, there could be more than one record with a duplicate phone
number, but this is the kind of thing you would be looking at:

Private Sub Customer_Phone_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.Customer_Phone) Then
strWhere = "Customer_Phone = """ & Me.Customer_Phone & """"
varResult = DLookup("CustomerID", tblCustomer", strWhere)
If Not IsNull(varResult) Then
strMsg = "Same phone number as customer " & varResult & _
vbCrLf & "Abandon changes and go to that record?"
If MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton2, _
"Possible duplicate") = vbYes Then
Me.Undo
strWhere = "CustomerID = " & varResult
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in form. Filtered?"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If
End Sub
 
Allen - Thanks for the code. The code does similar to what I had earlier
provided. However, it doesn't populate the form with entire record i.e. it
doesn't display any other relevant data of that particular customer like
address, city, st etc. How do I do that. I did write a query and it works but
it doesn't populate the form. To your earlier question about multiple
customers having same number. The requirement I have set is 1 to 1. So phone
number will be my primary key. Thanks for all your help.
 
You could modify the DLookup() to return more than one field like this:
Dim strFieldList As String
strFieldList = "Trim([FirstName] & "" "" & [Surname] & "" "" & [Address]
& "" "" & [City])"
varResult = DLookup(strFieldList, "tblCustomer", strWhere)

If that's not enough, you could OpenForm to show the matching results in a
continuous form.
 
Allen - Thanks for your response and assistance. When I tried to use the code
below it is giving me following error:
Run-time error 2001
You canceled the previous operation

My code is below just as you pointed out also how do I display info on a
continuous form. I think that will be easier because I have used several
different codes but none of them are working. Still learning hard-core access
programming. Thanks for all your help.

Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String


If Not IsNull(Me.Customer_Phone) Then
Dim strfieldlist As String
strfieldlist = "[Customer_First] & [Customer_ID] & [Customer_Phone]"

strWhere = "Customer_Phone = """ & Me.Customer_Phone & """"
varResult = DLookup(strfieldlist, "tblCustomer", strWhere)


If Not IsNull(varResult) Then
strMsg = "Same phone number as customer " & varResult & _
vbCrLf & "Abandon changes and go to that record?"
If MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton2, _
"Possible duplicate") = vbYes Then
Me.Undo
strWhere = "Customer_ID = " & varResult
With Me.RecordsetClone
.FindFirst strWhere

If .NoMatch Then
MsgBox "Not found in form. Filtered?"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If



Allen Browne said:
You could modify the DLookup() to return more than one field like this:
Dim strFieldList As String
strFieldList = "Trim([FirstName] & "" "" & [Surname] & "" "" & [Address]
& "" "" & [City])"
varResult = DLookup(strFieldList, "tblCustomer", strWhere)

If that's not enough, you could OpenForm to show the matching results in a
continuous form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

learningMikey said:
Allen - Thanks for the code. The code does similar to what I had earlier
provided. However, it doesn't populate the form with entire record i.e. it
doesn't display any other relevant data of that particular customer like
address, city, st etc. How do I do that. I did write a query and it works
but
it doesn't populate the form. To your earlier question about multiple
customers having same number. The requirement I have set is 1 to 1. So
phone
number will be my primary key. Thanks for all your help.
Click to expand...
Click to expand...
 
The error indicates that there is a problem with one of the inputs, so
DLookup() is not able to complete its operation.

For example, when you open tblCustomer in design view, do the field names
actually use the underscore, or are they spaces?

(Ultimately you will want to put the spaces back into the string also, but
that can be after you get it working.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

learningMikey said:
Allen - Thanks for your response and assistance. When I tried to use the
code
below it is giving me following error:
Run-time error 2001
You canceled the previous operation

My code is below just as you pointed out also how do I display info on a
continuous form. I think that will be easier because I have used several
different codes but none of them are working. Still learning hard-core
access
programming. Thanks for all your help.

Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String


If Not IsNull(Me.Customer_Phone) Then
Dim strfieldlist As String
strfieldlist = "[Customer_First] & [Customer_ID] &
[Customer_Phone]"

strWhere = "Customer_Phone = """ & Me.Customer_Phone & """"
varResult = DLookup(strfieldlist, "tblCustomer", strWhere)


If Not IsNull(varResult) Then
strMsg = "Same phone number as customer " & varResult & _
vbCrLf & "Abandon changes and go to that record?"
If MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton2, _
"Possible duplicate") = vbYes Then
Me.Undo
strWhere = "Customer_ID = " & varResult
With Me.RecordsetClone
.FindFirst strWhere

If .NoMatch Then
MsgBox "Not found in form. Filtered?"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If



Allen Browne said:
You could modify the DLookup() to return more than one field like this:
Dim strFieldList As String
strFieldList = "Trim([FirstName] & "" "" & [Surname] & "" "" &
[Address]
& "" "" & [City])"
varResult = DLookup(strFieldList, "tblCustomer", strWhere)

If that's not enough, you could OpenForm to show the matching results in
a
continuous form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Allen - Thanks for the code. The code does similar to what I had
earlier
provided. However, it doesn't populate the form with entire record i.e.
it
doesn't display any other relevant data of that particular customer
like
address, city, st etc. How do I do that. I did write a query and it
works
but
it doesn't populate the form. To your earlier question about multiple
customers having same number. The requirement I have set is 1 to 1. So
phone
number will be my primary key. Thanks for all your help.


:

Is it possible the 2 customers could share the same phone number? For
example, if an adult child is a customer in their own right, or
friends
sharing an address? If so, it makes sense to warn the user of the
duplicate,
rather than blocking it.

Of course, there could be more than one record with a duplicate phone
number, but this is the kind of thing you would be looking at:

Private Sub Customer_Phone_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.Customer_Phone) Then
strWhere = "Customer_Phone = """ & Me.Customer_Phone & """"
varResult = DLookup("CustomerID", tblCustomer", strWhere)
If Not IsNull(varResult) Then
strMsg = "Same phone number as customer " & varResult & _
vbCrLf & "Abandon changes and go to that record?"
If MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton2,
_
"Possible duplicate") = vbYes Then
Me.Undo
strWhere = "CustomerID = " & varResult
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in form. Filtered?"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End If
End Sub


message
Hi All - I have created a form to capture customer details. When an
end
user
tries to add the data and put the phone number, I want my
application
to
first find if the record exists. If it does then it should display
the
record. If the record doesn't exist then it should allow user to add
the
data. I was able to detect the record exists by below code but not
able
to
display the entire record for e.g. customer name, address etc.

Private Sub Customer_Phone_AfterUpdate()
Dim mydb
Dim myrecset
Dim SQLString As String

SQLString = "Select * from tblCustomer where Customer_Phone = "
&
"'"
&
Me.Customer_Phone & "'"
Set mydb = CurrentDb
Set myrecset = mydb.OpenRecordset(SQLString)


If myrecset.RecordCount = 0 Then
MsgBox "No Record Found", vbCritical, "CCS
Implementation"
Else
MsgBox "Record Found", vbCritical

End If


End Sub

Any gurus out there who can provide some insight I would really
appreciate
it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top