Getting Record ID

[brianh]

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?

[brianh]

Anyone?

[kleinma]

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...

[brianh]

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?

[kleinma]

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

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...

[brianh]

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.

[kleinma]

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")

[brianh]

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?

[brianh]

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

[kleinma]

no problem... happy to help