Results 1 to 15 of 15
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Additional Autonumbering

    I have three bank accounts, and a Petty Cash account. For those four, all receipts gets processed through one form f02receipts to the t02receipts table. Every receipt get the next autonumber RCT000001 and so on. In a field BankName I can select the account from the 4 first mentioned. I can print ledger accounts for each bank on its own. It al works well. Payments work on the same basis.

    I want following numbers for each of the 4 accounts as well. So maybe in the table t02Receipts the ID no RCT001000 is reached but in Bank account no 1 it is only receipt no 256.

    I believe I understand the process should normally work from the other side. My thinking is then that 4 bank accounts may need 8 tables to get the autonumbering right. 4 for receipts, and 4 for payments. I know how to do that and it should result in what I want.

    Does that mean when a new Bank Account is added it will need new tables? That is not what I want. My hard thinking fall short. My whole apllication should work in a way that when a user adds a new Bank account, no new tables should be needed, but each bank account should have numbering that follows on the previous number.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Generating custom unique identifier is a common topic. Requires code. Review https://www.accessforums.net/showthread.php?t=23329
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks June7. If the following is the situation. Receipt Table. I will try. My thinking is that the New numbers have to be in one field, because if new bank account gets added. I looked at the link you suggest, but I am too much of a rookie. The exact code will surely help.

    ReceiptID Bankaccount NewNumbers
    RCT000001 Bnk1 1
    RCT000002 Bnk3 1
    RCT000003 Bnk3 2
    RCT000004 Bnk3 3
    RCT000005 Bnk2 1
    RCT000006 Bnk1 2
    RCT000007 Bnk1 3
    RCT000008 Bnk1 4

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code is not really difficult. A DMax() could accomplish for your example.

    Nz(DMax("NewNumbers", "Receipts", "BankAccount=" & Me.combobox),0) + 1

    That expression assumes you are saving a number ID as foreign key for the bank, and not the full name of bank and the ID is in combobox RowSource.

    The real trick is figuring out what event to put code into. At what point do you want to generate the ID? Perhaps not until the record is ready to commit to table? Record is committed when 1) close table/query/form or 2) move to another record or 3) run code to save.

    In my example the ID has to be generated early because user must see it and write it on a submittal form.

    Otherwise, can generate this sequence dynamically using report design. Textbox on report has a RunningSum property that can calculate the sequence. Set a grouping on the bank ID and RunningSum over group.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I'm trying the expression right now. Sorry if I confuse you but I am doing it on the payment side of things, not receipt, its the same. Not coming right yet. I don't need the number immediately, so when form close is fine. In your expression; am I understanding correct.
    a. The field where the new numbers are named "NewNumbers" I put the expression there in the query, a calculated field in query q02Receipt. I named it Bnk01Pmt.
    b. t02Payment is the table that stores everything about payments.
    c. As you said Bankaccount keeps foreign key for bank, my fieldname CmbEnt_ID08, this field in t02Receipt and q02Receipt.
    d.I assume I put the expression in a new text field on the form f02Receipt. The combobox which refer to rowsource for banks, the text box name is T003.

    Should my expression in the textbox that is bounded to Bnk01Pmt be

    Nz(DMax("Bnk01Pmt", "t01Payment", "CmbEnt_ID08=" & Me.T003),0) + 1

    T003 is bounded to field
    CmbEnt_ID08.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, code (macro or VBA) is required to save calculated data to a natural (not calculated) field. The expression would have to be referenced directly in some event code to save the value to the table field.

    Combobox to select bank is named CmbEnt_ID08?

    What is name of field that has the bank ID?

    Perhaps in the form BeforeUpdate event, VBA code like:

    Sub Form_BeforeUpdate(Cancel as Integer)
    Me!NewNumber = Nz(DMax("NewNumber", "t02Payment", "BankID=" & CmbEnt_ID08),0) + 1
    End Sub



    Is this db multi-user? Is it split design?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The following is example code to get you thinking:

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim vLast As Variant
        Dim iNext As Integer
    
        vLast = DMax("[CaseNum]", "[tblCase]", "[CaseNum] LIKE '" & Format([txtDateCreated], "yyyy\*\'"))
        If IsNull(vLast) Then
            iNext = 1
        Else
            iNext = Val(Mid(vLast, 5)) + 1
        End If
        Me![txtCaseNum] = Format([txtDateCreated], "yyyy") & "-" & Format(iNext, "00000")
    End Sub
    Increments by one based on last number (00001) for each year: 2018-00001, 2018-00002, 2018-00003
    When the year changes the numbering starts over at 00001.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Yes it is split design, works on network, but not setup for internet. I am working on a separate copy that is not split but a brand new copy is loaded on Dropbox. I believe if you give me an email address, I can invite you. Never used dropbox, how do I get you to connect. My emailaddres jdvn63@gmail.com

    I don't know what I am missing. There is only one field referring to bank accounts CmbEnt_ID08. The combobox.

  9. #9
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The following builds on the last where files are numbered based on case number:

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim Prefix As String
        Dim vLast As Variant
        Dim iNext As Integer
        If Not IsNull([Forms]![frmViewCase]![txtCaseNum]) Then
            Me![txtCaseNum] = [Forms]![frmViewCase]![txtCaseNum]
        Else
            Exit Sub
        End If
        
        Me![txtDateCreated] = Date
    
        Prefix = "-CFN-"
        vLast = DMax("[CaseFileNum]", "[tblCaseFile]", "[CaseFileNum] LIKE '" & Me.txtCaseNum & Prefix & "*'")
        If IsNull(vLast) Then
            iNext = 1
        Else
            iNext = Val(Right(vLast, 5)) + 1
        End If
        Me![txtCaseFileNum] = [txtCaseNum] & Prefix & Format(iNext, "00000")
    End Sub
    Results with: 2018-00001-CFN-00001

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not posting my email address and neither should you. Forum IM is for private conversations.

    Don't see how can be more explicit in coding examples. Adapt as needed for your design. Post your attempted code.

    Follow instructions at bottom of my post to attach files to post.

    I use Box.com to share files too large for forum.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi SJ. If you answering me, thanks. I don't want numbers to restart after a year. I hope my explanation does the job.Attachment 32499

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I pulled the BankID into my query, and put the code in exactly as you suggest and it works. Thanks.

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me!CashBookNum01 = DMax("[CashBookNum01]", "q02Payment", "[CmbEnt_ID08]=" & [CmbEntID]) + 1
    End Sub

    You gave me this expression and I am excited about it since I can use it elsewhere as well. There is still a challenge. If I copy the database to start new sets of books, this expression wouldn’t start at no 1, probably because it find no record or a zero. How do I get it to start with no 1?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Perfac View Post
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me!CashBookNum01 = DMax("[CashBookNum01]", "q02Payment", "[CmbEnt_ID08]=" & [CmbEntID]) + 1
    End Sub

    You gave me this expression and I am excited about it since I can use it elsewhere as well. There is still a challenge. If I copy the database to start new sets of books, this expression wouldn’t start at no 1, probably because it find no record or a zero. How do I get it to start with no 1?
    If you want a new 'empty' database after copying all objects, empty the data from each table & then compact the database.
    As well as reducing file size, it means autonumbering will then start at 1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    It is all contained in my previous posts about numbering.

    hint:
    If IsNull(vLast) Then
    iNext = 1
    Else
    iNext = Val(Right(vLast, 5)) + 1
    End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SOS.... AutoNumbering Issue
    By Tammy in forum Access
    Replies: 6
    Last Post: 03-04-2015, 01:13 PM
  2. Custom Autonumbering
    By mguardab in forum Access
    Replies: 1
    Last Post: 05-22-2014, 12:02 PM
  3. Replies: 2
    Last Post: 06-28-2013, 01:27 PM
  4. autonumbering.
    By puzzlednewbie in forum Access
    Replies: 3
    Last Post: 09-24-2012, 11:33 AM
  5. Autonumbering when a checkbox is clicked?
    By kutehart in forum Forms
    Replies: 1
    Last Post: 08-02-2010, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums