Retrieving an autonumber after inserted to a table

  • Thread starter Thread starter Will
  • Start date Start date
W

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 AddCalculatedLabourCost(LabourFun 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.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblLabour")

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

'Set db = DBEngine.Workspaces(0).Databases(0) 'this is my effort to try
and add the autonumber of the adding process above
'Set rs = db.OpenRecordset("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
 
You can refer to rs![Labour ID] after you've created the row. I'm having a
slight mental block, though, remembering whether you do this before or after
the .Update statement, so I'm afraid you'll have to experiment. (It's one
way for DAO and the other for ADO, and I can't remember which is which!)
 
Back
Top