February 25th, 2009, 01:18 PM
posted to microsoft.public.access.forms
|
|
|
Help with Catching Duplicate Before Update
Allen,
Thanks for your response. I'll give it a try and let you know.
"Allen Browne" wrote:
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Trini Gal" wrote in message
...
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.
|