Incremental file number

Question

I would like to have a field where I can incrementally add file numbers.  In addition I would like (if I can do it) to have the year appended to the file number, as such: YY-0000, YY-0001, YY-0002, etc.  Is there any way to do this?  Through a mask?  Please keep in mind that I'm a very basic Access user.  Thanks.

Answer 1

You might like to take a look at CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option for sequentially numbering rows by group, in my case gender.  In your case it would be customer and year.  Any conflicts which arise when two or more users are inserting a record simultaneously, and thus get the same number, are trapped and corrected in the form's Error event procedure.  This avoids prejudicing the integrity of the data base by prematurely saving an incomplete record.  The start number for each sequence defaults to 1, but can be set to any integer number in the form.

In my demo the next number per gender is computed by the following function:

Private Function GetNameID(strGender As String, intStartSequence As Integer)

    strCriteria = "Gender = """ & strGender & """"

    GetNameID = Nz(DMax("NameID", "NamesByGender", strCriteria), intStartSequence - 1) + 1

End Function

As, in your case, the sequence is determined by the combination of customer and year, you would have to amend your equivalent of this function so that it accepts two arguments, e.g. intInvoiceYear and lngCustomerID.  The strCriteria variable would then be assigned a value by building a string expression which uses both values in a Boolean AND operation, e.g.

    strCriteria = "Year(InvoiceDate) = " & intInvoiceYear & " AND CustomerID = " & lngCustomerID

Answer 2

Please see my blog article on Sequential Numbering (see sig for blog address). It deals with the scenario you have as well as others, with detailed instructions on how to accomplish what you want.

Answer 3

A Sequence field is generally controlled by you, not Access. The Sequence may change for different root elements, so you may need to use criteria when doing this.

To get the next sequence number, you can use DMax:

Dim NextSequenceNumber as Double

NextSequenceNumber = Nz(DMax("YourSequenceField", "YourTable"),0) + 1

If you want to start your sequence at a value other than one, increase the 0 value in the line above.

Of course, you can add criteria to the DMax call as well. For example, if you want to get the highest sequence number for the value "Ford" in the Customer column:

NextSequenceNumber = Nz(DMax("YourSequenceField", "YourTable", "Customer='Ford'"),0) + 1

If you want to get it for the value currently in a control named txCustomer:

NextSequenceNumber = Nz(DMax("YourSequenceField", "YourTable", "Customer='" & Me.txCustomer & "'"),0) + 1

More information on DMax:

https://msdn.microsoft.com/en-us/library/office/ff835050.aspx?f=255&MSPPError=-2147217396

I was able to get Access to do this back in 1998, but it's changed a lot since then, nes pa?

Access has change a LOT since that time :). However, Access has never had a "sequence" number. It does include the AutoNumber field. The AutoNumber field should never be used for anything that has meaning, however. It's simply a surrogate key (i.e. a record pointer) that can be used to identify a specific record. It's guaranteed to be unique, but not necessarily sequential. If you need a true sequence field, you'll have to create it yourself (and it's always been that way, since A2.0)

Answer 4

Thank you, Scott.  I see what you're saying.  That's certainly something I can do, using separate fields for the year and the file number.  However, how do I get the file number field to add an incremental number, like checks in a checkbook (or is that too old fashioned of an example?)?  I thought about AutoNumber, but I don't want to start with "1".  Any further ideas?  Also, how would I get the number to reset each year on January 1?

Thanks.

P.S.  I was able to get Access to do this back in 1998, but it's changed a lot since then, nes pa?

Answer 5

When you need logic applied to a data member like this, you should store the root values of that element. For example, if I wanted a sequence number like this for an Invoice number:

Customer-Year-Sequence

I'd store the Customer, Date and Sequence number individually, and then use code to combine them together when needed (like on my Form or Report). I would NOT store a formatted sequence like FORD-17-002 but would instead store "FORD" in my Customer field (or the ID value that relates the record back to my Customer table), the Invoice Date value in my InvoiceDate field, and the sequence number in my SequenceNumber field. When I needed that value I'd use something like this:

DLookup("CustomerName", "Customers", "CustomerID=" & Me.CustomerID) & "-" & Year(Me.InvoiceDate) & "-" & Me.SequenceNumber

That would give me the UI value that I want to see.

This follows the best practice of never storing "calculated values" in a field. Calculated Values are anything that is derived from other values. The reasoning behind it is simple - if one of the root values ever changes, your stored calculated value would have to change as well, and that can be very difficult to manage.