Solved

Access 2013 Autonumber

Posted on 2014-10-06
9
195 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
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 36

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

685 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