Record clone function throws error

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."

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

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

            ...
            ... 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):

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

Private Sub EmpName_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord    
End Sub


SUBFORM
======
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."?

Tom

P.S.   I provided a small sample file at:  http://www.savefile.com/files.php?fid=7176282

TomBock2004Asked:
Who is Participating?
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.

Rick_RickardsCommented:
Modify the line...
       If Me.[sfrmJobAssignments].Form.RecordsetClone.RecordCount > 0 Then
to read this instead...
       If [Forms]![sfrmJobAssignments].[RecordsetClone].RecordCount > 0 Then

It's breaking because the sub form is no longer a subordinate to the main form.  Given the design approach you are taking you'll probably want to add some code that checks for and insures that the sub form (now used as a popup form), is in fact open before you attempt to clone the record.  In any case, changing the one line of code detailed above should resolve the error and get you going again.

Rick

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
TomBock2004Author Commented:
Rick,

thanks for the feedback... ah I see... that makes sense.

Okay, I replaced the line of code.   Now, I get a different error: "Access can't find the form 'sfrmJobAssignments' referred to in macro expression or VB code."

Am I still missing some else?

Tom
Rick_RickardsCommented:
That error would appear if the sub form was not opened before clicking on the Clone button.  Any chance that is what is happening?

Rick
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

TomBock2004Author Commented:
Rick,

ok, I'm one step further... your code does work great.   The error still pops up since my "CloneRecord" cmdButton resides on the mainform.  

Here's how it works:
1. open the main form
2. open the popup form
3. move popup form "aside" to view the mainform
4. click "Clone" on main form
5. parent and child record then have been cloned properly

Here's the problem though... the user will always have the popup form open.   How can I tweak it so that the cloning works w/o the need to have the popup form open?

Tom
TomBock2004Author Commented:
funny... the moment you sent yours, I sent mine...

yes, we're in agreement... pls read previous post.

Tom
TomBock2004Author Commented:
Okay,

I simply open/closed the popup form before/after cloning it.

Thanks for your help on the VBA error!

Tom
Rick_RickardsCommented:
You're most welcome.  Sorry I didn't get back online till just now but would seem you worked around the 2nd issue on your own. :)  

Rick
TomBock2004Author Commented:
Rick,

yes, thanks... I simply automatically open the form and then close it after the cloning.   It "blinks" for a brief moment but I guess that's okay.

If you have a smoother solution (w/o using the DoCmd.OpenForm "xxx" and DoCmd.Close acForm "xxx"), I'd certainly appreciate your feedback.

Tom
Rick_RickardsCommented:
If you build a recordset instead of opening the form you can avoid opening the form entirely and instead check to see whether the recordset contains records instead of checking the form for the same.  Implementation requires the following steps...

1) Add Microsoft DAO 3.60 to references.  (How: Open any code module, select the Tools Pull down Menu, then References, then scroll through the list of choices until you find "Microsoft DAO 3.60" - check that reference).

2) Add the following variable declarations at the beginning of your procedure...

    Dim rst As DAO.Recordset
    Dim db As DAO.Database

3) Replace...
                     If [Forms]![sfrmJobAssignments].[RecordsetClone].RecordCount > 0 Then
with the lines...

                     strSql = "SELECT " & lngID & " As NewID, JobTitle " & _
                                 "FROM [tblJobs] WHERE EmpIDfk = " & Me.EmpID & ";"
                     Set db = CurrentDb
                     Set rst = db.OpenRecordset(strSql, DAO.dbOpenSnapshot)
                     If rst.RecordCount Then

4) Remove the lines that opened and closed the popup form as they will no longer be needed.

After that you should be set.

Rick
TomBock2004Author Commented:
Rick,

thanks... I still seem to be missing something..

here's the (almost) entire code


.... 'stuff

            .Bookmark = .LastModified
            lngID = !EmpID


            'Caches AutonumberID value from parent record
            strAutoIDReference = Me.EmpID.Value
           
                strSql = "SELECT " & lngID & " As NewID, JobTitle " & _
                            "FROM [tblJobs] WHERE EmpIDfk = " & Me.EmpID & ";"
                Set db = CurrentDb
                Set rst = db.OpenRecordset(strSql, DAO.dbOpenSnapshot)
                If rst.RecordCount Then

                DBEngine(0)(0).Execute strSql, dbFailOnError                
               
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If

.... 'more stuff

I now get the error "I cannot execute a selecte query".    What am I missing?

Tom
Rick_RickardsCommented:
That's because you are missing one of the lines of code.  Notice the code that sets strSQL a 2nd time appearing immediately after If rst.RecordCount then...

            If rst.RecordCount Then
                strSql = "INSERT INTO [tblJobs] ( EmpIDfk, JobTitle ) " & _
                            "SELECT " & lngID & " As NewID, JobTitle " & _
                            "FROM [tblJobs] WHERE EmpIDfk = " & Me.EmpID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
TomBock2004Author Commented:
Rick.... sorry, it's still barking at me... I copied it verbatim but now I get yet another error.   BTW, I don't have to have the  "If rst.RecordCount > 0 Then"... you're using "If rst.RecordCount Then".

So, currently, the error is: "Object variable or With block variable not set".   Urgh... please bear w/ me.

tom



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

        MsgBox "Select the record to duplicate."

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

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


            'Caches AutonumberID value from parent record
            strAutoIDReference = Me.EmpID.Value

            If rst.RecordCount Then
                strSql = "INSERT INTO [tblJobs] ( EmpIDfk, JobTitle ) " & _
                            "SELECT " & lngID & " As NewID, JobTitle " & _
                            "FROM [tblJobs] WHERE EmpIDfk = " & Me.EmpID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError

            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If

            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If
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.