retrieving last used key field in a table

[llaman]

I have an invoice table with each invoice having a unique id_number

I need to pull the last used id_number, and increment it by one to put in with the new invoices I update the table with (using SQL statement).

This needs to be done inside of my module I wrote. Anyone know what the code for this would look like? or where i could find an example of this?

 

[JoeTweak]

Why not use the autoincrement data type?

 

[mdusoe]

I assume you are writing the SQL INSERT statement "from scratch", using strictly code. While the AutoNumber option still is probably your best bet, you could use some code like this if you must do it through VBA:

NewKey = DMax("KeyField", "MyTable") + 1

Then you can use NewKey in your insert query.

HTH,
Mike.

 

[llaman]

the first option is probably best, but at the point i'm at, i'd rather not go back and change it (would end up bein more work than the 2nd part, which i already have everything set up for)

can u set that equal to an Integer right off or should it be as a variant?

 

[mdusoe]

It will be an integer.