troubleshooting Question

Record clone function throws error

Avatar of TomBock2004
TomBock2004 asked on
Microsoft Access
12 Comments1 Solution402 ViewsLast Modified:
I have 2 tables that are joined via 1:M relationship.

Now, instead of using the child data via subform in the parent's mainform, I use a cmdButton to open up the "standalone" subform as a popup form.   All of this works great.

Now, I needed to added a CloneRecord feature that will copy the parent record plus the children records in the popup form.

I use the function below but it does throw me an error indicating that:   "Access can't field the field "|" referred to in the expression."

Again, the parent record get's cloned okay... but not the child record.

Private Sub CloneRecord_Click()

    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String
    Dim lngID As Long       'Primary key value of the new record.

    'Save and edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then

        MsgBox "Select the record to duplicate."

        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
                !EmpName = Me.EmpName
                'etc for other fields.

            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !EmpID

            'Duplicate the related records: append query.
            If Me.[sfrmJobAssignments].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblJobs] ( EmpIDfk, JobTitle ) " & _
                    "SELECT " & lngID & " As NewID, JobTitle " & _
                     "FROM [tblJobs] WHERE EmpIDfk = " & Me.EmpID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError

            ... more stuff such as confirmation messages etc.

End Sub

Once I click on Debug, the line "If Me.[sfrmJobAssignments].Form.RecordsetClone.RecordCount > 0 Then" is highlighted.

A few more points for the mainform (parent records) to popup form (child records):

Private Sub cmdPopUpForm_Click()
    strAutoIDReference = Me.EmpID.Value
    DoCmd.OpenForm "sfrmJobAssignments"    
End Sub

Private Sub EmpName_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord    
End Sub

Private Sub Form_Load()
    Me.EmpIDfk = strAutoIDReference    
End Sub

Private Sub Form_Current()
    Me.EmpIDfk = strAutoIDReference    
End Sub

Any ideas as to why I'm getting the error "Access can't field the field "|" referred to in the expression."?


P.S.   I provided a small sample file at:

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros