Unanswered: Finding Primary key of a recordset inserted programatically
Does anyone out there know how to find the primary key value of a record that you have just inserted via the .addnew and .update of ADODB for Access 2002? I am trying to make a function that, for a given combo box, if a value isn't in the list a message box pops up saying that that particular Client, say, doesn't exist, would you like to enter it. If the user clicks yes I add that to the appropriate table, recalc the form to refresh the query underlying the combo box, and then enter the primary key value of the added record in the combo box. I was planning on only using this on tables where the primary key value is an autonumber (which happens to be most of my tables). I'm thinking that there may be an ado property which refers to the primary key for the current recordset but I may be wrong--I certainly didn't find it in the vb help files. I am contemplating just taking the easy way out and having one of the function's parameters be the name of the key value field for the table that the record would be entered into but wondered if there was a more elegant and univeral way to get the value of this key field for the inserted record. For simplicity sake the the name of the table would be one of the function's parameters and and the sql for the rec.open would be "SELECT * FROM " & name of the table. Thank you in advance for your time and your responses.
When you use an AddNew method, the autonumber field is automatically populated with the new key value. Just grab the value of that field to a variable. You can grab the value before the update event if you want.
This is why, if an AddNew method is canceled, the key value is still consumed - it will appear as though the number is missing on the table.
Grabbing it before update works well--it was what I was going to do if I couldn't find another way. I was just wondering if there was something like SQLServer's @@IDENTITY in Access--I know that that is a tall order for a personal database probably.
As best as I can tell, when a new record is requested in Access, it automatically reserves the next key field. That's why the number is consumed even if the new record is cancelled.
Makes sense, I suppose, in the scererio where I request a new record and while I am slowly typing in my data you request a new record. So, even if I decide to cancel, you are guarenteed a unique number.
The thing that's not so good is that FDA Part 11 requires accountability for all records and a canceled record looks just like a deleted record. So, new records go in using recordsets and unbound forms so that a cancelled new record is cancelled before the number is assigned.
I'm not so worried about the FDA but I suppose if you made audit trail tables along with success and failure indicators this might help. I know some about several DBMS programs and I was hoping that there was something liking @@IDENTITY or .CURVAL from ORACLE to grab the identity field without having to name it as a parameter each time that I run the function. It really is not a big deal, just a pie in the sky, wishing and hoping kind of thing. Thank you for your replies.