Opening duplicate records via a subform.

On my form (fm_Inventory) there is a sub form (frm_inventory_sub)
On that sub form there is a field labeled DCN.  
On the After Update Event I have the following code:
    Dim stDOCName As String
    Dim stLinkCriteria As String
    Dim Sid As String
           
   If IsNull(Me.DCN) Then Exit Sub
      
    Sid = Me.DCN.Value

    stDOCName = "frm_Inventory"
    stLinkCriteria = "DCN=" & "'" & Sid & "'"
    
   
    If DCount("DCN", "qry_Inventory_Dups", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        'Me.Undo
        'Message box warning of duplication
         MsgBox "Warning DCN: " _
        & Sid & " has already been entered." _
        & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
        , "Duplicate Information"
        'Go to record of original Employee
        
        Cancel = True 'add this line
        DoCmd.Close acForm, "frm_Inventory"
        DoCmd.OpenForm stDOCName, , , stLinkCriteria
    Else:
    End If

Open in new window


Everything works great up to the end.  It finds the duplicates but when ti goes to open frm_inventory it can't.

I know the link criteria needs to change to open up the hardware with the record with the DCN in it.
I have never done this via a sub form.  I know I would need to add a new variable for the Hardware_ID Field

But how do I tell the code to open the correct record?

Thanks
John
John SheehySecurity AnalystAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
I'm confused as to what you are trying to accomplish.  If you do not want to allow duplicates, then you have to identify that the record to be added is a duplicate before allowing it to be added.  Checking for a duplicate in the AfterUpdate event is akin to closing the barn door after the horses have escaped.  If the AfterUpdate event runs, that means that the bad data has already been saved.

To prevent duplicates:
1. Change the properties of the DCN field on the table to not allow duplicates by adding a unique index.  That will prevent Access from ever allowing a duplicate to be saved.
2. To give the users a more meaningful error message and help them fix the problem, you would perform the duplicate check in the BeforeUpdate of the Form.  In this case, if the DCount() returns anything > 0, that means that the DCN already exists in the table. So you would check the NewRecord property.  If this is a new record, you would cancel the update and set focus back to the DCN control with a message that this is a duplcate.  The tricky situation is if the DCN for an existing record is changed.  To prevent the DCN on record 3 from being changed to duplicate the DCN on record 2, you will have to do an additional check and that is to use a DLookup() to find the primary key value of the existing DCN value.  If it is the same as the record currently being saved, that is fine.  But, if the IDs are different, then you know that someone is trying to reuse an existing DCN on a second record.

The code you have that is trying to open the duplicate record can't work because  the DCN is the same value on two records so Access will bring back which ever record it encounters first which may not be the record you actually want.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not quite sure what you're trying to do. What are you hoping to do if the DCN is not found?

Would the DCN record be on your parent form, or the child form?

If the code you're showing executed on the parent form or the child form?
0
 
John SheehySecurity AnalystAuthor Commented:
The DCN is on the child form.  If the DCN is not found to be a duplicate then it does nothing

So if the Dcount = 0 then it goes to Else which just ends the sub

If it is found to be a duplicate I would like the form to close out and open the  parent form with the correct data displayed on both the parent/child form.  Hardware_ID is the linked field between the parent and child form.
0
 
John SheehySecurity AnalystAuthor Commented:
I ended up having to set the DCN to unique under the Index.  I was able to have it check for duplicates prior to adding the record as suggested and I was able to store the HW_ID in a temp filed so it knew which record to open once it found the first occurrence.    However, it kept sending me to the same record I was on.  So I will toy with as time permits.

My main goal was this.
DCNs are not generated by my group.  And we have over 3000 pieces of equipment with a DCN assigned.   I didn't want dups to be added during the annual inventory with 15 people doing the inventory there is bound to be overlap.  If one of the employees were entering a DCN they could see it was a duplicate, be sent to that record to see if that information matches what they inventoried.  If it doesn't then we need to reconcile the two pieces of equipment.

Thanks
John
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.