Custom autonumber fields? (1 Viewer)

jonathanchye · Mar 28, 2011

Hi,

I am wondering if it would be possible to apply a mask to an autonumber field and also have it to start at a custom number?

For example, I would like my PK field "InvoiceNo" to start from IV8888 and autonumber from then on...

I am trying to create a field that starts with a number for ex 8887 and then create another field that adds 1 to the field and apply a "IV" mask but I can't get it working for new records and it won't accept being a primary key...

DCrake · Mar 28, 2011

if you want to use a sequential numbering system then do not use auto numbers for this task as even though they are sequential they are not always consecutive as deleted records are not reused.

jonathanchye · Mar 28, 2011

DCrake said:
if you want to use a sequential numbering system then do not use auto numbers for this task as even though they are sequential they are not always consecutive as deleted records are not reused.

Yeah true. I am trying to create a dummy field containing the sequential number I want the form to start and then have an auto calculated field with generates the next number on new records but not having much luck atm... I need to have a field which supports mask and autocalculation but right now it seems its either one or the other.

DCrake · Mar 28, 2011

All you need is a numeric field that can be used to hold the sequential consecutive number. To generate the next number you simple do a DMax([FieldName],"TableName")+1 to get the next number and then concatenate "INV" to it

Thus:

Code:

NextID = [B]"INV" & DMax([FieldName],"TableName")+1[/B]

jonathanchye · Mar 28, 2011

DCrake said:
All you need is a numeric field that can be used to hold the sequential consecutive number. To generate the next number you simple do a DMax([FieldName],"TableName")+1 to get the next number and then concatenate "INV" to it

Thus:

Code:

NextID = [B]"INV" & DMax([FieldName],"TableName")+1[/B]

Hmm, not 100% clear there. When you say a numeric field should I create a new field which only contain one record (which would be the starting number ie 8888?)

Or should I create a field called "nextID" and set that as the Expression?

DCrake · Mar 28, 2011

Apart from your primary key autonumber field - if you have one - you would add a new field called InvNumber this is where you would store the numeric part of the invoice number. There is no need to store the INV prefix as this is a constant string and only takes ou space.

jonathanchye · Mar 28, 2011

hmm getting expression errors. Maybe I show explain more.

Here is the fields of my table called tblInvoices

invNo - at the moment its set to Calculated and I've paste the expresion you've given
CustomerName - Text
ReferenceName - Text
RefNo - Text

I keep getting the error field not found.

edit: I want the field invNO to autogenerate and start at a custom value

DCrake · Mar 28, 2011

You can't do this at table level. You need to do this at the point you create an invoice via a form.

jonathanchye · Mar 28, 2011

hmm ok, I guess I would then have to rely on users to enter the right number everytime...

Basically what happens is a unique invoice number is generated everytime the user submits a work order. It would be nice if that could be sequential so when user adds new form the number is automatically generated and all they have to do is fill in rest on form.

Is there no formula to increment whatever value in the field by 1 everytime a new record is created?

DCrake · Mar 28, 2011

This is what I have given you.

Your user opens a new form to crete the order.

Have the field InvNo on your form set the enabled property to False and the Locked property to True

Set the default value to DMax("InvNo","Orders")+1

Where Orders is the name of the table being used.

This means that the end user does not have to generate the number themselves the form does that for them.

But be careful, if more than one person has this form open at the same time they may end up with the same inv no. This is why it should be generated at the exact point that the record is first created. Thus eliminating duplications.

jonathanchye · Mar 28, 2011

Ah ok... so for this to work I need to put in an initial value in InvNo ?

DCrake said:
This is what I have given you.

Your user opens a new form to crete the order.

Have the field InvNo on your form set the enabled property to False and the Locked property to True

Set the default value to DMax("InvNo","Orders")+1

Where Orders is the name of the table being used.

This means that the end user does not have to generate the number themselves the form does that for them.

But be careful, if more than one person has this form open at the same time they may end up with the same inv no. This is why it should be generated at the exact point that the record is first created. Thus eliminating duplications.

DCrake · Mar 28, 2011

Yes, if you simply add the field to a table and the table does not contain any records then obviously the Next invoice number is going to be 1. Therefore make sure that the first record added is where you want to start counting from.

jonathanchye · Mar 28, 2011

Thanks mate

Never thought of using Forms to enfornce autonumbering. Always thought it must be done at table level!

DCrake · Mar 28, 2011

When you want to display the invoice number in your query use the following syntax

InvoiceNo:"INV" & Format([InvNo],"00000")

This will ensure that the results look like below

INV08888
INV08889
....
INV10001
INV10002

Not

INV8888
INV8889
INV10001
INV10002

jonathanchye · Mar 28, 2011

Thanks again for the additional tip. Useful when I design the reports.

The invoice numbers are now in the early 5 digits so I guess there's no need to format them for the moment but I think I would have to design for 6 digits as well.

jonathanchye · Mar 28, 2011

Right, when I create a listbox to display the results from the table it displays without the INV prefix (which is set to a mask in the table).

Whereabouts in the query do I enter this syntax? I am in the Query builder view at the moment...

DCrake said:
When you want to display the invoice number in your query use the following syntax

InvoiceNo:"INV" & Format([InvNo],"00000")

This will ensure that the results look like below

INV08888
INV08889
....
INV10001
INV10002

Not

INV8888
INV8889
INV10001
INV10002

Custom autonumber fields? (1 Viewer)

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

jonathanchye

Registered User.

Similar threads

Users who are viewing this thread