Solved

Creating an Account Number Generator in Access 2007

Posted on 2010-08-30
7
813 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

697 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