Auto numbering in Type Text fields

[TamarackAero]

New to VBA but learning quickly,

I need a string for a ID example "WO" & year(date) & month(date) & an auto numbered consecutive non duplicate integer beginning at 1 at the start of each month.

Any help Thanks

[hansup]

Where does the date come from? Is that the VBA Date() function, when the row is inserted? Or a field like "effective date" or "submitted date"?

Show us some sample ID values. Do you want year as 2 or 4 digits? Should May be 5 or 05? How will you format the auto number part?

How will you use these ID values? As a sort key for recordsets? As a foreign key in other tables? If you want to select all rows from 2011, will you have to parse out the year from the ID or do you have another field to hold the year?

If each ID value starts with WO, what is the value of storing WO as part of the ID? You could add WO to the base part of the ID whenever you need to display it.

SELECT "WO" & ID_base AS ID
FROM YourTable;

Is this for an Access application which will always be used by no more than one user at a time?

See if you can use the techniques mentioned in this recipe from The Access Cookbook:
Recipe 6.7 Create and Use Flexible AutoNumber Fields (http://etutorials.org/Microsoft+Products/access/Chapter+6.+Data/Recipe+6.7+Create+and+Use+Flexible+AutoNumber+Fields/)

[HiTechCoach]

I agree that the WO probably should not be stored with the data. It is like using () around the area code of a phone number. Just a formatting thing.

This is one of those times where what the user see and what is actually stored is not the same thing. The


I need a string for a ID example "WO" & year(date) & month(date) & an auto numbered consecutive non duplicate integer beginning at 1 at the start of each month.


The Best Practice (data normalization) of database design/programming you do not store calculated values. You calculate the data as needed and show it to the user. To the user it appears as a stored value. What the user see and what is stored does not have to be the same thing.

At the most you would store a data and the sequence number for the month. This will allow you to have a permanent WO number.


SELECT "WO" & Format([WODate], "yyyymm") & Format([WOMthSeqNum], "00000") As WO_Number
FROM tblWorkOrders;



This also makes getting eh next Sequence Number very easy.

If you wanted to automatically calculate the next sequence number for a new record you could use this :


Private Sub Form_Current()

If Me.NewRercord Then

' set the WO Date
Me.WODate = date()
' calculate the seq number
Me.WOMthSeqNum = Nz(DMax("[WOMthSeqNum ]", "[tblWorkOrders]", "Format([WODate],""yyyymm"") = " & Format(Me.WODate, "yyyymm")), 0) + 1
End If

End Sub


You would then have a Text box on the form to show the calculated WO number by setting her control source to be:

="WO" & Format(Me.WODate, "yyyymm") & Format([WOMthSeqNum], "00000")

This assumes the seq number will always be 5 digits. 00001 to 99999.

[HiTechCoach]

Seeing this is your first post,

TamarackAero, Welcome to the VBA Express forums. :friends:

[hansup]

Greetings HiTechCoach.

How would you adapt that suggestion to allow 2 or more users to insert WO records at the same time? Seems to me that approach could lead to multiple users attempting to insert the same WOMthSeqNum value.

Is the answer to create a unique index on (the equivalent of) YYYYMM plus WOMthSeqNum, and trap the key violation ... then retry with a different WOMthSeqNum value?

[HiTechCoach]

Greetings HiTechCoach.

How would you adapt that suggestion to allow 2 or more users to insert WO records at the same time? Seems to me that approach could lead to multiple users attempting to insert the same WOMthSeqNum value.

Is the answer to create a unique index on (the equivalent of) YYYYMM plus WOMthSeqNum, and trap the key violation ... then retry with a different WOMthSeqNum value?

Yes.

What ever method you use should always include good error handling to trap duplicates and handle it.

[hansup]

OK. Additionally, I think moving the "calculate the sequence number" step from Form Current to Form BeforeInsert could limit the potential for conflicts in a multi-user application.

[HiTechCoach]

Yes, the before insert is a good place. This works well if the use does not need to see the value until after the record is saved.

If using he On Current event you probably should immediately save the record after assigning the next sequence number.

I have specialized in Accounting systems for 30+ years. In the modern computer age I rarely see this type of numbering system used. This was done before computers were used. The first migration to computers carried this numbering system forward. Today this type of numbering system really is not needed and is only possible helpful for a few humans. It does not help the software since the primary key is a system assigned number anyway. Usually a sequential auto-number. Besides the computer can easily find what you need. Most people search by name or date. It can easily calculate any stats you may need.