I need to duplicate a record on a main sub form

[Neal]

I've tried various things but can't do it.

Two tables, Jobs and Items. One job, many items. Keys are JobID, ItemID,
link JobID. Main form name frmJobsMain, linked subform name frmItemsSub.

One job gets issued to a contractor, and there are many items shown on the
main sub form. At a later stage, we need to get the contractor to do
exactly the same job again, so we want a button on the main sub form that
'duplicates' the job.

I've resorted to trying to write some code triggered by a button on
frmJobsMain to do it, but it's beyond me. I started thinking about running
a 'MakeTable' query to select and hold the Items first, then making a new
record in the main table, somehow changing the JobID in the temp table to
the ID of the new job and adding the records back into the Item table.

Am I barking up the wrong tree? How should I do this?

Thanks, Neal

 

[John Vinson]

I've tried various things but can't do it.

Two tables, Jobs and Items. One job, many items. Keys are JobID, ItemID,
link JobID. Main form name frmJobsMain, linked subform name frmItemsSub.

One job gets issued to a contractor, and there are many items shown on the
main sub form. At a later stage, we need to get the contractor to do
exactly the same job again, so we want a button on the main sub form that
'duplicates' the job.

I've resorted to trying to write some code triggered by a button on
frmJobsMain to do it, but it's beyond me. I started thinking about running
a 'MakeTable' query to select and hold the Items first, then making a new
record in the main table, somehow changing the JobID in the temp table to
the ID of the new job and adding the records back into the Item table.

You don't need a maketable - you need two append queries, one for the
single mainform record, and a second one for the Items table. You
should be able to run them with a RunQuery macro action or (better)
with an Execute method from VBA code.

Try something like this:

Private Sub cmdCloneJob_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
' clone the Job first
strSQL = "INSERT INTO [Jobs] SELECT <?> FROM [Jobs]" _
& " WHERE [JobID] = " & Me![JobID] & ";"
' the <?> is all fields EXCEPT the JobID
' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)
' and run it; any errors will jump to Proc_Error
qd.Execute dbFailOnError
' now do the items
strSQL = "INSERT INTO [Items] SELECT <?> FROM [Items]" _
& " WHERE [JobID] = " & Me![JobID] & ";"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<handle error appropriately, say with msgboxes>
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Neal]

I can't thank you enough for taking the time to write that for me.

I understand what you are doing, but I haven't been able to tweak it to work
just yet, but I will!

At the moment I get an error message when I run it. Run time error 3075
syntax error (missing operator) in query expression '<?>'

I click debug and it goes to this line...

Set qd = db.CreateQueryDef("", strSQL)

Thanks again, Neal

I've tried various things but can't do it.

Two tables, Jobs and Items. One job, many items. Keys are JobID, ItemID,
link JobID. Main form name frmJobsMain, linked subform name frmItemsSub.

One job gets issued to a contractor, and there are many items shown on the
main sub form. At a later stage, we need to get the contractor to do
exactly the same job again, so we want a button on the main sub form that
'duplicates' the job.

I've resorted to trying to write some code triggered by a button on
frmJobsMain to do it, but it's beyond me. I started thinking about
running
a 'MakeTable' query to select and hold the Items first, then making a new
record in the main table, somehow changing the JobID in the temp table to
the ID of the new job and adding the records back into the Item table.

You don't need a maketable - you need two append queries, one for the
single mainform record, and a second one for the Items table. You
should be able to run them with a RunQuery macro action or (better)
with an Execute method from VBA code.

Try something like this:

Private Sub cmdCloneJob_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
' clone the Job first
strSQL = "INSERT INTO [Jobs] SELECT <?> FROM [Jobs]" _
& " WHERE [JobID] = " & Me![JobID] & ";"
' the <?> is all fields EXCEPT the JobID
' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)
' and run it; any errors will jump to Proc_Error
qd.Execute dbFailOnError
' now do the items
strSQL = "INSERT INTO [Items] SELECT <?> FROM [Items]" _
& " WHERE [JobID] = " & Me![JobID] & ";"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<handle error appropriately, say with msgboxes>
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

I can't thank you enough for taking the time to write that for me.

I understand what you are doing, but I haven't been able to tweak it to work
just yet, but I will!

At the moment I get an error message when I run it. Run time error 3075
syntax error (missing operator) in query expression '<?>'

<chuckle>

Read the comment in the code.

I don't know the names of the fields in your tables. You will have to
replace the <?> with all the fields in your table, separated by
commas, leaving out the Autonumber primary key field. If you leave the
ID fields in the query, you'll get NOTHING appended, because it will
try to insert a new record with the same ID as the current record.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Neal]

I love doing stupid things for all the world to see (-:

OK, I'll wear that one. I've sorted that out, and it works fine for the
first part (clone the Job details), but I think it might be tripping up on
the second part where it copies the items. There are many items, and each
one has to have the JobID added to it.

I can't see where the code adds the JobID to the item records it is
creating.

Again, thanks a million (-:

Neal

 

[John Vinson]

I love doing stupid things for all the world to see (-:

Sorry about that... :-{)

OK, I'll wear that one. I've sorted that out, and it works fine for the
first part (clone the Job details), but I think it might be tripping up on
the second part where it copies the items. There are many items, and each
one has to have the JobID added to it.

I can't see where the code adds the JobID to the item records it is
creating.

It isn't: and that was *my* stupid thing for the day!

Try explicitly naming the fields into which you want to insert data,
including the JobID field, and insert the form reference value:

' now do the items
strSQL = "INSERT INTO [Items] (JobID, this, that, ...)" _
& " SELECT " & Me!JobID & " AS JobID, this, that, ... FROM [Items]" _
& " WHERE [JobID] = " & Me![JobID] & ";"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Neal]

Now we are getting close! Thanks for the update John.

It works - but adds the items to the old JobID. The old job gets the
duplicated Items in the subform, not the new job that was created. All 'we'
have to do is tweak it a little to pick up on the new JobID. I'll have a
go, but don't really know how to go about it.

I guess there will have to be a step in there that goes to the Jobs table
and gets the value of the JobID for the last record, holds it and then uses
it in the 'now do the items bit.

(-:


John Vinson says>

 

[Neal]

John has given me some code to clone a record on a main sub form. The first
bit clones the data on the main form, and that's fine. When it's done that,
the new record gets the new JobID. I need to get that new ID and use that
to insert the data into the table that holds the subform data.

I am looking at @@Identity to somehow get the new ID but don't know how.
Also I'd then need to modify the next bit (below) to use that.

strSQL = "INSERT INTO [Items] (JobID, this, that, ...)" _
& " SELECT " & Me!JobID & " AS JobID, this, that, ... FROM [Items]" _
& " WHERE [JobID] = " & Me![JobID] & ";"

Any ideas?

Table Jobs (One), key (autonumber) JobID, table Items (many), key ItemID.

Thanks, Neal