programmatically add record

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I have a number of instances where I need to generate a
record in a second table after inserting into the main
table.
In the example here, I run this code after adding a new
record on form1. The RecordID is the PK in the current
form, and FK in Table2Log. RecordID is autoincrement in
table1. The backend is SQL server 2k. I cannot get it to
return the RecordID in form1 so it can be utlizied to
generate a record in table2Log.
How have others solved this issue?!

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("Table2Log",
dbOpenDynaset, dbSeeChanges)

With Rst
.AddNew
!RecordID = Me.RecordID
.Update
.Close
End With

Thanks so much and happy new year!!
Josh
 
This is a common problem when using the server equivalent of AutoNumber as a
Primary Key in a server table. On a number of projects with different server
databases, we solved it by creating a stored procedure on the server to
return the NextID for a specified table, kept in a table of tables. It
incremented the NextID field for the specified table, saved it, and then
returned that value to the user. Thus we never used an AutoNumber /
Increment field in the server database.

There are some other approaches, but some of them fail in actual use with
multiple concurrent users of the client.

Larry Linson
Microsoft Access MVP
 
That should work fine if you save the record before
referencing the control.

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("Table2Log",
dbOpenDynaset, dbSeeChanges)

Docmd.RunCommand accmdSaveRecord

With Rst
.AddNew
!RecordID = Me.RecordID
.Update
.Close
End With
 
Back
Top