Autonumber question (1 Viewer)

Cgaro62

New member
Local time
Today, 04:32
Joined
Jul 10, 2019
Messages
3
Hi Isladogs,


I hope so too! I am working on creating a custom auto number in for my form but so far, no luck.

The form is 2019 CAPAR Log, the field is "CAR Number" in which I am trying to create a formula that when a new record is entered it will provide me with an auto-number that begins with "2019-" followed by a 3-digit number. For example, 2019-001, 2019-002, 2019-003 and so on.



Do you think you could help? I'm a beginner when it comes to creating databases. :cool:
 

isladogs

MVP / VIP
Local time
Today, 08:32
Joined
Jan 14, 2017
Messages
18,186
Re: C-Ana

Hi
In the table design, go to the properties section for that field and enter "2019-"000
The autonumbering will be displayed as 2019-001,2019-002 etc but will actually be saved as 1,2 etc

For info I have moved this to the Tables section as it is a technical question
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Sep 12, 2006
Messages
15,613
I imagine you want to start the prefix from scratch each year, so you need a slghtly different solution to have 2019-nnn, then next year 2020-nnn, etc.

Rather than have one field, have 2 fields. Store the year in one field.
Make the other your sequence number.


You need to get the highest sequence number for the active year with a dmax

nextcarnumber = dmax("carnumber","tblcars","yearnumber = " & yearrequired),0)+1
 

Mark_

Longboard on the internet
Local time
Today, 01:32
Joined
Sep 12, 2017
Messages
2,111
One warning about "2019-###", what happens in 2021 when you have 2174 records for the year? Is this a possibility?
 

statsman

Active member
Local time
Today, 04:32
Joined
Aug 22, 2004
Messages
2,088
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 integer
TheSequence – long integer

Then enter this code in your form's AfterInsert

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. It’s much easier to store and group them when they're stored separately.

It’s 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
TheYear - 2006 TheSequence – 14
the next will be
TheYear - 2006 TheSequence – 15
assuming its still 2006. If this is the first entry in 2007 you will get:
TheYear – 2007 TheSequence – 1

Be sure to set TheYear and TheSequence text boxes on your forms to Enabled-NO Locked-YES. This will prevent people from overriding the pre-set numbers.

I use this function constantly to create Invoice Numbers.
 

Users who are viewing this thread

Top Bottom