Solved

Creating an Account Number Generator in Access 2007

Posted on 2010-08-30
7
806 Views
Last Modified: 2013-11-28
I have searched the site and reviewed the prior problems and solutions regarding the creation of an incremental account number. The problem may be syntactical but I cannot seem to ge the code to work. I attached a 1 table, 1 form, "test" database that illustrates my problem.

What I expect to happen: When the user creates a new record, the [fldAcct#] value is 0. After the user enters the Name (f,m,l) and hits enter to go to the next record, I want the code to query the field [fldAcct#] in [tblName] for the MAX value and increment that number by one and populate the textbox (Text1) with the new number and then lock the textbox so it cannot be changed. At that point I expect the textbox value to be stored in [fldAcct#] in "tblName".

One point of confusion is how to define the elements of DMAX as I have seen it three different ways, DMAX([fldAcct#], "tblName") and DMAX("[fldAcct#]", "tblName") and DBMAX([fldAcct#], [tblName])

This seems so basic but I have been unsuccessful in many trys to resolve. TestAcctNum.accdb

Thanks
0
Comment
Question by:ergenbgr
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33559472
is this going to be a multiuser app? by this I mean are you expecting two processes (can be users) accessing the table at the same time?

if not then easiest solution might be to use the form beforeupdate function and you want to make sure its on the add
this will do what you want, add a new id after you move to the next record

eg

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.Text1 = NZ(DMax("[fldAcct#]", "tblName"),"0") + 1
    End If
End Sub

thi



0
 

Author Comment

by:ergenbgr
ID: 33559756
It will be a multiuser application and a potential for more than one user accessing the table at the same time. Is there a way to lock the table when a another user has begun to create a new record?

I did figure out why my original code was not working. I was using the "Before Update" event on the field rather than the form.
Thanks,
Your solution offered the results I was looking for so I will award points to you and post another question to see if there is a way to lock a table once it is in use.
Ray
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33560123
there is another way which is why I asked. way I do it is to open the table and lock. if it fails to lock then I wait and try again. You still want the code?
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

 

Author Comment

by:ergenbgr
ID: 33560285
Thanks rockiroads,

Yes I would like to have the code.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33560306
I assume you do not want to use a autonumber which is why you are going down this approach. An autonumber handles all the issues you will face.

Without an autonumber, Create a new table, say called IDTable. It has one field called NextNum. This is used to generate the id's. What we do is to lock the table, increment, update and release.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.Text1 = GetNextID
    End If
End Sub


Public Function GetNextID() As Long

    Dim rs As dao.Recordset
    Dim iCnt As Integer
   
    GetNextID = -1
   
    iCnt = 0
    On Error GoTo SodThis
   
    Set rs = CurrentDb.OpenRecordset("IDTable", dbOpenTable, dbDenyWrite)
   
    If rs.EOF = True Then
        rs.AddNew
    Else
        rs.MoveLast
        rs.Edit
    End If
    rs!NextNum = Nz(rs!NextNum, 0) + 1
    GetNextID = rs!NextNum
    rs.Update

CloseDown:
    rs.Close
    Set rs = Nothing
   
    Exit Function
   
SodThis:
    If Err.Number = 3008 Then
        iCnt = iCnt + 1
        'try 10 times
        If iCnt = 10 Then
            GoTo CloseDown
        Else
            Resume 0
        End If
    Else
        MsgBox Err.Description
        GoTo CloseDown
    End If
End Function




0
 

Author Comment

by:ergenbgr
ID: 33567484
I would like to rely on auto-number but in access it seems that the number goes into a negative numbers and will not consistantly increment the number by 1 which makes the account number odd. It will even produce some duplicates if the conditions are right. Is there an update that I am not aware of that resolved this issue. The last time I researched the issue, ther work arounds were not very reliable either.

Thanks for the code.

Ray
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33571428
not sure about the negative numbers. Maybe it was a replicated db or seed was to start from negative. With regards to dodgy increments, if you add then delete a record, access doesnt reset.

So the only other way round is to define your own id. The way I defined it was to use another table to hold the id's. Then fetch and update that table handling locking at the same time.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

762 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

22 Experts available now in Live!

Get 1:1 Help Now