How can i generate an auto #

[othmanyg]

Hello,

How can i generate a record auto number which starts with the year and then three digits that increments every time i add a new record, looks like 04-001.

Thanks

 

[WayneRyan]

oth,

If you want to (in addition to) using Access' AutoNumber facility make your
own numbers ...

Split your number into two fields:

TheYear - Integer
TheIDField - Sequential

Then, you can use the DMax function to retrieve the maximum number for
that year, increment it by one, and assign it to your key fields.

You can use the BeforeInsert event on your form.

Wayne

 

[othmanyg]

How do i use the DMax function

Thanks but how do I use the DMax function? I want that 04-001 number appear every time i click on new entry button in the Control # Field.

your help is much appreciated.

 

[WayneRyan]

oth,

I added a new field SeqNumber to your table & your form.

Using the BeforeInsert event of your form, you can use the DMax
function to generate your desired number.

Keep your AutoNumber field (you don't have to display it).

The new field should be displayed for the user, but they should not
edit it.

Hope that gets you started.

Wayne

 

[WayneRyan]

oth,

Maybe the DB would help.

Wayne

 

[othmanyg]

It works great!!!!!

Great....it works, thanks very much WayneRyan. That was a great help. Appreciate it.

Good day