Help with Catching Duplicate Before Update

[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.

 

[Allen Browne]

Suggestions:

1. Use the BeforeUpdate event of the *form*, not of a control. At the time
the user fills in the Location, the other boxes may still be null.

2. Open your [Leaks Found] table in design view, and set the Allow Zero
Length property to No for each of the Text fields. (You may need to execute
an update query to change the zero-length-string values to Null as well.)

3. Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html
Then compact/repair the database (which will also fix an index problem.)

4. To help debug the code, break it down so you can see the string and the
result of the look up. You can then look in the Immediate Window (Ctrl+G)
and examine what's going on. This kind of thing:

Dim strWhere As String
Dim varResult As Variant
If Me.NewRecord Then
strWhere = "(Address = """ & Me.Address & _
""") AND (Street = """ & Me.Street & _
""") AND (Location = """ & Me.Location & _
""") AND (S_Community = """ & Me.S_Community & """)"
Debug.Print strWhere
varResult = DLookup("ID", "Leaks Found", strWhere)
Debug.Print varResult
If Not IsNull(varResult) Then
...

 

[Trini Gal]

Allen,

Thanks for your response. I'll give it a try and let you know.

Suggestions:

1. Use the BeforeUpdate event of the *form*, not of a control. At the time
the user fills in the Location, the other boxes may still be null.

2. Open your [Leaks Found] table in design view, and set the Allow Zero
Length property to No for each of the Text fields. (You may need to execute
an update query to change the zero-length-string values to Null as well.)

3. Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html
Then compact/repair the database (which will also fix an index problem.)

4. To help debug the code, break it down so you can see the string and the
result of the look up. You can then look in the Immediate Window (Ctrl+G)
and examine what's going on. This kind of thing:

Dim strWhere As String
Dim varResult As Variant
If Me.NewRecord Then
strWhere = "(Address = """ & Me.Address & _
""") AND (Street = """ & Me.Street & _
""") AND (Location = """ & Me.Location & _
""") AND (S_Community = """ & Me.S_Community & """)"
Debug.Print strWhere
varResult = DLookup("ID", "Leaks Found", strWhere)
Debug.Print varResult
If Not IsNull(varResult) Then
...

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

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

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.