If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to carture the newly autonumber in the primary field?
In a one to many table relationship, an "Insert" statement to the "one"
table would craete a new autonumber for the primary key on a row for the "one" table and this autonumber will then be used as secondary key on the "many" table. What I need is to retrieve that autonumber right after the "Insert" statement and show it to a textbox. Say I have the following codes: Docmd.RunSql "Insert Into Table1...bha bha bha" Me.Autonumber = newly created autonumber Should I use the DMAX function to capture the max. Autonumber or something I can embedded in the "Insert" statement to capture that autonumber number and save it as a variable to the cache? Thanks |
#2
|
|||
|
|||
How to carture the newly autonumber in the primary field?
Here's an example of how to retrieve the newly assigned autonumber value
from JET tables after executing an INSERT query: Function ShowIdentity() As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Set db = DBEngine(0)(0) db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;" Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;") ShowIdentity = rs!LastID rs.Close Set rs = Nothing Set db = Nothing End Function -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Paul" wrote in message ... In a one to many table relationship, an "Insert" statement to the "one" table would craete a new autonumber for the primary key on a row for the "one" table and this autonumber will then be used as secondary key on the "many" table. What I need is to retrieve that autonumber right after the "Insert" statement and show it to a textbox. Say I have the following codes: Docmd.RunSql "Insert Into Table1...bha bha bha" Me.Autonumber = newly created autonumber Should I use the DMAX function to capture the max. Autonumber or something I can embedded in the "Insert" statement to capture that autonumber number and save it as a variable to the cache? Thanks |
Thread Tools | |
Display Modes | |
|
|