Solved

Access 2013 Autonumber

Posted on 2014-10-06
9
196 Views
Last Modified: 2014-12-06
I am working on a project where I need to generate unique tag numbers for incoming shipments.  I have a customer code for example "GR"  I have a month in number format.  Then a 3 to 4 digit number at the end which needs to reset to 001 at the beginning of each month.  I can create the Customer code and get the month easy enough but I can't figure out how to create the number field, have it increment and then reset to 001 at the beginning of each month.
0
Comment
Question by:Mwvarner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 57
ID: 40364632
<<but I can't figure out how to create the number field, have it increment and then reset to 001 at the beginning of each month. >>

There are three approaches:

1. Determine based on customer and month, what the last value assigned was based on current records.

2. Keep a counter for each customer along with the year and month in a  table (one record per Customer/year/Month combination).   If no record exists, you add one and would be starting at one.

3. Create a process where a lock is placed not on the main table, but a "resource lock" of generating a key for a specific customer.  So you place a lock for the resource of "I want to generate a key for customer XYZ", you look at the existing records, generate the key, then release the resource lock.

Each approach has pro's and con's.  #1 is tough in a multi-user situation because you need to lock the table to get an accurate value.  But if your process of assigning numbers is always single-user, then it's the best.   But it has the downside that if the last record is deleted, then the same key might get assigned 2x.

#2 might be a problem depending on how many customers you have, but is better because you don't lock your entire main table. The down side is your depending on stored data for the key values and not what is currently in the table.  If anything goes wrong with the process, you might get dups or skip a bunch.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40364633
This would need to be a regular integer or long integer value.

I usually store these numbers in a table (tbl_Counters).  Normally, this table only has two fields, CounterType (string) and CounterNum(long).  To get the next counter, I would call the function like:

lngNextCounter = fnNextCounter("Incoming")

and the function would look something like:

Public Function fnNextCounter(CtrType As String) As Long

    Dim strsql As String
    Dim rs As DAO.Recordset
    
    strsql = "SELECT CounterNum from tbl_Counters WHERE CounterType = '" & CtrType & "'"
    Set rs = CurrentDb.OpenRecordset(strsql, , dbFailOnError)
    
    If rs.EOF Then
        rs.AddNew
        rs!CounterType = CtrType
        rs!CounterNum = 1
    Else
        rs.Edit
        rs!CounterNum = rs!CounterNum + 1
    End If
    rs.Update
    rs.Bookmark = rs.LastModified
    fnNextCounter = rs!CounterNum
    
    rs.Close
    Set rs = Nothing
    
End Function

Open in new window

It is important in a multi-user environment to implement a procedure that will not allow multiple users to generate the same number.  Many people try to use NZ(DMAX("someField", "SomeTable"), 0) + 1 as the control source or in the Current event of a new record to generate this number.  This technique does not work because it provides another user to generate the same number if they attempt to do so before the first users record is actually written.  This technique can be used anywhere in the process, because it will prevents two successive people from getting the same number.

In your case, you are going to have to track when this number was last updated.  To do that, I would probably add an optional parameter to the function and a date field (dtLastReset) to the table.  Then, you would modify the code something like:
Public Function fnNextCounter(CtrType As String, Optional ResetFreq As String = "") As Long

    Dim strsql As String
    Dim rs As DAO.Recordset
    
    strsql = "SELECT * from tbl_Counters WHERE CounterType = '" & CtrType & "'"
    Set rs = CurrentDb.OpenRecordset(strsql, , dbFailOnError)
    
    If rs.EOF Then
        rs.AddNew
        rs!CounterType = CtrType
        rs!CounterNum = 1
        rs!dtLastReset = Now()
    Else
        rs.Edit
        If ResetFreq = "" Then
            rs!CounterNum = rs!CounterNum + 1
        ElseIf Format(rs!dtLastReset, ResetFreq) = Format(Now(), ResetFreq) Then
            rs!CounterNum = rs!CounterNum + 1
        Else
            rs!CounterNum = 1
            rs!dtLastReset = Now()
        End If
    End If
    rs.Update
    rs.Bookmark = rs.LastModified
    fnNextCounter = rs!CounterNum
    
    rs.Close
    Set rs = Nothing
    
End Function

Open in new window

Then, to get this particular counter, you would call this function like:

