text box with auto number (1 Viewer)

bigmac

Registered User.
Local time
Today, 06:40
Joined
Oct 5, 2008
Messages
295
hi all can you help please , I want a text box on my form to show a number that will generate a new number every time I create a new record, I now I can use the primary key but I want it to show "00000" to start and next record to show"00001" etc any ideas :confused:
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:40
Joined
Sep 21, 2011
Messages
14,041
Subtract 1 from the autonumber?
You might not get a complete sequence using the autonumber though.

Also see Similar Threads section at the bottom of this page.
 

mike60smart

Registered User.
Local time
Today, 13:40
Joined
Aug 6, 2017
Messages
1,899
Hi Bigmac

I would recommend you do NOT use that method.

In your table named tblCustomers create a field named UniqueNumber.

You should generate a Unique Number by using the Before Insert Event of the form by using the following:-

Me.UniqueNumber= Nz(DMax("UniqueNumber","tblCustomers"),0) + 1
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 19, 2002
Messages
42,970
You can force Mike's suggested UniqueNumber to display as zero-filled and 5 characters long on your form by setting the format to:
00000

Use 6 zeros if you want it to be 6 characters.
 

bigmac

Registered User.
Local time
Today, 06:40
Joined
Oct 5, 2008
Messages
295
mike60smart hi, why do you recommend I do not use the method I found?:confused:
 

mike60smart

Registered User.
Local time
Today, 13:40
Joined
Aug 6, 2017
Messages
1,899
Hi

You should not use an Autonumber to do anything other than give each record a unique id number, This number should then be hidden from the user.
 

bigmac

Registered User.
Local time
Today, 06:40
Joined
Oct 5, 2008
Messages
295
Hi Bigmac

I would recommend you do NOT use that method.

In your table named tblCustomers create a field named UniqueNumber.

You should generate a Unique Number by using the Before Insert Event of the form by using the following:-

Me.UniqueNumber= Nz(DMax("UniqueNumber","tblCustomers"),0) + 1

hi I have just tried this and it does not increment by 1 every time, any ideas:banghead:
 

mike60smart

Registered User.
Local time
Today, 13:40
Joined
Aug 6, 2017
Messages
1,899
Hi

Can you upload a zipped copy of the database so we can see what is happening?
 

missinglinq

AWF VIP
Local time
Today, 09:40
Joined
Jun 20, 2003
Messages
6,423
Mike is correct in not using an Autonumber field for this! My only differing with his advice is that the code should be in the Form_BeforeUpdate event...not the Form_BeforeInsert event...unless this is going to be used in a one-off database by a single user on a single machine.

If this is in a multi-user environment...you face the very real chance of having two or more Records with the same UniqueNumber, as the BeforeInsert event fires as soon as a single character is entered in any other Control.

So, you start creating a Record by entering data...the UniqueNumber is generated...and while you're still entering data in that Record...Joe across the room starts creating a Record by entering data...and the UniqueNumber is generated for that Record...the same, identical UniqueNumber...because the UniqueNumber from the first Record hasn't been saved yet!

Best place to generate this UniqueNumber is in the Form_BeforeUpdate event...which is to say the last possible second before the Record is saved. In all the years I've been doing this kind of thing I've never had a duplicate 'UniqueNumber' generated, using this approach.

Which begs the question...where did you place this code when it 'didn't work?' Just to be clear...this will only work when a new Record is generated...it's not going to retroactively number Records that already exist.

Linq ;0)>
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:40
Joined
Sep 12, 2006
Messages
15,613
@linq is correct - assigning an autonumber with dmax needs to be left to the very last moment, before you finally write the record.

if you need it earlier, because you want to record it on a log sheet for example, an alternative is to read the next number from a table, and then increment the numberin the table for the next user. However, you do then run the risk of leaving gaps in the sequence, if you cancel your entry. This is similar to an autonumber in fact - except that you may find an autonumber will jump to a different sequence from time to time.

The reason an autonumber is not what you want, is because an autonumber cannot guarantee an intact sequence.
 

mike60smart

Registered User.
Local time
Today, 13:40
Joined
Aug 6, 2017
Messages
1,899
Hi Dave & Ling

Thanks for the pointer about using Before Update vice Insert.

Will amend my VB snippet archive

Thanks again
:)
 

Users who are viewing this thread

Top Bottom