Navigate to record from index violation (1 Viewer)

Steve@trop

Registered User
Joined
May 10, 2013
Messages
142
Users enter customer information into a form I created. The underlying table has an index set up to prevent users from entering duplicate customers. The index is made of two table fields (last name and phone number). I've got error handling set up so that if they enter a duplicate customer it instructs them to do a search to open the form on the existing customer. They don't like the extra step of doing the search. They want me to set it up so that instead of asking them to search for the existing customer, the form simply opens to that customer so they can edit it or add orders to it. I've tried various ways of doing this but haven't had any luck getting them to work correctly.

I've tried having the form close and then re-open to the correct customer but for some reason the form always comes up blank when it opens instead of opening to the record I want.

There is probably a simpler way. Anybody got ideas?

Thanks,

Steve
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
I think Uncle Gizmo posted a way of doing this a while back. If nobody elses give you a solution I suggest asking him through a personal message.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,551
Just for clarity, in plain English,

- how is the user attempting to Add a duplicate ( does system not check for a duplicate when doing an ADD?)
- what error handling do you have? (show the code to help readers understand)
- what exactly do you want to happen?(seems the issue could be resolved if there was a check to see if Customer exists before doing the ADD-- but there may be more details involved)
 

Steve@trop

Registered User
Joined
May 10, 2013
Messages
142
Thanks Jdraw, let me clarify.

The way I prevent duplicates is by using a table index so that when they are on the customer information form if they save a duplicate customer (Last name and phone number exist in the table) the index will prevent the record from saving to the customer table. If there is a better way to handle that, I'm open to trying it. Perhaps I should do a lookup before the form tries to save the record?

Here is the error handling code I'm using:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then

        strMsg = "This customer already exists in this database." & vbCrLf
        strMsg = strMsg & "Perform a search before entering customer information." & vbCrLf & vbCrLf
        strMsg = strMsg & "The record will not be saved."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        'Set focus to the offending control
        Me.PhoneNumber.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
    End If

End Sub
So when the user is entering customer information and the customer they enter already exists, I want the form to pull that existing customer (switch to the existing record) instead of running the error code. If the customer is actually new the form should just allow them to continue entering data.
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
I think the solution that Uncle Gizmo had did have a lookup in the before update. Based on the result of that a message was displayed give the user giving the option of returning to the addition or going to the existing record. I think to get to the record to be edited he did a recordset clone, a find, and then go to record. Something like that. It was pretty simple and worked fine.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,551
I was thinking along this logic:

Adding a Customer:
Does Customer already exist?
If Dcount("*", "TblCustomer", "CustId =" & CustomerID ) > 0
No--then continue with the ADD
Yes --then Msg to user that Customer Exists
Change request to an Update of Customer/Customer Order -whichever applies
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
I checked with Uncle Gizmo and he doesn't remember the post so I wrote what I think it was. Anyway the code below (also in attached) does what I think you want. I put this in the forms before update which occurs before the form error event so you can leave the old code in without any problems. This code assumes you are editing and adding records with the same form. If that is not the case let me know and I'll see if I can't create something for that.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim MsgBoxResult As VbMsgBoxResult
Dim strMsg As String
Dim strCriteria
Dim rst As DAO.Recordset

strMsg = "This customer already exists in this database." & vbCrLf
strMsg = strMsg & "Click Yes to edit the existing record" & vbCrLf
strMsg = strMsg & "Click No to return to adding records."

strCriteria = "[Last Name] = '" & Me.Last_Name & "' AND [Phone Number] = '" & Me.Phone_Number & "'"
If DCount("*", "[Customer]", strCriteria) > 0 Then
    MsgBoxResult = MsgBox(strMsg, vbYesNo, "Duplicate Error")
    Select Case MsgBoxResult
        Case vbYes
            Me.Undo
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            Me.Bookmark = rst.Bookmark
        Case vbNo
            Cancel = True
    End Select
End If


End Sub
 

Attachments

Solo712

Registered User
Joined
Oct 19, 2012
Messages
749
I checked with Uncle Gizmo and he doesn't remember the post so I wrote what I think it was. Anyway the code below (also in attached) does what I think you want. I put this in the forms before update which occurs before the form error event so you can leave the old code in without any problems. This code assumes you are editing and adding records with the same form. If that is not the case let me know and I'll see if I can't create something for that.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim MsgBoxResult As VbMsgBoxResult
Dim strMsg As String
Dim strCriteria
Dim rst As DAO.Recordset

strMsg = "This customer already exists in this database." & vbCrLf
strMsg = strMsg & "Click Yes to edit the existing record" & vbCrLf
strMsg = strMsg & "Click No to return to adding records."

strCriteria = "[Last Name] = '" & Me.Last_Name & "' AND [Phone Number] = '" & Me.Phone_Number & "'"
If DCount("*", "[Customer]", strCriteria) > 0 Then
    MsgBoxResult = MsgBox(strMsg, vbYesNo, "Duplicate Error")
    Select Case MsgBoxResult
        Case vbYes
            Me.Undo
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            Me.Bookmark = rst.Bookmark
        Case vbNo
            Cancel = True
    End Select
