Results 1 to 14 of 14
  1. #1
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23

    Talking Increment field based on combo box

    Hi, I've done a lot of searching and I believe what I want to do is possible but I am not sure how to do it and nothing I've found has helped me with the how.



    I am setting up a database that will create overhaul reports for a product. The product when manufactured was done in batches and therefore have batch numbers (eg, AA, AB, AC etc) however when we overhaul the product later down the track we need to give it a specific number to identify it, therefore we mark it up to be AA-01, AA-02 etc.

    I wanted to see if I can have the batch number (AA, AB, AC etc) as a combo box so the tester chooses which batch the product was from and then have Access automatically pick the next number in the sequence of that batch.

    So I could have:

    AA-01
    AD-01
    AA-02
    AA-03
    AB-01
    AB-02

    Is this possible and if so how do I do it? I have some knowledge of VBA but I am not good at deciphering what code means what. Or what information I would need to change from someone elses code to make mine work. Any help would be greatly appreciated.

    Cheers

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Whenever auto-increasing a value, you have to consider when to do this and how, based on the process. If I choose a value and we increase the suffix by 1, then you start a record, we now have AA-n+1 and AA-n+1+1 on the go. I cancel, you finish. Now we have AA-01, AA-02, AA-04 because I was creating AA-03. There is no AA-03. So does that matter?

    If you increment only when the record creation is committed then you avoid this problem. However, if you need to be able to save the record (say, for completion later) then the record has to be committed, but what about the increment? Do later or now? This might seem pointless, but in one of my cases, the form had to be completed in order to get a number, then the final record had to be locked in later.

    Regardless of which seems best to you, I would put the batches and what I suspect is a bunch of related data in one table, and the revisions/overhauls in another. This way, you don't dupe all the batch info. It would be easy enough to increment the last overhaul number by 1 for a group (AA or BB or whatever). When you write the new record, the suffix field value would be the Max() of the batch field +1 where Batch = AA. You could determine the max value by using DLookup, but I think you need to consider when to implement the increase and if that decision doesn't produce any issues referred to above.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2015
    Posts
    23
    In this particular database there would only be one person entering data at one time, as it is specific to a section of the business and it's the supervisors job to do these reports, so there would be no real chance of you and me entering data at the same time.

    The increment, I think, really needs to be done at the beginning of entering the data, as sometimes we need to know the number to do labels etc, and then complete the database record later.

    I was thinking I could have one table with all the data (cause there really isnt that much data required) and then do a LookUp for the Batch. Once the Batch is chosen when the "Number" would increment.

    I have my databases so that everyone has a specific login and only me as the Admin can actually view the tables and queries. Therefore it would only ever be in the form that the Batch is chosen and the "Number" incremented...if that makes sense

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The increment, I think, really needs to be done at the beginning of entering the data, as sometimes we need to know the number to do labels etc, and then complete the database record later.
    Then I think you will need both a create and edit process. You also don't want the number to be editable, nor do you want the edit process to create a new number. Check out the OpenArgs method of the DoCmd object so that you can use one form for both. User action dictates which process is being invoked. I have done this by passing "new" or "edit" to the args parameter and setting up the form accordingly.

    Well, you may have information I"m not privy to so I'm not going to push the multi table approach further. Good luck with your db.

  5. #5
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2015
    Posts
    23
    Quote Originally Posted by Micron View Post
    Then I think you will need both a create and edit process. You also don't want the number to be editable, nor do you want the edit process to create a new number. Check out the OpenArgs method of the DoCmd object so that you can use one form for both. User action dictates which process is being invoked. I have done this by passing "new" or "edit" to the args parameter and setting up the form accordingly.

    Well, you may have information I"m not privy to so I'm not going to push the multi table approach further. Good luck with your db.

    Hey,
    I am not great with databases....I know what I know and the rest kinda confuses me lol. So I have no idea what or where the OpenArgs is.

    What would be your multiple table approach? I've done a lot of googling and nothing is helping me so I am willing to try anything to get this thing to work. Thanks

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is a bit old now; will have to review what it was about.
    So I have no idea what or where the OpenArgs is.
    In the meantime, try this

    https://www.google.ca/search?num=100...20.wU4Mg_d0Z0k

    don't know what you use to search, but this Google search took about 10 seconds to come up with and gave me about 72 results (the search term was kinda specific). See if the results hold any promise for you.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm not entirely clear on exactly what your scheme is but I wrote a function recently that should help you.

    This is a generic function that takes an input string and increments the RIGHT MOST group of alphanumeric characters by one character. An alphanumeric character being A through Z, a through z, and 0 through 9. I wrote this function to function similiar to the String.succ or String.next method in the ruby scripting language.

    Here's ruby's description:

    Returns the successor to str. The successor is calculated by incrementing characters starting from the rightmost alphanumeric (or the rightmost character if there are no alphanumerics) in the string. Incrementing a digit always results in another digit, and incrementing a letter results in another letter of the same case... If the increment generates a “carry,'' the character to the left of it is incremented. This process repeats until there is no carry, adding an additional character if necessary.
    Code:
    "abcd".succ        #=> "abce"
    "THX1138".succ     #=> "THX1139"
    "<<koala>>".succ   #=> "<<koalb>>"
    "1999zzz".succ     #=> "2000aaa"
    "ZZZ9999".succ     #=> "AAAA0000"
    "***".succ         #=> "**+"
    https://ruby-doc.org/core-2.4.2/String.html#method-i-succ

    Here's my vba port:
    Code:
    'succ increments an alphanumeric string, returns the (succ)essor to str
    '   this is my implementation of ruby's String.succ / String.next method
    '   it will only increment the right most group of alphanumeric characters
    '   it will add one character if necessary eg 2 char input "ZZ" becomes 3 chars "AAA"
    '   or "99" becomes "100".
    '   Numbers always increment to the next number, letters to the next letter.
    '   https://ruby-doc.org/core-2.3.1/String.html#method-i-next
    Public Function succ(ByVal str) As Variant
    On Error GoTo ErrHandler
        Dim str_len As Integer 'input string's length
        Dim i As Integer       'index as we loop through input string
        Dim b As Byte          'ascii byte value of character we're evaluating
        Dim b_prev As Byte     'ascii byte of last alphanumeric character evaluated
        Dim carry As Boolean   'does the loop need to keep going?
        
        str = " " & str 'insert space to trick for loop to behaving correctly when we have carry over after index = 1 and avoid extra error handling
        str_len = Len(str)
        b_prev = False
        carry = True
        
        For i = str_len To 1 Step -1    'Loop through the string backwards (right to left), i = current index of loop
            b = Asc(Mid(str, i, 1))     'b = the ascii value of the character at string index i
            
            Select Case b
                Case 65 To 90, 97 To 122, 48 To 57  'case if A to Z, a to z, or 0 to 1 (alphanumerics)
                    b = b + 1                       'we found a alphanumeric so increment it
                    
                    'If the increment needs to roll back then handle it. eg Z increments back to A
                    ' if there is a roll back (or carry over) then we will need to continue the for loop to the next char
                    Select Case b
                        Case 91
                            b = 65 '=A
                        Case 123
                            b = 97 '=a
                        Case 58
                            b = 48 '=0 (zero)
                        Case Else
                            'There was no carry over, we're already almost done
                            carry = False
                    End Select
                    
                    'replace the character at index i with the incremented value
                    Mid(str, i, 1) = Chr(b)
                    
                    'if there was no carry over then we're done, exit loop. otherwise repeat process until no more carry overs
                    If Not carry Then Exit For Else b_prev = b
                    
                Case Else
                    'either we havn't hit an alphanumeric yet in our right to left scan
                    ' - OR - we need to add an additional character. Eg given input string "9" increments and rolls back to "0" BUT
                    ' we need to insert another character in this case so result equals "10", not just "0". Insert that char into
                    ' the string here.
                    If b_prev Then 'we need to insert a new character
                        Select Case b_prev
                            Case 65 'A
                                b = 65
                            Case 97 'a
                                b = 97
                            Case 48 '0 (zero)
                                b = 49
                        End Select
                        str = Left(str, i) & Chr(b) & Right(str, str_len - i)
                        Exit For ' We are done
                    End If
            End Select
        Next i
        
        'return results without the extra space we inserted at the beginning of the function
        '   recalcualte len(str) because we could have inserted another character since we started
        succ = Right(str, Len(str) - 1)
        
    ExitHandler:
        Exit Function
        
    ErrHandler:
        debug.print "Error in 'succ' function. Error #" & err.number & ": " & err.description
        succ = err
        Resume ExitHandler
        
    End Function
    So in your case it would increment "AA-01" to "AA-02", "AA-99" to "AA-100", etc. It wouldn't increment characters to the left of the hyphen.

    To use it simply copy the function into a VBA module in your project and you might do something like this:
    Code:
    Dim prev_num as String
    Dim next_num as Variant
    
    prev_num = <your code to find what ever the previous number was>
    next_num = succ( prev_num )
    
    if not iserror(next_num) then
      'Your code here
    else
      'Your error code here
    end if

    [EDIT]


    Quote Originally Posted by Squirrel1804 View Post
    The product when manufactured was done in batches and therefore have batch numbers (eg, AA, AB, AC etc) however when we overhaul the product later down the track we need to give it a specific number to identify it, therefore we mark it up to be AA-01, AA-02 etc.

    I wanted to see if I can have the batch number (AA, AB, AC etc) as a combo box so the tester chooses which batch the product was from and then have Access automatically pick the next number in the sequence of that batch.
    Okay, maybe forget everything I said. You could just have a numeric field that stores the number portion of the product id number and then concat the batch number and this number field at runtime, correct? What's your table structure? Assuming your product table has a foreign key to a batch table you might make a multi field unique index on this numeric field and the batch foreign key.

    For reference here is an example of how you can combine a string batch number and a numeric product number to display the product code
    Code:
    Sub test()
        Dim str As String
        Dim num As Byte
        Dim code As String
        
        str = "AA"
        num = 3
        
        code = str & Format(num, "-00")
        
        MsgBox code
    End Sub

  8. #8
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2015
    Posts
    23
    I havent actually got anything set up yet. I am trying to plan out how it needs to be done. So from beginning to end (in simple english because I am really not making sense of this at the moment) I am opened to any suggestions on how to do this.

    I simply need to have a Batch No. (which the user chooses, maybe from a dropdown, depending on what batch no. comes into the workshop to be overhauled). And then have the next number for that particular batch generated.

    So if we have AA-01, AA-02 but then we get batch AB come in I need that to be AB-01

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Squirrel1804 View Post
    The increment, I think, really needs to be done at the beginning of entering the data, as sometimes we need to know the number to do labels etc, and then complete the database record later.

    I was thinking I could have one table with all the data (cause there really isnt that much data required) and then do a LookUp for the Batch. Once the Batch is chosen when the "Number" would increment.
    Quote Originally Posted by Micron View Post
    Then I think you will need both a create and edit process. You also don't want the number to be editable, nor do you want the edit process to create a new number. Check out the OpenArgs method of the DoCmd object so that you can use one form for both. User action dictates which process is being invoked. I have done this by passing "new" or "edit" to the args parameter and setting up the form accordingly.

    Well, you may have information I"m not privy to so I'm not going to push the multi table approach further. Good luck with your db.
    So how about in the form's Load event (and/or OnCurrent?) you would have something like:
    Code:
    If Me.DataEntry Then
      'code to enable batch combo box
    Else
      'code to disable batch combo box
    End If
    And the form's batch number combo box has something like this in the Before Update event
    Code:
    If Not Me.NewRecord Then
      Cancel = True 'just in case
    Else
      Dim last_num as Integer
      last_num = DMax("[ProductNumber]", "Products", "[BatchID] = " & Me.cboBatch)
      Me![ProductNumber] = last_num + 1
    End if
    ?

    I'm guessing at table structure/field names here but hopefully this is sound and helpful.

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [delete this]

  11. #11
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2015
    Posts
    23
    Sorry whats the [delete this]?

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It's where I rambled on about non sense and decided to delete my post but the forum doesn't allow me to delete the post so I have to edit it to say something else, in this case "[delete this]".

    Anyway, I made this for you. Please see attached.
    Attached Files Attached Files

  13. #13
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2015
    Posts
    23
    Oh my god that's awesome. Thank you so much kd2017.
    This should work out perfectly. Thanks again.
    If I have any issues I'll be back LOL

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, I was pulled away during a post and didn't see the latest updates since I went back to it to finish.
    I threw together a little demo db. It assumes your batch #s are in 1 table, and the numbers will be in another. That's the way I think it should be. Assuming you can open the db with your version, open form, choose a batch and see what the highest current # is and what the next will be. Click Create New and those controls should update themselves as well as write the next batch # to the tblBatchSuffix for that batch (e.g. if 05 is the highest for AA, 06 should get added ).
    Following proper normalization, the batch id (e.g. 2 for batch AB in tblBatch) is written, not AB.
    Maybe not what you're looking for, but maybe another way to do it. I can't look at the posted db now - getting late here. If you can't open, I'll try to save as an earlier version if you want. I didn't get to the point where I finessed so as to start new batches or provide a means to begin a new batch, not knowing if I was even close to achieving the desired result, nor knowing how your batch numbers are created.
    NextNum.accdb
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Increment based on linked value
    By dniezby in forum Programming
    Replies: 7
    Last Post: 05-21-2013, 11:40 AM
  2. Replies: 13
    Last Post: 05-01-2013, 09:01 AM
  3. Replies: 1
    Last Post: 02-23-2012, 11:48 AM
  4. Replies: 9
    Last Post: 05-30-2011, 01:52 PM
  5. Replies: 5
    Last Post: 11-12-2010, 12:10 PM

Tags for this Thread

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