Pass last AutoNumber generated as a variable and loop on a listbox to populate table

[Etxezarreta]

Hello everyone,
In a form, I populate the source table DSP_RDV_CRRDV_t_CompteRendusListesThematiques" with some data, and I will send selected items of the list boxes to anoteher one: "DSP_RDV_CRRDV_t_CompteRendusListesThematiques", using a loop. To populate a field of this last table, I need to obtain the primary key generated in my first table (in order to create a foreign key).
The problem is: I dont know how to pass from str_Sql = "SELECT MAX(ID_CompteRendu_RDV) AS max FROM DSP_RDV_CRRDV_t_CompteRendus" to the value of this string..
I have bee trying to use ADO method, but I am a bit confused: the recordset is a single value, how can I assign this value to my variable, intMaxOfID_CompteRendu_RDV, that will be the value of the FK_CompteRendu_RDV field of the DSP_RDV_CRRDV_t_CompteRendusListesThematiques table?
I guess this is easy: you will find my trial in "Private Sub bt_ValiderChoixPraticien_Click()"
Thanks.
Etxe.

 

[Isaac]

You might be able to use DMAX() instead of opening a recordset?

 

[isladogs]

Your table naming convention is getting very complex but, unless I'm mistaken, you appear to be referring to the same table in the first two lines of your post.

 

[Isaac]

If you use the CurrentDB.Execute method to perform the Insert, you should probably use @@Identity to guarantee you are getting the corresponding ID rather than just the max.
For reference:

How to get last inserted ID in a table

Hi, I have this code in Access VBA and it is not working, I hope somebody can give me some help on this: Private Sub btnAddWorkingday_Click() Dim strSQL As String Dim strDate As Integer Dim lastID As Integer strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE) VALUES...

www.access-programmers.co.uk

 

[Pat Hartman]

This is DAO code. I don't use ADO but the code might be the same.

Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
NewProvProcID = rsScope!NewID

Once you've saved the ID to a variable, you can just use that variable in your other SQL strings.


PS - if you use both ADO and DAO code in the same database, you must ALWAYS disambiguate ALL variable declarations since there is overlap with object types such as database and recordset.

 

[deletedT]

I use the following to find out the last inserted PK:

CurrentDb.Execute sql
SN = CurrentDb.OpenRecordset("select @@identity")(0)

Edit: Sorry I didn't notice @Pat Hartman had already suggested the same way.

 

[Etxezarreta]

Ok, DMAX works, but as pisorsisacc pointed out, there are some reliability issues.
It also works with DAO and "SELECT @@IDENTITY as NewID", need to test it with ADO.
Thanks a lot!
Etxe.

 

[Isaac]

Ok, DMAX works, but as pisorsisacc pointed out, there are some reliability issues.
It also works with DAO and "SELECT @@IDENTITY as NewID", need to test it with ADO.
Thanks a lot!
Etxe.

You're very welcome.

One thing to note ... when you select the @@identity, it needs to be within the same database references as the insert.
It is no good to code

CurrentDb.Execute "something"
CurrentDb.OpenRecordset("select @@identity")(0)

You either need to do it within the same With block as the article suggested:

With CurrentDb
.execute "something"
.openrecordset 'for @@identity
End With

Or else, set a variable and do them both from the same, still initialized database reference:

Dim db as Dao.Database
Set db=CurrentDB
db.Execute "something"
db.Openrecordset 'for the @@identity grabbing

That is, if you want to achieve the value of making sure it's the last identity YOUR code inserted, and not some other FE user's. (which is the whole point of not using Dmax, after all). Every time you call CurrentDb, it's a separate database instance.

 

[Isaac]

Edit: Sorry I didn't notice @Pat Hartman had already suggested the same way.

And I did before she did. Lots of "votes of confidence", I guess--All good & part of the fun.

 

[pbaldy]

I often use the method Allen shows here, with the LastModified and bookmark:

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[Pat Hartman]

And I did before she did.

Except you didn't provide any code So I added the code.

 

[Isaac]

Except you didn't provide any code So I added the code

Amen !!!

 

[arnelgp]

from your code:

...
...
intMaxOfID_CompteRendu_RDV = adoRecSet(0)