insert id

[mlh407]

Hi all,
I am using access as a front end for mysql.
I have a field type called id, set to (auto_increment)

When I insert a record in there it will push the count up by one.

If I run a insert query, afterward is there any way to pull the id of the record I just inserted?

 

[Pat Hartman]

Not with an insert query unless you are the only user in the database. I don't ever use insert queries for single records in the code behind forms. I always use DAO (or ADO). With either DAO or ADO, you can obtain the autonumber immediately following the .AddNew method provided the database is Jet.

If you have all the data you just inserted and none of the fields are null, you can retrieve the record you just entered by including all fields in the where clause -
strSQL = "Select TheAutonumberField From YourTable Where fld1 = " & Me.fld1 & " AND fld2 = " & Me.fld2 & " AND fld3 = " & Me.fld3

 

[workmad3]

Or by running the query

"SELECT MAX(ID) AS lastno FROM

" dumping it into a recordset and getting the value out.

This works on the idea that the last id entered will be the highest number in the id field (This holds up as even if you delete a record, the autonumber field wont fill in the gaps and always goes to the next number in its sequence)

 

[Pat Hartman]

Workmad3, That only works if no one else is doing data entry. If they are, you can't guarentee that you are getting your last record rather than theirs.

 

[modest]

I had to do something similar... I have used another "Temp" table for people currently working on a new record. It:
1) Gets highest number from main table and add 1
2) Check if number exists in temp table (if it does add 1 again, check again...)
3) Store number to temp table
4) When ready to save, copy data to main table and delete from temp