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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|