Solved

Creating an Autonumber with a Prefix for different categories while including the Year and Month

Posted on 2006-07-23
5
652 Views
Last Modified: 2008-02-01
I've found possible solutions for problems similar to mine, but not enough to get me where I need to go. What I am looking for is an Incrementing Autonumber including the Month, Year, and a Letter prefix at the beginning of the Autonumber based on the users choice.

For example: A06-05-001, A06-05-002....A06-05-018, A06-05-019
          B06-05-001, B06-05-002....B06-05-025, B06-05-026
          C06-05-001, C06-05-002....C06-05-056, C06-05-057

"A" = Apples (Category), "B" Bananas (Category), "C" Cherries (Category)
"06" = Current year
"05" = Current month
"001" = Counter incrementing for each category

I want to be able to have the user choose a category from a drop down list in a form so it can apply a prefix to the Autonumber including the year and month. I will need to have each category have its own increment so that if a user chooses category "C", the counter will automatically check the last number used for Category "C" and not the others, and apply the next sequential number. The tricky part for this is I am trying to have the counters reset every month for all categories.

I don’t mind using VBA if it will accomplish this task. Hope this makes sense. Thanks in advance for your help.

0
Comment
Question by:masterimi
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17164302
Suggest you use 3 fields

Category
CurrDate
Counter

or make use of one field with delimiters Category-CurrDate-Counter


say u had the 3 field part, to check the max id, u can simply do a DMAX
e.g.

DMAX("Counter","mytable","Category = '" & somecat & "' AND CurrDate = " & format(somedate,"YYMM"))


basically when u store records, u store category as string field "A","B" etc
and date field as date entered by user but formatted
format(somedate,"YYMM")

so year is first

Now when u run max, it gives u the highest counter value. You can then increment to get the next value

If u use the one field approach, u would need to make use of string manipulation functins like LEFT and MID
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17164378
If u had it as one field - format like u said    B06-05-001
field in table was called ProdKey

say you had two fields on your form

Category (A,B,C etc)

DateFld (if not present, we could just use Now() - this returns current date, but lets assume a date field is present)


'Lets create a function to get the next counter and one to build one

public function GetNextCounter(byval sCategory as String, byval sDate as string) as long

    Dim sWhere as string


    GetNextCounter = -1         'indicates failure

 B06-05-001,
    sField = "Left(ProdKey,1) = '" & sCategory & "' AND Mid(ProdKey,2,5) = '" & format(year(sDate),"00") & "-" & format(Month(sDate),"00") & "'"

    GetNextCounter = DMAX("Right(ProdKey,3)","tblProdKeys",sWhere)

    If GetNextCounter > 0 then GetNextCounter = GetNextCounter + 1 else GetNextCounter = -1
end function

public function BuildCounter(byval sCategory as String, byval sDate as String, optional byval lCounter as integer = -1) as String

    'If counter is -1 then we have to do the hard work

    dim lNextCounter as integer

    if lCounter = -1 then lNextCounter = GetNextCounter(sCategory, sDate) else lNextCounter = lCounter

    BuildCounter = sCategory & format(year(sDate),"00") & "-" & format(month(sDate),"00") & "-" & format(lNextCounter,"000")
end function

0
 

Author Comment

by:masterimi
ID: 17164399
Thank you for the quick responses rockiroads. I will try your suggestions and get back ASAP. I haven't used VBA in a while so I'll get on it quickly and see if it works. Will these counters reset each month also? Thanks.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17165612
Counters dont reset, but in theory they should do

if we check for specific month and year, and it doesnt find anything then counter should become 1
0
 

Author Comment

by:masterimi
ID: 17172146
I tried to duplicate the suggestions you made rockiroads but I ran into a few problems on my part. I created a small DB based on what I have gathered so far including your comments and other resources. I am getting close but still running into some problems.

Table named "Codes":
Field: Code_Desc
Field: Last_Nbr_Assigned

Table named "Events":
Field: Seq_Number
Field: Item_Type
Field: Event_Description

Form:
 Controls:
   -Sequential # Text Box
   -Item Type Combo Box
   -Description Text Box

The item type combo box has the list of items such as Apples, Bananas, and Cherries. When the user chooses Apples, the locked Sequential #Text Box automatically generates the correct number such as A06-07-001 (on Event: Lost Focus). The problem I have now is the counter is not going up. This is what I have so far in VB.

Form_frmEvents

Private Sub Item_Type_LostFocus()

    'Assign sequential number
    If IsNull(Seq_Number) = True Then
        'An Item_Type must be selected
        If IsNull(Item_Type.Value) = False Then
            Seq_Number = NewSeqNumber(Item_Type.Value)
        End If
    End If
   
End Sub

Module1

Option Compare Database

Function NewSeqNumber(pItem_Type) As String

    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim Lrs            As DAO.Recordset
    Dim LSeqNumber     As String
    Dim LYear          As String
    Dim LMonth         As String
       
    On Error GoTo Err_Execute
   
    Set db = CurrentDb()
   
    'Retrieve last 2 digits of current year
    LYear = Mid(CStr(Year(Date)), 3, 2)
   
    'Retrieve 2 digits of current month
    LMonth = Format(Date, "mm")
   
    'Retrieve last number assigned for item_type/year/month combination
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = LSQL & " where Code_Desc = '" & pItem_Type & LYear & LMonth & "'"
   
    Set Lrs = db.OpenRecordset(LSQL)
   
    'If no records were found, create a new item_type/year/month combination in
    'the Codes table and set initial value to 1
    If Lrs.EOF = True Then
   
        LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pItem_Type & LYear & "-" & LMonth & "', 1)"
       
        db.Execute LInsert, dbFailOnError
       
        'New sequential number is formatted as "C05-07-001", for example
        LSeqNumber = pItem_Type & LYear & "-" & LMonth & "-" & Format(1, "000")
       
    Else
        'Determine new sequential number
        'New sequential number is formatted as "C05-07-001", for example
        LSeqNumber = pItem_Type & LYear & "-" & LMonth & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "000")
       
        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & Format(Lrs("Last_Nbr_Assigned") + 1, "000")
        LUpdate = LUpdate & " where Code_Desc = '" & pItem_Type & LYear & "-" & LMonth & "-" & "'"
       
        db.Execute LUpdate, dbFailOnError
       
    End If
   
    Lrs.Close
    Set Lrs = Nothing
    Set db = Nothing
   
    NewSeqNumber = LSeqNumber
   
    Exit Function
   
Err_Execute:
    'An error occurred, return blank string
    NewSeqNumber = ""
    MsgBox "An error occurred while trying to determine the next sequential number to assign."
   
End Function

It doesn’t seem to be tracking the counter in the Codes table in field Code_Desc. It only shows A06-07 and not the three digit counter. So when I go to created the next sequential number, I get a Run-time error saying “Field ‘Events.Seq_Number’ cannot be a zero length string. Thanks all.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now