Access SQL Question

[JasonGS]

Using Access 2000, Visual Basic, ADO 2.1...

Does anyone know how to get the primary key of a record that was just inserted? The table contains a autonumber primary key, and I want to give that primary key back to the person to serve as a "ticket number".

Can I do something like adoRset("@@IDENTITY").Value or something to get this number?

[HunterMcCray]

Originally posted by JasonGS
Using Access 2000, Visual Basic, ADO 2.1...

Does anyone know how to get the primary key of a record that was just inserted? The table contains a autonumber primary key, and I want to give that primary key back to the person to serve as a "ticket number".

Can I do something like adoRset("@@IDENTITY").Value or something to get this number?

I have not used ADO, but I do not think that you should have any trouble using this method in ADO:

.
.
.
.
recordset.update
recordset.movelast
TicketNumber=recordset!Fieldname
.
.
.
Because the last record added in a an auto numbered table should be the last record this should return the desired record. If you have a large number of users adding records then there is a possibility that this might cause errors. In this case I would suggest that you either do away with auto number, or generate a unique field such as employee number, or computer number and add this field to the table. Then in your SQL WHERE arguement return a recordset with only the particular user id sorted by the key index, then the movelast method will be guranteed to return the last record added by that particular user.

Hope this helps,

Hunter

alternatvely you coud open a query based on your table, that returns 0 records. "Select * From tblTest Where Id=0" will work nicely, if Id is an AutoValue. Then do an .AddNew followed by an .Update and by a .MoveFirst.

best regards

Sascha