lngNexCounter = fnNextCounter("Incoming", "yymm"")

This will reset the CounterNum at whatever interval you select.  You can use any valid date format ("yyyy", "yymm", "yymmdd", "mmdd", "dd",... ) as the ResetFreq string; I tested it with "hhmm" to see whether it would create a new counter each minute, and it does.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40364638
BTW, If I was going to use the 2nd option above, I'd add some error checking code to make sure that the ResetFreq I used matches a set of acceptable values.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:PatHartman
ID: 40364939
Regardless of what you decide to do for this user-friendly number, your primary key should be an autonumber and the autonumber should be what is used to enforce RI.  The user-friendly number should have a unique index to prevent duplicates but otherwise only be used for lookups and to identify printed documents.

I use the DMax() technique that Dale doesn't like but, I generate the number as the LAST thing I do before the record gets saved.  That minimizes the potential for duplicates.  If you generate it immediately when the user dirties the record, you run a serious risk of a faster typist generating the same number and saving it first.  You also need to add code to trap duplicate errors that could be raised when the record is inserted and generate the ID again.  You need to put this into a loop with a limited span.  5 tries is more than sufficient.  If you try 5 times and still can't get a unique number, stop and report back to the user.

Since it is possible to generate the same ID multiple times if someone deletes the last record, you should prevent deletes from the table.  Add a "delete" flag and use that in all your queries to avoid the deleted records except for certain processes which are designed to show them.  You don't want someone to enter a record, create some paperwork, print it, etc and then delete the record because the next record they add will be assigned the same sequence number.  This is less of an issue if you are using traditional autonumbers because Access won't generate the same number again unless you go through a lot of trouble to make it happen.
0
 
LVL 57
ID: 40365688
You can use DMax() even in a multi-user situation, but you need a re-try loop on it.   Years ago, I used it and saw first hand that with more than a half dozen or so users, duplicate keys would get generated.   It just takes too long to execute the DMax (especially on a large table).

 I basically went to method #2 when I want to generate unique keys.

Jim.
0
 

Author Comment

by:Mwvarner
ID: 40371254
I'm sorry guys but I've built several databases over the years but I'm not that well educated in VBA.  I'm a visual sort of guy so if either of you have a basic sample I could see it would help me tremendously.

Marshall
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40458409
As everyone has noted, this can be a tough requirement.
In this case, it is especially so because you are storing a string, and in a string this order prevails
0001, 0010, 0011, 0012 ... 0019, 0002, 0020 ...

I need to generate unique tag numbers for incoming shipments
So I take it that a form is in play.
Good.
So you can start with a query (qryMaxNumber).
Your query is going to snag all the records from the table where the customer matches the form's customer, and the month matches the form's month.
As another field in the query, your going to have
NewTagNumber:CInt(Right([TheCustomerTagField],4))
That'll shear off the last 4 characters of the tag -- which are the number -- and coerce them to an Integer
You're going to sort your query by NewTagNumber DESC

With that done, when your form goes to commit a record, you'll need to call some code

Dim db as database
Dim rs as recordset
Dim qdf as querydef
dim prm as parameter

set db = CurrentDb
set qdf = db.QueryDefs("qryMaxNumber")

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

At this point rs!NewTagNumber will be your largest number for the client
You'll need to add one, and then pad out any additional zeroes and build your whole tag string.

After you've built the string, you then are going to want to test it for uniqueness

set rs = db.OpenRecordset("select TheCustomerTag from TheTable where TheCustomerTag = " & chr(34) & YourNewTag & chr(34) dbOpenDynaset, dbSeeChanges)

if rs.recordcount <. 0 then 'crap!
    goto RestartTagCaluclation
end if
' at this point we're golden, add the record

That's my 2 Cents
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40458424
Oh,
and then reset to 001 at the beginning of each month.
After you open the recordset
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

if you test for rs.recordcount = 0 and it is true, then you haven't entered a tag this month, and the number part will be 0001
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40458433
Then of course, we could do dumbass brute force wastefulness

Dim GottaGooder as Boolean
Dim x as integer
dim db as database
dim rs as recordset
dim TheTag as string

set db = Currentdb
GottaGooder = false
do until GottaGooder =  true
    for x = 1 to 9999
        'create the string using x
         set rs = db.OpenRecordset("select TheCustomerTag from TheTable where TheCustomerTag = " & chr(34) & YourNewTag & chr(34) dbOpenDynaset, dbSeeChanges)
         if rs.recordcount = 0 then 'hooray!
             GottaGooder = true
        end if
   next x
loop

Open in new window


It would work, but the bigger the number, the more wasteful such a code block would be
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

734 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