Way for making a monthly unique auto increment reference number

[atzdgreat]

hi i am creating an apps that will save a data with auto increment monthly. I dont want to use the autonumber since every month my refnum will reset to 1. this is the format of my reference number.


AB-1911XXX, AAB-1911XXX
AB/AAB is the category
19 is the year
11 is the month
XXX is the auto increment.

so if december 1 came. the first user who create a new entry will having a refnum of AB-1912001/AAB-1912001

in my dbase, i have the columns:

ref_yrmonth <--- this will store the data AB-1911 or AAB-1911 the purpose of this is for counting
refnum <---- and here is the complete reference number like AB-19110001/AAB-1911001

my problem is, when i test to both pc and make a new entry at the same time, there are times that it will give same reference number. i think there is something wrong with my code

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim DbName as String
Dim NewRefCtr as Integer
Dim RefNumToSrch as String

RefNumToSrch = "AB-" & format(Date,"YYMM")

DbName = "Location\a.accdb"
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM MyTable WHERE ref_yrmonth='"& RefNumToSrch &"'" )

   With rs
         If .RecordCount = 0  Then
             NewRefCtr = 1
         Else
            .MoveLast
             NewRefCtr = .RecordCount + 1
        EndIf

        .Addnew
        !ref_yrmonth = RefNumToSrch
        !refnum = RefNumToSrch & Format(NewRefCtr,"000")
        .Update
   End With

[Micron]

What event calls this code? You are describing a situation that exists where a value is created at the beginning of a process. That means the current value (not saved) for user1 becomes the same for when user2 begins and user1 has not committed the record already. If you save the num when user1 begins, then user1 cancels, there are gaps in the sequence if this cancellation removes user1's num value. That may or may not be important. If user2 tries to save and user1 has already saved, there are conflict errors if the values cannot be the equal. Same goes for user1 if user2 saves first.

To avoid all this mess, it is best to create the value in the form's BeforeUpdate event. If this value must be seen by the user during the process then I don't have an alternative suggestion at the moment.

I only did a quick review of the code and it seems you are basing part of this value construct based on the number of records in the table. Don't think I would do that. However, I'll leave those thoughts aside as the more important thing at present is about when you're doing what you're doing.

[atzdgreat]

actually im doing this at excel vba. what would be the best thing to do?

[Micron]

You posted in the programming section for Access, but you are coding in Excel? I will have to bow out. You will probably get more response in an Excel forum.

[atzdgreat]

im sorry for this.

[ArviLaanemets]

In case you work in Excel, you don't need VBA at all.

At first, from your post follows, that you have separate numeration for every category in every month.

1. Into your table, add a column (e.g. RefNumPart - may be hidden), where number part of reference will be calculated [=1*MID(RefNum,8,4)]
2. Create a table on hidden sheet with Categories list as Leftmost column (e.g Category);
3. Into next column (e.g CatYYMM), calculate value [=Category & "-" & TEXT(TODAY(),"yymm")]
4. Into next column (e.g. NextNumPart), calculate MAX value in RfNumPart in your table where ref_yrmonth = CatYYMM, add 1 to it and wrap the result in TEXT function [TEXT(result,"0000")];
5. Into next column (e.g. NextRef), calculate next reference number for current month and category [=CatYYMM & NextNumPart]
6. For your table,create a dynamic range (e.g. lRefNo), which refers to NextNumPart on hidden sheet in row with same category and month number as active row in your table;
7. Create a Validation List for column refnum with formula [=lRfNo].

Whenever you activate validation list in refnum column, when the month number in ref_yrmonth column for active row is for current month, next available number for this category is only possible option. Otherwise the list will be empty.