I need to create an incrementing number to be used as a primary key in a table, based on a few variables. The format will be YYMM0000. It represents the number of units received each month.

I can come up with the formula to create that string no problem, but I need to create some code to check the table MAIN for the field TRACKER_ID to find the last number and increment it by 1.

Can anyone help? :confused:

Take a hint from Pat Hartman as she has noted in several posts here. Am supplying a few links with info on this:

http://www.access-programmers.co.uk/forums/showthread.php?t=114747&highlight=autonumber

http://www.access-programmers.co.uk/forums/showthread.php?t=108118&highlight=autonumber

I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeUpdate

Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. Its much easier to store and group them when they're stored separately.

Its also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was 2006 14 the next will be 2006 15.