A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with Catching Duplicate Before Update



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2009, 05:13 PM posted to microsoft.public.access.forms
Trini Gal
external usenet poster
 
Posts: 20
Default Help with Catching Duplicate Before Update

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.
  #2  
Old February 25th, 2009, 02:54 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Help with Catching Duplicate Before Update

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.


  #3  
Old February 25th, 2009, 02:18 PM posted to microsoft.public.access.forms
Trini Gal
external usenet poster
 
Posts: 20
Default 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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.