T
Trini Gal
I have the following code:
Private Sub LOCATION_BeforeUpdate(Cancel As Integer)
Dim varADDRESS As Variant
If Me.NewRecord Then
varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION _
& "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY & "'")
If Not IsNull(varADDRESS) Then
If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
bolCheckDuplicate = True
End If
End If
End If
Exit_LOCATION_BeforeUpdate:
Exit Sub
Err_LOCATION_BeforeUpdate:
MsgBox Err.Description
Resume Exit_LOCATION_BeforeUpdate
End Sub
For some reason, its catching the duplicates of when the # part of an
address is entered like "123" too, instead of the whole address "123 Main".
It will catch an address like "123 South" just because the "123" is a
duplicate. I want it to catch the whole address "123 Main".
Can someone tell me what I'm doing wrong please, I can't seem to figure it
out?
Thanks in advance.
Private Sub LOCATION_BeforeUpdate(Cancel As Integer)
Dim varADDRESS As Variant
If Me.NewRecord Then
varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" &
Me.ADDRESS _
& "' and [STREET] = '" & Me.LOCATION _
& "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY & "'")
If Not IsNull(varADDRESS) Then
If MsgBox("This record already exists." & _
"Do you want to cancel these changes and go to that
record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Address Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
bolCheckDuplicate = True
End If
End If
End If
Exit_LOCATION_BeforeUpdate:
Exit Sub
Err_LOCATION_BeforeUpdate:
MsgBox Err.Description
Resume Exit_LOCATION_BeforeUpdate
End Sub
For some reason, its catching the duplicates of when the # part of an
address is entered like "123" too, instead of the whole address "123 Main".
It will catch an address like "123 South" just because the "123" is a
duplicate. I want it to catch the whole address "123 Main".
Can someone tell me what I'm doing wrong please, I can't seem to figure it
out?
Thanks in advance.