Solved

Access 2013 Autonumber

Posted on 2014-10-06
9
182 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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)
Comment Utility
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)
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
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

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

12 Experts available now in Live!

Get 1:1 Help Now