Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2013 Autonumber

Posted on 2014-10-06
9
Medium Priority
?
203 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 58
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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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 39

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 58
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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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