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
Solved

Creating an Account Number Generator in Access 2007

Posted on 2010-08-30
7
812 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

809 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