Take user number input and create set number of entries in table based on last number in sequential order

[Mixzawa]

So originally the users just selected an Item # and the system would assign it a lot # for prints out later. If they had to use several lots for the same number, they would have to do them one at a time.

What I want to do for them, is that they would select the item # they want to assign a lot to and then in the "number of sequential lots required" they can enter how many lots that item will take. When they hit "execute lot assignment" it will use the next number from the lot table and then assign the sequential number of lots the user put in the field box.

Basically taking out the need to repeat the process over and over for the same item because it will use more lots. Below is the Visual Basic code I am working with. I notice I can tell it to use the number they input but it just adds that to the current lot number instead of sequential numbers. Any guidance be great.

"LotNoEntry" is the name of the text box the user inputs for the number of lots the Item # will need to fill.

Private Sub ExecuteLotAssign_Click()
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim LotNum As Long
Dim LastLot As Long
Dim LastPallet As Long
Dim LastLot1 As Long
Dim LastPallet1 As Long

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblLotNo", DB_OPEN_DYNASET)
Set rs1 = db.OpenRecordset("tblSpec", DB_OPEN_DYNASET)

With rs
rs1.MoveFirst
Do Until rs1.EOF
If rs1![GMTItem#] <> [forms]![frmAddEditHeader]![Item] Then GoTo 20
.MoveLast
LotNum = ![Lot#]
If rs1!PalletsPerLot > 1 Then
LastLot = rs1!LastLotUsed
LastPallet = rs1!LastPalletUsed
If LastPallet = rs1!PalletsPerLot Then
.MoveLast
LotNum = ![Lot#]
.AddNew
![Lot#] = LotNum + [LotNoEntry]
![Pallet#] = 1
.Update
rs1.Edit
rs1!LastLotUsed = LotNum + [LotNoEntry]
rs1!LastPalletUsed = 1
rs1.Update
Me![Lot] = LotNum + [LotNoEntry]
[forms]![frmAddEditHeader]![Pallet] = 1
[forms]![frmAddEditHeader]![ChipSize] = rs1![ChipSize]
End If
If LastPallet < 5 Then
.AddNew
![Lot#] = LastLot
![Pallet#] = LastPallet + 1
.Update
rs1.Edit
rs1!LastLotUsed = LastLot
rs1!LastPalletUsed = LastPallet + 1
rs1.Update
Me![Lot] = LastLot
[forms]![frmAddEditHeader]![Pallet] = LastPallet + 1
[forms]![frmAddEditHeader]![ChipSize] = rs1![ChipSize]
End If

End If
If rs1!PalletsPerLot = 1 Then
.MoveLast
.AddNew
![Lot#] = LotNum + [LotNoEntry]
![Pallet#] = 1
.Update
Me![Lot] = LotNum + [LotNoEntry]
[forms]![frmAddEditHeader]![Pallet] = 1
[forms]![frmAddEditHeader]![ChipSize] = rs1![ChipSize]
End If
20
rs1.MoveNext
Loop
End With
rs.close
rs1.close

End Sub

Continue reading...