Getting Record ID

I am upadting a DB using an insert statement. I would like to retirieve the record ID (Auto Increment Value in DB) from that new record. I tried the following syntax right after the Insert statement was executed:

RS.open strSQL, conn
ID = rs("Int_ID")

So it opens the record set and with strSQL it inserts a record. I now want to get the Int_ID which is Autom Increment. It says....Item cannot be found in the collection corresponding to the requested name or ordinal. Any suggestions?

Anyone?

well the reason you are getting that particular error is because you are opening a recordset with a insert statement.. so you are not actually opening a recordset.. you are not putting any values in the RS object... you actually don't even need a recordset for an insert statement.. you could use the connection.execute(sql) statement...

OK, so I did that because I do not need the rs, now I want to get the current record ID. Do I have to run a query and get the last record?

well that is how i always did it.. not to say there isn't a better way... buy yeah i would insert the record and then open a recordset with the last record in it... and there would be the ID for that record :D :D

Depending on what database format you are using... there may be different ways.. like I know SQL server has @@IDENTITY but access jet does not.. things like that...

Using mySQL, I am so used to using Access and all of the things that i can do in there. This is not bad though. I just need to keep learning. It does not want to use the rs.movelast method.

try using

strSQL = "SELECT MAX(Int_ID) AS AutoNum FROM MyTable"

if your autonumber incriments(not random)... then this will return the highest autonumber in the table...

msgbox rs("AutoNum")

Now that seems to work great, the syntax is correct because I ran it on the mySQL server and it returned the last record. But how do I put that result into a var now?

Thank you, I got it now. Sorry for being so dense!

no problem... happy to help