Add new record, get its AutoNumber ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am writing a function to 'import data' from tblAgent to tblMaster. Here is my logic

- read one tblAgent recor
- check if tblAgent.CustomerID exists in tblMaster.CustomerID (CustomerID is autoIncrement Primary Key )
* use rsMaster.FindFirst .....
- No, a NEW record, let's add this tblAgent record to tblMaster
* use rsMaster.addNew ...
* tblAgent.CustomerID will goto tblMaster.OriginalI
* this record will have a new CustomerI
* Get this new CustomerID
** use this new CustomerID to create a child record in tblProduct
- Yes (let's ignore for now
- read next tblAgent record til EO
- now, if all goes well, we will >> rsMaster.Update, rsProducts.Upat
- otherwise, we abort the operation and the tables remain unchanged

Question is after the rsMaster.addNew and assigning data into fields, how do I get the new CustomerID so I have this number I can use when I add records to tblProducts

Thank you for any help given
 
The line below with "Bookmark" does the magic. It points the 'current record'
at the record that you just added:

rsMaster.AddNew
rsMaster!OriginalID = rsAgent!CustomerID
rsMaster.Update
rsMaster.Bookmark = rsMaster.LastModified
SaveCustomerID = rsMaster!CustomerID

Rick
 
After you have finished editing the new record in tblMaster you have to use
rsMaster.Update to update the record. Then you have to move to the new
record in rsMaster using the Move method, and supplying the bookmark to this
record as rsMaster.LastModified. (I think the exact syntax is "rsMaster.Move
0, rsMaster.LastModified", but I've had some wine and can't be too sure that
what I'm typing is coherent English). Then you can retrieve its autonumber
field value.

cameron said:
Hi,

I am writing a function to 'import data' from tblAgent to tblMaster. Here is my logic :

- read one tblAgent record
- check if tblAgent.CustomerID exists in tblMaster.CustomerID
Click to expand...
(CustomerID is autoIncrement Primary Key ) ?
* use rsMaster.FindFirst .....
- No, a NEW record, let's add this tblAgent record to tblMaster
* use rsMaster.addNew ....
* tblAgent.CustomerID will goto tblMaster.OriginalID
* this record will have a new CustomerID
* Get this new CustomerID
** use this new CustomerID to create a child record in tblProducts
- Yes (let's ignore for now)
- read next tblAgent record til EOF
- now, if all goes well, we will >> rsMaster.Update, rsProducts.Upate
- otherwise, we abort the operation and the tables remain unchanged.

Question is after the rsMaster.addNew and assigning data into fields, how
Click to expand...
do I get the new CustomerID so I have this number I can use when I add
records to tblProducts.
 
cameron said:
Hi,

I am writing a function to 'import data' from tblAgent to tblMaster.
Here is my logic :

- read one tblAgent record
- check if tblAgent.CustomerID exists in tblMaster.CustomerID
(CustomerID is autoIncrement Primary Key ) ? * use
rsMaster.FindFirst ..... - No, a NEW record, let's add this
tblAgent record to tblMaster
* use rsMaster.addNew ....
* tblAgent.CustomerID will goto tblMaster.OriginalID
* this record will have a new CustomerID
* Get this new CustomerID
** use this new CustomerID to create a child
record in tblProducts
- Yes (let's ignore for now)
- read next tblAgent record til EOF
- now, if all goes well, we will >> rsMaster.Update, rsProducts.Upate
- otherwise, we abort the operation and the tables remain unchanged.

Question is after the rsMaster.addNew and assigning data into fields,
how do I get the new CustomerID so I have this number I can use when
I add records to tblProducts.

Thank you for any help given.
Click to expand...

If these are recordsets open on local Jet tables, then I'm pretty sure
the new autonumber rsMaster!CustomerID is available as soon as you call
the rsMaster.AddNew method. I *think* that also works if they are
linked Jet tables. If they are linked SQL Server tables, or for maximum
safety in the event of future upsizing, then do it like this:

Dim lngNewCustomerID As Long

With rsMaster
.AddNew
' ... assign values to fields in new record.
.Update
.Bookmark = .LastModified
lngNewCustomerID = !CustomerID
End With
 
Dirk Goldgar said:
If these are recordsets open on local Jet tables, then I'm pretty sure
the new autonumber rsMaster!CustomerID is available as soon as you call
the rsMaster.AddNew method. I *think* that also works if they are
linked Jet tables. If they are linked SQL Server tables, or for maximum
safety in the event of future upsizing, then do it like this:
Click to expand...

I had a need to do this yesterday, and had to move to the new record to get
the new autonumber. Without the move, the number returned was the number of
the current record before adding the new record. I checked this in the
Access Developers Handbook and this confirmed the need to explicitly move to
the new record.
 
Andrew Smith said:
I had a need to do this yesterday, and had to move to the new record
to get the new autonumber. Without the move, the number returned was
the number of the current record before adding the new record. I
checked this in the Access Developers Handbook and this confirmed the
need to explicitly move to the new record.
Click to expand...

You are mistaken, within the contraints I specified. Consider this
routine:

'----- start of code -----
Sub AddRecordGetAutoNumber()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1")

With rs
If Not .EOF Then
Debug.Print "Current record ID is " & !ID
End If
.AddNew
Debug.Print "New record ID is " & !ID
!Desc = "This is a test record."
.Update
Debug.Print "Current record ID is " & !ID
.Close
End With

Set rs = Nothing
Set db = Nothing

End Sub
'----- end of code -----

Table1 is a simple test table with an autonumber field named "ID" and a
text field named "Desc". When I put the above code in a standard module
and run it, this is what gets printed in the immediate window:

Current record ID is 3
New record ID is 174
Current record ID is 3

You see, between AddNew and Update, the current record is the new record
being added, and the autonumber has been populated. After the update,
the current record is the one that was current before AddNew was called.
 
Thanks for the clarification. I was trying to get the value of the
autonumber after running the Update method.
 
Hi

Appreciate all you guys helping...and it works as I needed to

Thank you once again

cameron :-

----- Dirk Goldgar wrote: ----

Andrew Smith said:
to get the new autonumber. Without the move, the number returned wa
the number of the current record before adding the new record.
checked this in the Access Developers Handbook and this confirmed th
need to explicitly move to the new record
Click to expand...

You are mistaken, within the contraints I specified. Consider thi
routine

'----- start of code ----
Sub AddRecordGetAutoNumber(

Dim db As DAO.Databas
Dim rs As DAO.Recordse

Set db = CurrentD
Set rs = db.OpenRecordset("Table1"

With r
If Not .EOF The
Debug.Print "Current record ID is " & !I
End I
.AddNe
Debug.Print "New record ID is " & !I
!Desc = "This is a test record.
.Updat
Debug.Print "Current record ID is " & !I
.Clos
End Wit

Set rs = Nothin
Set db = Nothin

End Su
'----- end of code ----

Table1 is a simple test table with an autonumber field named "ID" and
text field named "Desc". When I put the above code in a standard modul
and run it, this is what gets printed in the immediate window

Current record ID is
New record ID is 17
Current record ID is

You see, between AddNew and Update, the current record is the new recor
being added, and the autonumber has been populated. After the update
the current record is the one that was current before AddNew was called
 
Back
Top