End If

End Sub
This is not a good way to handle the exception. It will always throw a message for edits of existing records which is not what we want. There are basically two distinct situations in which the error occurs: when adding a record, and when editing a record. In the first instance, the error occurs if DCount > 0 in the second case if DCount > 1. I would change the code so it throws a message only if index violation actually occurs, IOW if Me.NewRecord = True and DCount > 0 Or Me.NewRecord =False and DCount > 1.

Best,
Jiri
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
in the second case if DCount > 1. I would change the code so it throws a message only if index violation actually occurs, IOW if Me.NewRecord = True and DCount > 0 Or Me.NewRecord =False and DCount > 1.
Thanks for catching this; however, the criteria Me.NewRecord =False and DCount > 1 doesn't work. DCount can never be greater than 1. The unique index the OP has on the fields won't allow it. I haven't figured this out yet but it looks like I need to detect a change in the key fields for the editing case. Something like:

Me.NewRecord =False and DCount > 0 AND ( Me.Phone_Number.OldValue <> Me.Phone_Number Or Me.[Last Name].OldValue <> Me.[Last Name])

I hope I find something simplier
 

Solo712

Registered User
Joined
Oct 19, 2012
Messages
749
Thanks for catching this; however, the criteria Me.NewRecord =False and DCount > 1 doesn't work. DCount can never be greater than 1. The unique index the OP has on the fields won't allow it. I haven't figured this out yet but it looks like I need to detect a change in the key fields for the editing case. Something like:

Me.NewRecord =False and DCount > 0 AND ( Me.Phone_Number.OldValue <> Me.Phone_Number Or Me.[Last Name].OldValue <> Me.[Last Name])

I hope I find something simplier
My bad ! Of course you are right. My thinking did not take into consideration that this is BEFORE UPDATE and therefore the record that would make the counter potentially jump to 2 was not yet written. But the problem still remains: when you are editing an existing record you will not know whether the DCount of 1 means "this" record has had the name or phone number changed in a way which creates a duplicate or whether it simply registers this record without any issue. So probably some test like this would be needed:
Code:
If DCount("*", "[Customer]", strCriteria) > 0 Then
    If Me.NewRecord or Me!ID <> Nz(DLookup("[ID]","[Customer]". strCriteria)) Then 
       'duplicate exist, handle it
Best,
Jiri
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
Here's the revised code which considers the case of edited records which you can also find in the attached database.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim MsgBoxResult As VbMsgBoxResult
Dim strMsg As String
Dim strCriteria
Dim rst As DAO.Recordset
Dim lngDCount As Long
Dim bolKeyChange As Boolean
strMsg = "This customer already exists in this database." & vbCrLf
strMsg = strMsg & "Click Yes to edit the existing record" & vbCrLf
strMsg = strMsg & "Click No to return to adding records."
If Not Me.NewRecord Then
    bolKeyChange = Me.Phone_Number <> Me.Phone_Number.OldValue Or Me.Last_Name <> Me.Last_Name.OldValue
Else
    bolKeyChange = False
End If
strCriteria = "[Last Name] = '" & Me.Last_Name & "' AND [Phone Number] = '" & Me.Phone_Number & "'"
lngDCount = DCount("*", "[Customer]", strCriteria)
If lngDCount > 0 And Me.NewRecord Or lngDCount > 0 And bolKeyChange Then
    MsgBoxResult = MsgBox(strMsg, vbYesNo, "Duplicate Error")
    Select Case MsgBoxResult
        Case vbYes
            Me.Undo
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            Me.Bookmark = rst.Bookmark
        Case vbNo
            Cancel = True
    End Select
End If


End Sub

The code for this is simpler if you do this in the form error event as shown below. The draw back to this approach is that error 3022 can occur for things other than a duplicate index, i.e., can occur for any database integrity violation. So you can only do it this way if you are sure none of these other errors will occur.


Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const ERR_DUPLICATE_INDEX_VALUE = 3022
Dim strMsg As String
Dim strCriteria
Dim rst As DAO.Recordset
strMsg = "This customer already exists in this database." & vbCrLf
 strMsg = strMsg & "Click Yes to edit the existing record" & vbCrLf
 strMsg = strMsg & "Click No to return to adding records."
strCriteria = "[Last Name] = '" & Me.Last_Name & "' AND [Phone Number] = '" & Me.Phone_Number & "'"
If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
   Response = acDataErrContinue 'prevent Access from displaying its own error message
    If MsgBox(strMsg, vbYesNo, "Duplicate Error") = vbYes Then
        Me.Undo
        Set rst = Me.RecordsetClone
        rst.FindFirst strCriteria
        Me.Bookmark = rst.Bookmark
        Exit Sub
    End If
     'Set focus to the offending control
     Me.Phone_Number.SetFocus
Else
     Response = acDataErrDisplay 'cause Access to display its own error message
End If

End Sub
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom