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
        '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
    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?

John SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
John SheehySystem Security ManagerAuthor 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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySystem Security ManagerAuthor 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.

You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.