Pass last AutoNumber generated as a variable and loop on a listbox to populate table (1 Viewer)

Etxezarreta

Member
Local time
Today, 17:29
Joined
Apr 13, 2020
Messages
175
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.
 

Attachments

  • Essai.zip
    470.7 KB · Views: 114

Isaac

Lifelong Learner
Local time
Today, 09:29
Joined
Mar 14, 2017
Messages
8,738
You might be able to use DMAX() instead of opening a recordset?
 

isladogs

MVP / VIP
Local time
Today, 16:29
Joined
Jan 14, 2017
Messages
18,186
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

Lifelong Learner
Local time
Today, 09:29
Joined
Mar 14, 2017
Messages
8,738
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:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

Guest
Local time
Today, 16:29
Joined
Feb 2, 2019
Messages
1,218
I use the following to find out the last inserted PK:
SQL:
CurrentDb.Execute sql
SN = CurrentDb.OpenRecordset("select @@identity")(0)

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

Etxezarreta

Member
Local time
Today, 17:29
Joined
Apr 13, 2020
Messages
175
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

Lifelong Learner
Local time
Today, 09:29
Joined
Mar 14, 2017
Messages
8,738
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
Code:
CurrentDb.Execute "something"
CurrentDb.OpenRecordset("select @@identity")(0)

You either need to do it within the same With block as the article suggested:
Code:
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:
Code:
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.
 

Users who are viewing this thread

Top Bottom