asked on

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

Scott McDaniel (EE MVE )

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 Sheehy

ASKER

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.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Sheehy

ASKER

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

PatHartman

You're welcome.