Last bit of code needed.

[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.
Using Access 2003.

Thanks, Neal

 

[Allen Browne]

This should do it:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL Asd String
Dim varID as Variant

strSQL = "INSERT ...
Set db = DBEngine(0)(0)
db.Execute strSQL

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
varID = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing

 

[Neal]

Thanks for that Allan (-:

That allows me to get the ID of the new Job created in the Job table.

All I need now is to use that value captured (LastID) as the JobID for all
the records that get copied from the subform into (new records) in the Items
table.

In English, copy the data in the main form (Jobs), get the ID, copy the data
in the subform (Items) and copy into the Items table - substituting in the
LastID for the JobID.

It's that last bit that is now tripping me up. The line under the comment,
' now do the items and use the ID captured - is wrong and I don't know how
to modify. Thanks!

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim VarID As Variant

Set db = CurrentDb

' clone the Job first
strSQL = "INSERT INTO [Jobs] SELECT Jobs.CustomerID, Jobs.ContractorID,
Jobs.LocationName, Jobs.LocationAddress, Jobs.LocationType,
Jobs.LocationDetail, Jobs.LocationCert, Jobs.City, Jobs.Country,
Jobs.EstimatedQuantity, Jobs.Quantity, Jobs.EstimatedSquareMeters,
Jobs.SquareMeters, Jobs.Price FROM [Jobs]" & " WHERE [JobID] = " & Me!JobID
& ";"
' Create an unnamed/unstored Query to run
Set qd = db.CreateQueryDef("", strSQL)
' and run it
qd.Execute dbFailOnError

'Get the last ID
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("select @@Identity as LastID;")
VarID = rs!LastID
rs.Close
'MsgBox (VarID), vbOKOnly

' now do the items and use the ID captured
strSQL = "INSERT INTO [Items] (JobID, Name, Description)" & " SELECT " &
Me!JobID & " AS JobID, Name, Description FROM [Items]" & " WHERE JobID = " &
Me!JobID & ";"
db.Execute strSQL
Set rs = Nothing
Set db = Nothing
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
' <handle error appropriately, say with msgboxes>
Resume Proc_Exit
End Sub

This should do it:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL Asd String
Dim varID as Variant

strSQL = "INSERT ...
Set db = DBEngine(0)(0)
db.Execute strSQL

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
varID = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.
Using Access 2003.

Thanks, Neal

 

[Allen Browne]

Concatenate the value of the variable into the string:

strSQL = "INSERT INTO [Items] (JobID, Name, Description) " & _
"SELECT " & varID & " AS JobID, Name, Description " & _
"FROM [Items] WHERE JobID = " & Me!JobID & ";"


Hmm. Field named "Name" is a bit of a worry. Should be okay in this context,
but most object in Access have a Name property so you will have problems
with things like Me.Name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allan (-:

That allows me to get the ID of the new Job created in the Job table.

All I need now is to use that value captured (LastID) as the JobID for all
the records that get copied from the subform into (new records) in the
Items table.

In English, copy the data in the main form (Jobs), get the ID, copy the
data in the subform (Items) and copy into the Items table - substituting
in the LastID for the JobID.

It's that last bit that is now tripping me up. The line under the
comment, ' now do the items and use the ID captured - is wrong and I don't
know how to modify. Thanks!

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim VarID As Variant

Set db = CurrentDb

' clone the Job first
strSQL = "INSERT INTO [Jobs] SELECT Jobs.CustomerID, Jobs.ContractorID,
Jobs.LocationName, Jobs.LocationAddress, Jobs.LocationType,
Jobs.LocationDetail, Jobs.LocationCert, Jobs.City, Jobs.Country,
Jobs.EstimatedQuantity, Jobs.Quantity, Jobs.EstimatedSquareMeters,
Jobs.SquareMeters, Jobs.Price FROM [Jobs]" & " WHERE [JobID] = " &
Me!JobID & ";"
' Create an unnamed/unstored Query to run
Set qd = db.CreateQueryDef("", strSQL)
' and run it
qd.Execute dbFailOnError

'Get the last ID
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("select @@Identity as LastID;")
VarID = rs!LastID
rs.Close
'MsgBox (VarID), vbOKOnly

' now do the items and use the ID captured
strSQL = "INSERT INTO [Items] (JobID, Name, Description)" & " SELECT "
& Me!JobID & " AS JobID, Name, Description FROM [Items]" & " WHERE JobID =
" & Me!JobID & ";"
db.Execute strSQL
Set rs = Nothing
Set db = Nothing
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
' <handle error appropriately, say with msgboxes>
Resume Proc_Exit
End Sub

This should do it:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL Asd String
Dim varID as Variant

strSQL = "INSERT ...
Set db = DBEngine(0)(0)
db.Execute strSQL

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
varID = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing

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. Using Access 2003.

Thanks, Neal

 

[Neal]

Thanks a million! It now works. Thanks Allan, thanks John!

Oh yeah, and I've renamed my fields so there are none called Name any more.

Neal

Concatenate the value of the variable into the string:

strSQL = "INSERT INTO [Items] (JobID, Name, Description) " & _
"SELECT " & varID & " AS JobID, Name, Description " & _
"FROM [Items] WHERE JobID = " & Me!JobID & ";"


Hmm. Field named "Name" is a bit of a worry. Should be okay in this
context, but most object in Access have a Name property so you will have
problems with things like Me.Name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allan (-:

That allows me to get the ID of the new Job created in the Job table.

All I need now is to use that value captured (LastID) as the JobID for
all the records that get copied from the subform into (new records) in
the Items table.

In English, copy the data in the main form (Jobs), get the ID, copy the
data in the subform (Items) and copy into the Items table - substituting
in the LastID for the JobID.

It's that last bit that is now tripping me up. The line under the
comment, ' now do the items and use the ID captured - is wrong and I
don't know how to modify. Thanks!

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim VarID As Variant

Set db = CurrentDb

' clone the Job first
strSQL = "INSERT INTO [Jobs] SELECT Jobs.CustomerID,
Jobs.ContractorID, Jobs.LocationName, Jobs.LocationAddress,
Jobs.LocationType, Jobs.LocationDetail, Jobs.LocationCert, Jobs.City,
Jobs.Country, Jobs.EstimatedQuantity, Jobs.Quantity,
Jobs.EstimatedSquareMeters, Jobs.SquareMeters, Jobs.Price FROM [Jobs]" &
" WHERE [JobID] = " & Me!JobID & ";"
' Create an unnamed/unstored Query to run
Set qd = db.CreateQueryDef("", strSQL)
' and run it
qd.Execute dbFailOnError

'Get the last ID
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("select @@Identity as LastID;")
VarID = rs!LastID
rs.Close
'MsgBox (VarID), vbOKOnly

' now do the items and use the ID captured
strSQL = "INSERT INTO [Items] (JobID, Name, Description)" & " SELECT "
& Me!JobID & " AS JobID, Name, Description FROM [Items]" & " WHERE JobID
= " & Me!JobID & ";"
db.Execute strSQL
Set rs = Nothing
Set db = Nothing
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
' <handle error appropriately, say with msgboxes>
Resume Proc_Exit
End Sub

This should do it:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL Asd String
Dim varID as Variant

strSQL = "INSERT ...
Set db = DBEngine(0)(0)
db.Execute strSQL

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
varID = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing

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. Using Access 2003.

Thanks, Neal