Please help with duplicate recods (form, subform) code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gaba - 03 Aug 2005 21:03 GMT

Hi,
Please help me... I need to duplicate a record (parent form
based on table Jobs) link to the subform (based on table Samples) by "JobID".
Relation is 1=M.
Many times we need to duplicate the job with the samples and modify some
information (date, labid, etc).

Do you have or can provide an example on how to accomplish this?
When we need to duplicate only the job, a copy record was fine, but
duplicating the record AND the many samples linked to it by the JobID is
getting more complicated.

Thanks in advance. Looking forward to lear something new today!

Signature

gaba :)

Reply to this Message

Ofer - 03 Aug 2005 23:26 GMT

How do you get the new JobID after copying the main records?

After the copy if you have the new Id number and the old id number you can
run an insert query, to append all the records with the old id number to the
new one.

> Hi,
> Please help me... I need to duplicate a record (parent form
[quoted text clipped - 9 lines]
>
> Thanks in advance. Looking forward to lear something new today!

Reply to this Message

gaba - 03 Aug 2005 23:37 GMT

First, Thanks Ofer for asking.
Second, Please don't laugh...
I was trying to make this piece of code from the help file work:

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![JobID]

' Add new record to end of Recordset object.
With Rst
.AddNew
!CompanyName = Me!CompanyName
!LabId = ""
!CompanyID = Me!CompanyID
!Date_Received = Me!Date_Received
!Contact = Me!Contact

.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryAddJobAndSamples"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![frmDEJob_Water_Sample].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

I forgot to make clear that I'm using an Access Project for the front end
and MSDE server as a back end. SQL statements are new to me, so I'm trying
really hard to catch up with the program. Another thing: this is the first
time I'm using an append query... as you can see I really need help and
guidence...

Any ideas?

Signature

gaba :)

> How do you get the new JobID after copying the main records?
>
[quoted text clipped - 15 lines]
> >
> > Thanks in advance. Looking forward to lear something new today!

Reply to this Message

gaba - 04 Aug 2005 04:31 GMT

Small steps...

I have the first part working, copies the Main form record. Now the next
challenge... how can I copy the subform information?
The append query is giving me an error:
Procedure 'QryAddSamples' expects parameter 'JobId, which was not supplied
How can I use the "tag" to append the records and the replace the JobId with
the new link (New JobId). Maybe the answer is on the append query.

Hope somebody up there can help ... it is going to be a long night...

Thanks in advance.

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database 'Do I still need this one?

' Return Database variable pointing to current database.
Set dbs = CurrentDb 'Do I still need this one?
On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![JobID]

' Add new record to end of Recordset object.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
DoCmd.RunCommand acCmdRefresh
DoCmd.RunCommand acCmdRecordsGoToLast

'Me.Bookmark = Rst.Bookmark

' Run the Duplicate QryAddSamples append query which selects all
' detail records that have the JobID stored in the form's
' Tag property and appends them back to the Sample table with
' the JobID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.RunSQL "QryAddSamples"
DoCmd.SetWarnings True

'Use Tag to copy old records, need a bookmark or something to update/replace
the
'old JobID with the new JobID or append query takes care of this?

'Requery the subform to display the newly appended records.
Me![frmDEJob_Water_Sample].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

Signature

gaba :)

Reply to this Message