Results 1 to 11 of 11
  1. #1
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28

    Increment part of a serial number

    I have a data entry form in which a serial number is input. I would like to have the capability to place a check box, which, when true, increments one part of the serial number when the "new data entry" button is pressed.

    So a sample serial number might be 1-24. I would like the next one to be 2-24, then 3-24 etc.

    Is there any way to do this?



    Thanks in advance!

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    In the On Click Event of the 'NewDataEntry' button, you can put code that will do the increment if the check box is checked:

    Code:
    Dim OldSerialNumber As String, NewSerialNumber As String
    If Check11.Value = True Then
        Text23.SetFocus
        OldSerialNumber = Text23.Text
        NewSerialNumber = CStr(CInt(Left(OldSerialNumber, 1)) + 1) + Right(OldSerialNumber, 3)
        Text23.Text = NewSerialNumber
    Else
        'If  you need to do something here . . .
    End If
    Hope this helps.
    Last edited by Robeen; 06-08-2012 at 10:07 AM. Reason: typo

  3. #3
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    It looks promising, but I'm not finding any success.
    The debugger says that I have a "type mismatch" on the

    NewSerialNumber = CStr(CInt(Left(OldSerialNumber, 1)) + 1) + Right(OldSerialNumber, 3)

    line

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would use two Integer field: one field would hold the number for the left part of the serial number and the other field would hold the right part of the serial number. Then incrementing either part of the SN would be easy.

    To display "2-24", in a query, concatenate the fields:
    SN: SN1 & "-" & SN2 (where SN1 and SN2 are the names of the two fields)

    For a text box on a form, the control source could be:
    = SN1 & "-" & SN2

  5. #5
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71
    NewSerialNumber = CStr(CInt(Left(OldSerialNumber, 1)) + 1) + Right(OldSerialNumber, 3)

    I think your parenthesis might be wrong. Also, if you code it like that you might run into problems if the left part goes above a single digit. It's uglier, but I might try something like this instead:
    Dim OldSerialNumber, NewSerialNumber As String
    Dim quickArray() As String
    quickArray = OldSerialNumber.Split("-")
    Dim temp As Integer
    temp = CInt(quickArray(0))
    temp = temp + 1
    temp = CStr(temp)
    NewSerialNumber = temp & quickArray(1)

  6. #6
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    If I do that for the text box, it tells me that the control cannot be edited, it is bound to the expression

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a data entry form in which a serial number is input. I would like to have the capability to place a check box, which, when true, increments one part of the serial number when the "new data entry" button is pressed.
    Let's step back from the code and look at the process of incrementing the serial number. It is easy for a person to know what the next SN should be, but quite hard for a computer.

    a sample serial number might be 1-24. I would like the next one to be 2-24, then 3-24 etc.
    OK, you have a form with a check box and a button on it. Clicking the button should open a data entry form. If the check box is checked (TRUE), then increment the first part of the SN only.
    The problem is which SN do you use to determine what the new SN should be. Create a table with one field named SN; with a datatype of text. Save the table as "Table1".
    Enter 11 records:
    0-24
    1-24
    2-24
    .
    .
    10-24


    How would these be sorted? Create a query, sorted ascending.
    The first record is 0-24 as expected. But then.... the next 3 records are
    10-24
    1-24
    2-24

    And if you add in a record of 10-25, then it gets sorted after 10-24 and before 1-24.
    Welcome to Alphanumeric Sorting! This sorts a string of alphanumeric text left to right, character by character.

    So again, the problem is which SN do you use to determine what the new SN should be. Once you can tell us that, the code to actually increment the SN is easy.

  8. #8
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    The serial numbers in the table will not be sorted by serial number, they will be sorted by date entered. So to answer your question, the first serial number entered during each session would be the one that would need to be incremented.

  9. #9
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    Wow this was a while ago. I tried your method, but I'm getting an error 424, object not found on third line: "quickArray = OldSerialNumber.Split("-")"

    Any idea why this would be?

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not my code, but there are a couple of errors.
    Try this:
    Code:
       Dim OldSerialNumber As String, NewSerialNumber As String
       Dim quickArray() As String
       Dim temp As Integer
    
       OldSerialNumber = Me.SN   ' <- my control name on a form
    
    
       quickArray = Split(OldSerialNumber, "-")
    
    'or you can use
    '     quickArray = Split(Me.SN, "-")  ' <- my control name on a form
    
       temp = CInt(quickArray(0))
       temp = temp + 1
    
       NewSerialNumber = CStr(temp) & "-" & quickArray(1)
       'Now do something with NewSerialNumber
    You will need to add a check to see if the check box value is TRUE.......(if I understand your question correctly)

  11. #11
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71
    Quote Originally Posted by swavemeisterg View Post
    Wow this was a while ago. I tried your method, but I'm getting an error 424, object not found on third line: "quickArray = OldSerialNumber.Split("-")"

    Any idea why this would be?
    I think ssanfu was right, there are some errors.

    Try this instead:


    Dim OldSerialNumber As String, NewSerialNumber As String
    Dim quickArray() As String
    Dim temp As Integer
    OldSerialNumber = Me.SN ' <- my control name on a form
    quickArray = Split(OldSerialNumber, "-")
    temp = CInt(quickArray(0))
    temp = temp + 1
    NewSerialNumber = CStr(temp) & "-" & quickArray(1)
    'Now do something with NewSerialNumber

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

Similar Threads

  1. Automatic Serial Number in Report
    By jamil_kwi in forum Reports
    Replies: 11
    Last Post: 01-19-2015, 11:09 AM
  2. Replies: 6
    Last Post: 03-21-2012, 07:13 PM
  3. Processor Serial Number
    By Azeez_Andaman in forum Programming
    Replies: 2
    Last Post: 08-16-2011, 11:33 AM
  4. Replies: 5
    Last Post: 11-12-2010, 12:10 PM
  5. Help On Creating Automatic of a Serial Number
    By lm_lopes in forum Programming
    Replies: 4
    Last Post: 03-10-2010, 06:47 AM

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