Trying to eliminate duplicate entries

[Guest]

hello

i have a form that is called from a switchboard in "add" mode

I have three controls on this form LastName, FirstName and ID which are
bound to the FirstName, LastName, and ID feilds in the IDTable table. What I
am trying to do is compare data on the form to existing data in the table to
check for dupilcate entries and prompt the user for confirmation. I have the
following code in place and cannot see the problem with it. Of course the
wrapping of the message wreaks havoc with the " _ " character.

please advise

************************************************************
Private Sub FirstName_AfterUpdate()
If Not IsNull(DLookup("[FirstName]", "IDTable", "FirstName ='" _
& Me.FirstName & "' AND [LastName] = '" & Me.LastName & "'")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Guest]

The code looks okay, but it is in the wrong place. It should probably be in
the form's Before Update event so you can cancel the update. Putting it in
the First Name After Update, you may not have a Last Name yet. You will want
to add:
Cancel = True if the user replies No to the message box.

One other thing to consider. It is easier to use the single quote in this
situation rather than trying to get the right number of double quotes to make
it work correctly, but when you are dealing with names, it is better not to
use the single quotes. Sooner or later you will run across an O' Leary or O'
Conner and your code will fail. (Those Irish are always causing problems

 

[Guest]

I Moved it to the before update event of the form and it did not behave any
different it still allowed dup entries through - I didnt figure that moving
thr event would block dup entries. but that would be a good way to stop
unwanted updates .. however when i took the quotes away from the field name
in the dlookup statement I got a 2001 error - I had cancelled a prev
operation. but it did trigger something at the before update time. making me
believe that my quotes are wrong in more ways than the "O'malley" issue
can you advise ?

thanx

The code looks okay, but it is in the wrong place. It should probably be in
the form's Before Update event so you can cancel the update. Putting it in
the First Name After Update, you may not have a Last Name yet. You will want
to add:
Cancel = True if the user replies No to the message box.

One other thing to consider. It is easier to use the single quote in this
situation rather than trying to get the right number of double quotes to make
it work correctly, but when you are dealing with names, it is better not to
use the single quotes. Sooner or later you will run across an O' Leary or O'
Conner and your code will fail. (Those Irish are always causing problems

hello

i have a form that is called from a switchboard in "add" mode

I have three controls on this form LastName, FirstName and ID which are
bound to the FirstName, LastName, and ID feilds in the IDTable table. What I
am trying to do is compare data on the form to existing data in the table to
check for dupilcate entries and prompt the user for confirmation. I have the
following code in place and cannot see the problem with it. Of course the
wrapping of the message wreaks havoc with the " _ " character.

please advise

************************************************************
Private Sub FirstName_AfterUpdate()
If Not IsNull(DLookup("[FirstName]", "IDTable", "FirstName ='" _
& Me.FirstName & "' AND [LastName] = '" & Me.LastName & "'")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Guest]

This will cure the O'Neil problem:
DLookup("[FirstName]", "IDTable", "[FirstName] ='" _
& Me.FirstName & """ AND [LastName] = """ & Me.LastName & """"))

The 2001 problem often has to do with a misspelled or misinterputed field
name. I don't know if the brackets missing arount FirsName would do it or
not.

Step though your code in debug mode to be sure the variable and control
vaules are what you expect them to be. The code should work. This is not
rocket science, it is a common way to do this.

I Moved it to the before update event of the form and it did not behave any
different it still allowed dup entries through - I didnt figure that moving
thr event would block dup entries. but that would be a good way to stop
unwanted updates .. however when i took the quotes away from the field name
in the dlookup statement I got a 2001 error - I had cancelled a prev
operation. but it did trigger something at the before update time. making me
believe that my quotes are wrong in more ways than the "O'malley" issue
can you advise ?

thanx

The code looks okay, but it is in the wrong place. It should probably be in
the form's Before Update event so you can cancel the update. Putting it in
the First Name After Update, you may not have a Last Name yet. You will want
to add:
Cancel = True if the user replies No to the message box.

One other thing to consider. It is easier to use the single quote in this
situation rather than trying to get the right number of double quotes to make
it work correctly, but when you are dealing with names, it is better not to
use the single quotes. Sooner or later you will run across an O' Leary or O'
Conner and your code will fail. (Those Irish are always causing problems

hello

i have a form that is called from a switchboard in "add" mode

I have three controls on this form LastName, FirstName and ID which are
bound to the FirstName, LastName, and ID feilds in the IDTable table. What I
am trying to do is compare data on the form to existing data in the table to
check for dupilcate entries and prompt the user for confirmation. I have the
following code in place and cannot see the problem with it. Of course the
wrapping of the message wreaks havoc with the " _ " character.

please advise

************************************************************
Private Sub FirstName_AfterUpdate()
If Not IsNull(DLookup("[FirstName]", "IDTable", "FirstName ='" _
& Me.FirstName & "' AND [LastName] = '" & Me.LastName & "'")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Guest]

Got it to work with the following code
If Not IsNull(DLookup("[FirstName]", "IDTable", "[FirstName] = """ _
& Me.FirstName & """ AND [LastName] = """ & Me.LastName & """")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If

-this vba is fickle stuff :d - thanx klatuu

This will cure the O'Neil problem:
DLookup("[FirstName]", "IDTable", "[FirstName] ='" _
& Me.FirstName & """ AND [LastName] = """ & Me.LastName & """"))

The 2001 problem often has to do with a misspelled or misinterputed field
name. I don't know if the brackets missing arount FirsName would do it or
not.

Step though your code in debug mode to be sure the variable and control
vaules are what you expect them to be. The code should work. This is not
rocket science, it is a common way to do this.

I Moved it to the before update event of the form and it did not behave any
different it still allowed dup entries through - I didnt figure that moving
thr event would block dup entries. but that would be a good way to stop
unwanted updates .. however when i took the quotes away from the field name
in the dlookup statement I got a 2001 error - I had cancelled a prev
operation. but it did trigger something at the before update time. making me
believe that my quotes are wrong in more ways than the "O'malley" issue
can you advise ?

thanx

The code looks okay, but it is in the wrong place. It should probably be in
the form's Before Update event so you can cancel the update. Putting it in
the First Name After Update, you may not have a Last Name yet. You will want
to add:
Cancel = True if the user replies No to the message box.

One other thing to consider. It is easier to use the single quote in this
situation rather than trying to get the right number of double quotes to make
it work correctly, but when you are dealing with names, it is better not to
use the single quotes. Sooner or later you will run across an O' Leary or O'
Conner and your code will fail. (Those Irish are always causing problems

:

hello

i have a form that is called from a switchboard in "add" mode

I have three controls on this form LastName, FirstName and ID which are
bound to the FirstName, LastName, and ID feilds in the IDTable table. What I
am trying to do is compare data on the form to existing data in the table to
check for dupilcate entries and prompt the user for confirmation. I have the
following code in place and cannot see the problem with it. Of course the
wrapping of the message wreaks havoc with the " _ " character.

please advise

************************************************************
Private Sub FirstName_AfterUpdate()
If Not IsNull(DLookup("[FirstName]", "IDTable", "FirstName ='" _
& Me.FirstName & "' AND [LastName] = '" & Me.LastName & "'")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Guest]

Great. If you are using the Before Update event of the form, add the line
noted below:
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
Cancel = True <-------------- Add this line
End If

Got it to work with the following code
If Not IsNull(DLookup("[FirstName]", "IDTable", "[FirstName] = """ _
& Me.FirstName & """ AND [LastName] = """ & Me.LastName & """")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If

-this vba is fickle stuff :d - thanx klatuu

This will cure the O'Neil problem:
DLookup("[FirstName]", "IDTable", "[FirstName] ='" _
& Me.FirstName & """ AND [LastName] = """ & Me.LastName & """"))

The 2001 problem often has to do with a misspelled or misinterputed field
name. I don't know if the brackets missing arount FirsName would do it or
not.

Step though your code in debug mode to be sure the variable and control
vaules are what you expect them to be. The code should work. This is not
rocket science, it is a common way to do this.

I Moved it to the before update event of the form and it did not behave any
different it still allowed dup entries through - I didnt figure that moving
thr event would block dup entries. but that would be a good way to stop
unwanted updates .. however when i took the quotes away from the field name
in the dlookup statement I got a 2001 error - I had cancelled a prev
operation. but it did trigger something at the before update time. making me
believe that my quotes are wrong in more ways than the "O'malley" issue
can you advise ?

thanx


:

The code looks okay, but it is in the wrong place. It should probably be in
the form's Before Update event so you can cancel the update. Putting it in
the First Name After Update, you may not have a Last Name yet. You will want
to add:
Cancel = True if the user replies No to the message box.

One other thing to consider. It is easier to use the single quote in this
situation rather than trying to get the right number of double quotes to make
it work correctly, but when you are dealing with names, it is better not to
use the single quotes. Sooner or later you will run across an O' Leary or O'
Conner and your code will fail. (Those Irish are always causing problems

:

hello

i have a form that is called from a switchboard in "add" mode

I have three controls on this form LastName, FirstName and ID which are
bound to the FirstName, LastName, and ID feilds in the IDTable table. What I
am trying to do is compare data on the form to existing data in the table to
check for dupilcate entries and prompt the user for confirmation. I have the
following code in place and cannot see the problem with it. Of course the
wrapping of the message wreaks havoc with the " _ " character.

please advise

************************************************************
Private Sub FirstName_AfterUpdate()
If Not IsNull(DLookup("[FirstName]", "IDTable", "FirstName ='" _
& Me.FirstName & "' AND [LastName] = '" & Me.LastName & "'")) Then
If MsgBox("A Record For" & " " & [FirstName] & " " & [LastName] & " " &
"already exists. Do you want to continue ?", _
vbYesNo, "Record Exists") = vbNo Then
Me.Undo
End If
End If
End Sub