Dlookup help for duplicate phone number entry and message box.

J

joecosmides

Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called "Phone".
The CustomerT has a field called "CustomerID" and that is the primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists. We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some Before
Update code that will use Dlookup or Dcount to see if the phone number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words, if
you start typing into the phone field and then erase what you just put
in, the code I am about to paste here will pop an error and debug.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"

If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
 
K

Klatuu

Private Function Form_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With

End Sub
 
K

Ken Sheridan

As far as I can see the only problem is that a Null is being assigned to the
SID variable, which is declared as a String. Use the Nz function to return a
zero-length string in place of the Null:

SID = Nz(Me.Phone, "")

The Phone column's AllowZeroLength property should be set to False (No) in
the table design as otherwise the DCount function would find any other rows
where the value of the Phone column is a zero-length string.

Ken Sheridan
Stafford, England
 
J

joecosmides

Private Function Form_BeforeUpdate(Cancel As Integer)

    With Me.RecordsetClone
        .FindFirst "[Phone] = """ & Me.Phone & """"
        If Not .NoMatch Then    'The Phone number already exists
            Cancel = True
            Me.Undo
'Message box warning of duplication
            MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
                & vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
           Me.Bookmark = .Bookmark
        End If
    End With

End Sub




Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called "Phone".
The CustomerT has a field called "CustomerID" and that is the primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists. We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some Before
Update code that will use Dlookup or Dcount to see if the phone number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words, if
you start typing into the phone field and then erase what you just put
in, the code I am about to paste here will pop an error and debug.
Click to expand...
   Dim SID As String
   Dim stLinkCriteria As String
   Dim rsc As DAO.Recordset
Click to expand...
   Set rsc = Me.RecordsetClone
Click to expand...
   SID = Me.Phone.Value
   stLinkCriteria = "[Phone]=" & "'" & SID & "'"
Click to expand...
       If DCount("Phone", "CustomerT", _
             stLinkCriteria) > 0 Then
       'Undo duplicate entry
       Me.Undo
       'Message box warning of duplication
       MsgBox "Warning Phone Number " _
            & SID & " already exists." _
            & vbCr & vbCr & "You will now been taken to therecord.",
_
              vbInformation, "Duplicate Information"
       'Go to record of original phone number
       rsc.FindFirst stLinkCriteria
       Me.Bookmark = rsc.Bookmark
   End If
   Set rsc = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.
 
K

Klatuu

Is the Phone field in the table a numeric field or a text field?
If it is text, are both fields formatte the same? For example,
555-123-9876 will not match
(555) 123-9876

Private Function Form_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With

End Sub




Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called "Phone".
The CustomerT has a field called "CustomerID" and that is the primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists. We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some Before
Update code that will use Dlookup or Dcount to see if the phone number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words, if
you start typing into the phone field and then erase what you just put
in, the code I am about to paste here will pop an error and debug.
Click to expand...
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Click to expand...
Set rsc = Me.RecordsetClone
Click to expand...
SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"
Click to expand...
If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.
 
J

joecosmides

Is the Phone field in the table a numeric field or a text field?
If it is text, are both fields formatte the same?  For example,
555-123-9876  will not match
(555) 123-9876


Private Function Form_BeforeUpdate(Cancel As Integer)
Click to expand...
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With
Click to expand...
End Sub
Click to expand...
news:[email protected]...
Click to expand...
Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called "Phone"..
The CustomerT has a field called "CustomerID" and that is the primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists. We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some Before
Update code that will use Dlookup or Dcount to see if the phone number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words, if
you start typing into the phone field and then erase what you just put
in, the code I am about to paste here will pop an error and debug.
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"
If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.- Hide quoted text -

- Show quoted text -
Click to expand...

It is a text field and all phone numbers are formatted the same. You
can just type the number into the field in the form and it will put
the dashes into it automatically after you tab out. You can go into
the table and see that all of the phone numbers are simply 7 digit
numbers with dashes like 813-555-1212 due to the Input Mask in the
table whic is 000\-000\-0000
 
J

joecosmides

Is the Phone field in the table a numeric field or a text field?
If it is text, are both fields formatte the same?  For example,
555-123-9876  will not match
(555) 123-9876
Click to expand...
<[email protected]> wrote in message
Click to expand...
Private Function Form_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With
End Sub

Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called "Phone".
The CustomerT has a field called "CustomerID" and that is the primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists. We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some Before
Update code that will use Dlookup or Dcount to see if the phone number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. Thisis
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words, if
you start typing into the phone field and then erase what you just put
in, the code I am about to paste here will pop an error and debug.
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"
If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

It is a text field and all phone numbers are formatted the same. You
can just type the number into the field in the form and it will put
the dashes into it automatically after you tab out. You can go into
the table and see that all of the phone numbers are simply 7 digit
numbers with dashes like 813-555-1212 due to the Input Mask in the
table whic is 000\-000\-0000- Hide quoted text -

- Show quoted text -
Click to expand...

I meant 10 digit numbers not 7.
 
K

Klatuu

Okay, but I suggest you put a breakpoint in your code and see what the value
is just before it does the FindFirst.

If the phone number is in the table and it is not finding it, that means
there is a formatting issue.

Put the breakpoint on this line:
.FindFirst "[Phone] = """ & Me.Phone & """"

Is the Phone field in the table a numeric field or a text field?
If it is text, are both fields formatte the same? For example,
555-123-9876 will not match
(555) 123-9876
Click to expand...
<[email protected]> wrote in message
Click to expand...
Private Function Form_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With
End Sub

Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called
"Phone".
The CustomerT has a field called "CustomerID" and that is the
primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists.
We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some
Before
Update code that will use Dlookup or Dcount to see if the phone
number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a
message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This
is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words,
if
you start typing into the phone field and then erase what you just
put
in, the code I am about to paste here will pop an error and debug.
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"
If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

It is a text field and all phone numbers are formatted the same. You
can just type the number into the field in the form and it will put
the dashes into it automatically after you tab out. You can go into
the table and see that all of the phone numbers are simply 7 digit
numbers with dashes like 813-555-1212 due to the Input Mask in the
table whic is 000\-000\-0000- Hide quoted text -

- Show quoted text -
Click to expand...

I meant 10 digit numbers not 7.
 
J

joecosmides

Okay, but I suggest you put a breakpoint in your code and see what the value
is just before it does the FindFirst.

If the phone number is in the table and it is not finding it, that means
there is a formatting issue.

Put the breakpoint on this line:
        .FindFirst "[Phone] = """ & Me.Phone & """"


Is the Phone field in the table a numeric field or a text field?
If it is text, are both fields formatte the same? For example,
555-123-9876 will not match
(555) 123-9876
Private Function Form_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
.FindFirst "[Phone] = """ & Me.Phone & """"
If Not .NoMatch Then 'The Phone number already exists
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " & Me.Phone & " Already exists." _
& vbNewLine & vbNewLine & "You will now been taken to the
record.", vbInformation, "Duplicate Information"
'Go to record of original phone number
Me.Bookmark = .Bookmark
End If
End With
End Sub

Hello all and thank you for any replies. I have a Table named
"CustomerT" and a customer form that has a field in it called
"Phone".
The CustomerT has a field called "CustomerID" and that is the
primary
key and an autonumber. Our sales people love to enter in a new
customer without searching to see if that customer already exists..
We
have the "Phone" field in the CustomerT table set to not allow
duplicate data but it will allow Null data. I need to have some
Before
Update code that will use Dlookup or Dcount to see if the phone
number
already exists in another record and if so it will not save any data
that they have already enterd into the form and will pop up a
message
telling them that another records exists with that phone number and
then ask them if they would like to open that existing record. This
is
the code I have so far but I can't figure out how to get it to open
the exisiting (original record) and I also need to make sure it does
not error if the phone number field is left blank. In other words,
if
you start typing into the phone field and then erase what you just
put
in, the code I am about to paste here will pop an error and debug..
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Phone.Value
stLinkCriteria = "[Phone]=" & "'" & SID & "'"
If DCount("Phone", "CustomerT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Phone Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.",
_
vbInformation, "Duplicate Information"
'Go to record of original phone number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing- Hide quoted text -
- Show quoted text -
It's not working for some reason. It accepts the phone number and does
not issue a warning or text box message.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
It is a text field and all phone numbers are formatted the same. You
can just type the number into the field in the form and it will put
the dashes into it automatically after you tab out. You can go into
the table and see that all of the phone numbers are simply 7 digit
numbers with dashes like 813-555-1212 due to the Input Mask in the
table whic is 000\-000\-0000- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I meant 10 digit numbers not 7.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, I got it working now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if duplicate goto record 3
Edit code to use integer instead of text 1
Searching Across Two Tables For A Record 3
Duplicate Values 3
check for two fields instead of one 4
If duplicate, open a different form 1
Preventing Duplicates from being entered 3
Problem with code to catch duplicate Primay Key 22

Top