• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 818
  • Last Modified:

Creating an Account Number Generator in Access 2007

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
ergenbgr
Asked:
ergenbgr
  • 4
  • 3
1 Solution
 
rockiroadsCommented:
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
 
ergenbgrAuthor Commented:
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
 
rockiroadsCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ergenbgrAuthor Commented:
Thanks rockiroads,

Yes I would like to have the code.
0
 
rockiroadsCommented:
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
 
ergenbgrAuthor Commented:
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
 
rockiroadsCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now