Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating an Account Number Generator in Access 2007

Posted on 2010-08-30
7
Medium Priority
?
817 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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