Re: Best way to find ID of new record

John wrote on Tue, 21 Mar 2006 10:43:52 -0800:

I often need to add a new record to a table, find out what that new
record?s auto-increment ID is, and add records to another table that refer
the new record in the first table using the before mentioned
auto-incremented ID. For example, lets call the first table Transaction
and I want to add an Invoice. I open a recordset, rsTrans, (with no
records to minimize overhead) from the Transaction table, rsTrans.addnew,
put values in the fields/columns (including only the invoice total amount
$100, not individual amounts from items), and rsTrans.update. In my
experience this is where it gets squirrely. Sometimes you can accurately
refer to rsTrans(?ID?) and sometimes it is not available or not accurate.
Then I immediately want to open another no record recordset,
rsInvoiceItems, from the InvoiceItems table and add 3 records; item1 for
$60, item2 for $35, and item3 for $5 where each rsInvoiceItems(?TransID?)
= rsTrans(?ID?)

I know from trial and error that the following code works but I am not
confident that it will always work the way it should.

Dim conWW As ADODB.Connection
Dim rsTrans As ADODB.Recordset
Dim rsInvoiceItem As ADODB.Recordset
Dim i As Integer

Set conWW = New ADODB.Connection
conWW.Mode = adModeReadWrite
conWW.Open strConnection

Set rsTrans = New ADODB.Recordset
rsTrans.Open "SELECT * FROM Transactions WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic

rsTrans.AddNew
rsTrans("Amount") = 100
rsTrans("TypeTrans") = "Invoice"
rsTrans("UserID") = User.ID
rsTrans.Update

Set rsInvoiceItem = New ADODB.Recordset
rsInvoiceItem.Open "SELECT * FROM InvoiceItems WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 60
rsInvoiceItem("Description") = "item1"
rsInvoiceItem.Update

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 35
rsInvoiceItem("Description") = "item2"
rsInvoiceItem.Update

rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 5
rsInvoiceItem("Description") = "item3"
rsInvoiceItem.Update

rsInvoiceItem.Close: Set rsInvoiceItem = Nothing
rsTrans.Close: Set rsTrans = Nothing
conWW.Close: Set conWW = Nothing

Thanks, John Brown


That's pretty much how I tend to do the same thing, except that I read the
ID into a variable after the .Update and then close the recordset, and then
reuse the recordset object for the other table and use the variable to pass
the ID. Saves having 2 recordsets open, and keeps the amount of time the
first recordset is open for to a minimum. I also tend to explicitly declare
the recordset location as adUseServer just in case something changes it
elsewhere - you might have problems with adUseClient. Also look into using
adOpenKeyset for the cursor type - I don't think this will affect you with
using Jet, but it might if you move to SQL Server later.

You might also want to look at use a Transaction on the connection to
enclose the whole update so that if anything fails you don't end up with a
parent trans record with no invoiceitem records.

Dan


.

Relevant Pages

  • Re: Best way to find ID of new record
    ... lets call the first table Transaction ... Then I immediately want to open another no record recordset, ... Dim conWW As ADODB.Connection ... adOpenKeyset for the cursor type - I don't think this will affect you with ...
    (microsoft.public.data.ado)
  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • RE: Best way to find ID of new record
    ... Dim conWW As ADODB.Connection ... Dim rsTrans As ADODB.Recordset ... Set rsTrans = New ADODB.Recordset ... Set rsInvoiceItem = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: Calculating Percentiles
    ... It seems that what you want then is not the percentile, ... If you want to call it repeatedly on the same recordset, ... Dim rsPercentile as DAO.Recordset ... ' Ascertain the quantile rank for a given score from the numeric ...
    (microsoft.public.access.modulesdaovba)
  • Re: A simple problem with MoveFirst
    ... update the table (rather than using the recordset) means that from ADO's ... Dim tbDataToBeEmailed As ADODB.Recordset ... tbDataToBeEmailed.Open "tbDataToBeEmailed", cnCurrent, adOpenKeyset, ... "You have not entered time into the ACIS ...
    (microsoft.public.access.modulesdaovba)
Loading