retrieve last autonumber from append and use that as FK for another table

[icemonster]

so how is this achieved? how do i retrieve each pk from an append sql statement and use that pk as an fk for another table used to combine it with another record. say a phone number and a client.

 

[Galaxiom]

Typically this task would be done with a form and subform bound to the respective tables. The LinkFields properties automatically insert the corresponding key in the subform records.

If you really do need to be inserting records from unbound forms into multiple tables I would not use an autonumber as the key.

Retreiving "the last autonumber" can be more complicated and error prone than expected particularly when you have multiple users.

 

[Mr. B]

Perhaps this link will provide you with what you need to get the record ID of the most recently added record:
http://stackoverflow.com/questions/8533283/how-do-we-get-last-inserted-record-in-msaccess-query

Here is another link with essentially the same info:
http://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba

Also take a look at the knowledge bass article from Microsoft:
http://support.microsoft.com/kb/815629

 

[Pat Hartman]

To get the autonumber, you will need to do the insert with DAO or ADO. Here's a DAO example:

Dim db as DAO.Database
Dim td as DAO.Tabledef
Dim rs as DAO.Recordset
Dim SaveID as Long

'''''Set td = Currentdb.Tabledefs!yourtablename -- doesn't work
Set db =  CurrentDB()
Set td = db.Tabledefs!yourTablename 
Set rs = td.OpenRecordset

rs.AddNew
    rs!fld1 = Me.fld1
    rs!fld2 = Me.fld2
    ....
    SaveID = rs!autonumberID
rs.Update

You need to capture the autonumber between the .AddNew and the .Update.
Note that this only works with Jet/ACE. For SQL server you need to use one of the @@Identity methods to retrieve the identity column inserted on this thread.

 

[Galaxiom]

Dim td as DAO.Tabledef
Dim rs as DAO.Recordset
 
Set td = Currentdb.Tabledefs!yourtablename
Set rs = td.OpenRecordset

This bit of the code doesn't actually work for exact reasons still unknown.

One solution is to Set a variable to Currentdb first.

Dim db As DAO.Database
Dim td as DAO.Tabledef
Dim rs as DAO.Recordset
 
Set db = CurrentDb
Set td = db.Tabledefs!yourtablename
Set rs = td.OpenRecordset

Either that or simply:

Dim rs as DAO.Recordset
 
Set rs = CurrentDb.TableDefs!yourtablename.OpenRecordset

I have discussing this exact problem for several days with other members who mainly either thought it was an obscure issue or I was "writing faulty code to prove a point".

Excuse my amusement but Pat also participated in that thread so might have seen it coming.

See Post #66 and onwards
http://www.access-programmers.co.uk/forums/showthread.php?t=86193&page=5

 

[RainLover]

so how is this achieved? how do i retrieve each pk from an append sql statement and use that pk as an fk for another table used to combine it with another record. say a phone number and a client.

Do I understand this correctly?

You are appending a group of records from a Table "TableOne" that has a Primary Key, to a different table "TableTwo".

This can't be right. Could you please explain where I am wrong.

Posting your SQL: may also help.

 

[ChrisO]

To retrieve the last Auto Number after a DAO AddNew…

No requirement for references.
DAO and ADO may or may not be there but, if they are, they can be at either priority.
No variables are declared and so no disassociation between variables can occur.
(Common parlance; with no variables there are no variables to go out of scope.)
The With block holds the pointer.
The With block goes out of scope at the End With.
There is no need to Close the CurrentDb; what was made a no-op many years ago.
There is no need to close the OpenRecordset, that was proved many years ago.

Sub Test()
    
    With CurrentDb.OpenRecordset("YourTableName")
        .AddNew
            !fld1 = "Sam"
            MsgBox !AutoNumberID
        .Update
    End With
    
End Sub

It works.

Chris.

 

[Galaxiom]

No variables are declared and so no disassociation between variables can occur.

Could you please post more detail explaining what you mean by this concept of "disassociation between variables". It is not a term that shows up in this context when I Google it.

 

[ChrisO]

In this case I prefer the word disassociation over scope because scope is not necessarily correct. When an Object variable goes out of scope its value is reset to 0.

Sub TestIt1()
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset

100    MsgBox ObjPtr(td)
200    MsgBox ObjPtr(CurrentDb.TableDefs!yourtablename)
300    Set td = CurrentDb.TableDefs!yourtablename
400    MsgBox ObjPtr(td)
500    Set rs = td.OpenRecordset

End Sub

In line 100 td has a value of 0; it was set to Nothing when it was dimensioned.
In Line 200 CurrentDb.TableDefs!yourtablename has a non zero value.
In Line 300 td is assigned the value of CurrentDb.TableDefs!yourtablename.
In Line 400 td still has the value assigned to it.
In Line 500 td fails, yet is has not been reset to Nothing.

By Line 500, in the normal sense of the word scope, td has not gone out of scope; it still has its value.
But td has been disassociated from CurrentDb.TableDefs!yourtablename and we get an error.

Therefore, to me, the word scope is inappropriate under this circumstance so I prefer the word disassociated.

Feel free to choose your own word for this, perhaps unique, circumstance.

Chris.

 

[Galaxiom]

I agree it isn't really about scope per se. In my tests, when I declared the variable as a Global (maximum scope) it still did not hold which would surely indicate a failure of line 300 to instantiate.

I think the ultimate cause of this problem is an "undocumented feature" in the way the CurrentDb function handles the TableDefs collection.

 

[ChrisO]

What exactly do you mean by "undocumented feature"?

Chris.

 

[Pat Hartman]

Excuse my amusement but Pat also participated in that thread so might have seen it coming

I stopped following the thread. I though this was what you folks had decided on. It is not what I normally use.

You are appending a group of records from a Table "TableOne" that has a Primary Key, to a different table "TableTwo".

This can't be right. Could you please explain where I am wrong.

No. He needs the PK so he can use it as the FK in child records.


Gentlemen,
Can you please take this discussion elsewhere. Then please come back and post your findings.
Thanks

 

[RainLover]

Pat

No. He needs the PK so he can use it as the FK in child records.

Perhaps he does. I am not so sure because the heading is singular and then in the question uses "Each PK" meaning plural. Hence I asked for clarification.

Also I tested your code and received the error that Galaxiom refers to. ChrisO's alternative appears to be correct.

One thing I was impressed with was your simple piece of advice.

You need to capture the autonumber between the .AddNew and the .Update.

I read some of the links which go into great detail of complexity. You did it in one line.

 

[Galaxiom]

What exactly do you mean by "undocumented feature"?

It is a euphamism for "bug".

 

[ChrisO]

To comply with Pat’s request can we please take this back to the other thread?

Chris.

 

[icemonster]

wow. thanks for the many replies. ok, so typically, this is how i would retrieve the last autonumber:

                        If Not IsNull(me.txtphonenumber) Then
                            
                            strSQL = "INSERT INTO tbl_contact_info " _
                                    & "(coninf_id_contact_info_class, " _
                                    & "coninf_def, " _
                                    & "coninf_id_record_information) VALUES " _
                                    & "(11, " _
                                    & "'" & me.txtphonenumber & "', " _
                                    & "" & lngLastRecInfoID & ");"
                            .Execute strSQL, , adCmdText + adExecuteNoRecords
                            strSQL = "SELECT Last_Insert_ID();"
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    lngLastPrimaryKey = .Fields(0)
                                Else
                                    'abort
                                End If
                            End With
                         End If

but the thing i want to achieve is this, say you have a listbox, but the listbox entries are stored temporarily in one of your temp tables and when it comes to the point that you need to append the records on the listbox to a permanent table. so i've been trying to be able to append the records from the temp table to my main table. but for "each" record appended, i need the primary key so that i can use that primary key as a FK with another table.

so basically what i want to achieve is something like:

For each PK in row inserted
Insert to new table as FK.

 

[Pat Hartman]

I gave you the code to insert a single row and capture the PK. Turn that into a loop.

There are other methods to get the last inserted PK although they are not at all reliable in a multi-user environment. There is no method to get the last n PKs. You would have to add a "batch" identifier to each new set of records. You could then use that "batch" identifier to retrieve the records and get their PKs.

However, there is no purpose to adding dummy records to a many-side table. So
"Fore each PK in a row inserted, Insert to new table as FK" doesn't make any sense.