Adding an autonumber which has just been created to a different table

[Will]

Hi, I have a form which calculates a final cost (frmWorkCosts, data stored
in tblWorkCosts) and once calculated adds this final cost (Which isn't saved
in tblWorkCosts) into a different table (tblLabour) as a new record. Once
it has done this I want it to save the Labour ID (Key Field/Autonumber in
tblLabour) to the Labour ID field in the original calculation table
tblWorkCosts, creating a link between the two tables.
I have tried a variety of methods such as DLast and Last modified but
without success.
At the moment my code looks like this:

Function AddCalculatedLa bourCost(Labour Fun As Variant, Costratehourskg
As Variant)
Dim db As Database
Dim rs As Recordset
Dim WSPrefix, LabourDesc As String
Dim Resp As Variant

Set db = DBEngine.Worksp aces(0).Databas es(0)
Set rs = db.OpenRecordse t("tblLabour" )

rs.AddNew
rs![WSPrefix] = WSPrefix
rs![Labour] = Costratehourskg
rs![RecipeLabourGro up] = LabourFun
rs![Description] = LabourDesc
rs.Update
rs.Close

'Set db = DBEngine.Worksp aces(0).Databas es(0) 'this is my effort to try
and add the autonumber of the adding process above
'Set rs = db.OpenRecordse t("tblWorkCosts ")
'rs.Edit
'rs![Labour ID] = LabourID
'rs.Update

Resp = MsgBox("The labour Cost is now saved", vbOKOnly, "New Labour
Cost")

DoCmd.Close

End Function

Any help would be greatly appreciated

Many thanks

Will

[Allen Browne]

If this data is in Access tables, the new autonumber is assigned as soon as
you AddNew, so you can read it then:

Function AddCalculatedLa bourCost(Labour Fun As Variant, _
Costratehourskg As Variant) As Long
Dim db As Database
Dim rs As Recordset
Dim WSPrefix, LabourDesc As String
Dim Resp As Variant
Dim lngNewID As Long

Set db = DBEngine.Worksp aces(0).Databas es(0)
Set rs = db.OpenRecordse t("tblLabour" )

rs.AddNew
'Store the new ID value into our variable.
lngNewID = rs![NameOfYourPrima ryKeyFieldHere]

rs![WSPrefix] = WSPrefix
rs![Labour] = Costratehourskg
rs![RecipeLabourGro up] = LabourFun
rs![Description] = LabourDesc
rs.Update
rs.Close

Resp = MsgBox("The labour Cost is now saved as " & lngNewID, _
vbOKOnly, "New Labour Cost")

'Return the new ID value
AddCalculatedLa bourCost = lngNewID

'Clean up.
Set rs = Nothing
Set db = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Will" <wi************ *****@f2s.com> wrote in message
news:cj******** **@news.freedom 2surf.net...

Hi, I have a form which calculates a final cost (frmWorkCosts, data stored
in tblWorkCosts) and once calculated adds this final cost (Which isn't
saved
in tblWorkCosts) into a different table (tblLabour) as a new record. Once
it has done this I want it to save the Labour ID (Key Field/Autonumber in
tblLabour) to the Labour ID field in the original calculation table
tblWorkCosts, creating a link between the two tables.
I have tried a variety of methods such as DLast and Last modified but
without success.
At the moment my code looks like this:

Function AddCalculatedLa bourCost(Labour Fun As Variant, Costratehourskg
As Variant)
Dim db As Database
Dim rs As Recordset
Dim WSPrefix, LabourDesc As String
Dim Resp As Variant

Set db = DBEngine.Worksp aces(0).Databas es(0)
Set rs = db.OpenRecordse t("tblLabour" )

rs.AddNew
rs![WSPrefix] = WSPrefix
rs![Labour] = Costratehourskg
rs![RecipeLabourGro up] = LabourFun
rs![Description] = LabourDesc
rs.Update
rs.Close

'Set db = DBEngine.Worksp aces(0).Databas es(0) 'this is my effort to try
and add the autonumber of the adding process above
'Set rs = db.OpenRecordse t("tblWorkCosts ")
'rs.Edit
'rs![Labour ID] = LabourID
'rs.Update

Resp = MsgBox("The labour Cost is now saved", vbOKOnly, "New Labour
Cost")

DoCmd.Close

End Function