Results 1 to 4 of 4
  1. #1
    nkoenig34 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    2

    Autopopulating Serial Numbers

    Dear Team,

    I currently have developed a form that has list boxes and combo boxes that are unbound. In this same form I have a button with code on the on-click event, which the was code retrieved online and it is working good however I would like to add one more feature to this form.

    The form has 3 combo boxes and one list box. cbosheetstart, cbosheetend, cboTestingType, txtLotNumber. The user selects a starting sheet number and an ending sheet number, then selects a testing type and lotnumber and clicks the button which executes the code.

    The code creates a new record for each individual sheet number in the range and then populates the lotnumber and testingtype fields with the information entered in the form. Example below. Information entered in the form would be sheetstart of 1 sheetend of 10, fast blank selected for testing type and 125460 selected for the lot number.
    LotNumber SheetNumber TestingType BeginningSerial EndingSerial
    125460 1 Fast Blank
    125460 2 Fast Blank
    125460 3 Fast Blank
    125460 4 Fast Blank
    125460 5 Fast Blank
    125460 6 Fast Blank
    125460 7 Fast Blank
    125460 8 Fast Blank
    125460 9 Fast Blank
    125460 10 Fast Blank

    The code for this operation is at the bottom of this message and is working great however I would like to be able to add one more list box on the form where the user can enter a beginning serial number. Once the button is clicked the usual code would run but it would also populate the beginning and ending serial number for each new record created based on the beginning serial number provided and the fact that there are 391 serial numbers in a sheet. So if the user entered a beginning serial number of 1 on the form the following would be populated in the beginning and ending serial numbers in the table.

    LotNumber SheetNumber TestingType BeginningSerial EndingSerial
    125460 1 Fast Blank 1 392
    125460 2 Fast Blank 393 784
    125460 3 Fast Blank 785 1176
    125460 4 Fast Blank 1177 1568
    125460 5 Fast Blank 1569 1960
    125460 6 Fast Blank 1961 2352
    125460 7 Fast Blank 2353 2744


    125460 8 Fast Blank 2745 3136
    125460 9 Fast Blank 3137 3528
    125460 10 Fast Blank 3529 3920


    I have not been able to find a solution online for this challenge and I am not a coder by any means but can understand code. I have just been picking it up as I go the best that I can. Thank you for any help you can provide!
    Code:
    Private Sub Command17_Click()
    Dim sLotNumber, sTestingType, sSQL As String
    Dim rs As ADODB.Recordset
    Dim i, iStart, iEnd As Integer
    
    'Check for filled combo boxes
    'Check Start sheet
    If IsNull(Me.cboSheetStart) Or Me.cboSheetStart = "" Then
       MsgBox "Please enter a starting sheet number.", vbCritical
       Exit Sub
    Else
       iStart = Me.cboSheetStart
    End If
    
    'Check End sheet
    If IsNull(Me.cboSheetEnd) Or Me.cboSheetEnd = "" Then
       MsgBox "Please enter an ending sheet number.", vbCritical
       Exit Sub
    Else
       iEnd = Me.cboSheetEnd
    End If
    
    'Check to see if the Start sheet is less than the End sheet
    If iStart > iEnd Then
       MsgBox "Stop messing around and fix the Start and End Sheets!", vbCritical
       Exit Sub
    Else
       i = iEnd - iStart 'number of records
    End If
    
    'Check Lot Number
    If IsNull(Me.txtLotNumber) Or Me.txtLotNumber = "" Then
       MsgBox "Please enter a Lot Number.", vbCritical
       Exit Sub
    Else
       sLotNumber = Me.txtLotNumber
    End If
    
    'Check Testing Type
    If IsNull(Me.cboTestingType) Or Me.cboTestingType = "" Then
       MsgBox "Please enter a Testing Type.", vbCritical
       Exit Sub
    Else
       sTestingType = Me.cboTestingType
    End If
    
    'define recordset to ADD new records with this data
    sSQL = "select * from CR39Entry"
    
    'open recordset and connection
    Set rs = New ADODB.Recordset
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    
    'Add all records here
    Do Until iStart = iEnd
    
        rs.AddNew
        rs("SheetNumber") = iStart
        rs("LotNumber") = sLotNumber
        rs("TestingType") = sTestingType
        rs.Update
        iStart = iStart + 1
    
    Loop
    
    rs.Close
    
    End Sub
    Last edited by June7; 01-18-2016 at 03:42 PM. Reason: add CODE tags

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use a variable that increments by 392 within each sheet loop
    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
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    FYI, your declarations are not doing what you think they are doing.
    Code:
    Dim sLotNumber, sTestingType, sSQL As String
    Dim i, iStart, iEnd As Integer
    "sSQL" is declared a String, "iEnd" is declared an Integer.
    ALL other variables are Variant type. You MUST explicitly declare variable types - otherwise they are variant.


    You should have:
    Code:
    Dim sLotNumber As String, sTestingType As String, sSQL As String
    Dim i As Integer, iStart As Integer, iEnd As Integer

  4. #4
    nkoenig34 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    2
    Thank you for the help

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

Similar Threads

  1. Adding serial numbers to reports automatically
    By Ayiramala in forum Access
    Replies: 2
    Last Post: 12-18-2014, 10:29 AM
  2. Help with Serial numbers table
    By RandyP in forum Programming
    Replies: 2
    Last Post: 06-11-2014, 03:39 PM
  3. mutli serial numbers in one
    By wirelineuk in forum Queries
    Replies: 1
    Last Post: 10-11-2012, 01:26 PM
  4. Automatic Serial Numbers
    By Mitch87 in forum Access
    Replies: 9
    Last Post: 02-18-2010, 12:57 PM
  5. organizing serial numbers and quantities
    By Diomeneus in forum Access
    Replies: 0
    Last Post: 11-14-2008, 03:17 PM

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