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