Code:
If Me.tglMultiItem = True Then
'Multi_Item Order
Select Case Me.cbxContractCo
Case "Other"
For i = 0 To lstMultiItem.ListCount - 1
Me.lstMultiItem.SetFocus
varItem = lstMultiItem.ItemData(i) 'get next item in list data
lstBox = varItem 'set listbox to the item
strSKU = lstBox.Column(0)
intQty = lstBox.Column(1)
strProcess = lstBox.Column(2)
Debug.Print strSKU
Debug.Print intQty
Debug.Print strProcess
strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
& varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
CurrentDb.Execute strRecordSQL
Next
Case "Contract - Larq"
For i = 0 To lstMultiItem.ListCount - 1
varItem = lstMultiItem.ItemData(i) 'get next item in list data
lstBox = varItem 'set listbox to the item
varSKU = lstBox.Column(0) 'in vb, columns start with zero
varQty = lstBox.Column(1)
varProcess = lstBox.Column(2)
If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = varSKU")) < CInt(varQty) Then
MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify manager.", vbOKOnly
Next
Else
End If
strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
& varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
CurrentDb.Execute strRecordSQL
strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU = '" & Me.cbxSKU & "';"
DoCmd.RunSQL strUpdateQtySQL
Next
Me.lstSerialNos.SetFocus
For nIndex = 0 To lstSerialNos.ListCount - 1
strSerialList = strSerialList & lstSerialNos.ItemData(nIndex) & ","
Next
strSerialList = Left$(strSerialList, Len(sList) - 1)
Debug.Print strSerialList
strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES (Me.tbxOrderNo.Value , strSerialList)"
CurrentDb.Execute strRecordLarqSQL
Case Else
For i = 0 To lstMultiItem.ListCount - 1
varItem = lstMultiItem.ItemData(i) 'get next item in list data
lstBox = varItem 'set listbox to the item
varSKU = lstBox.Column(0) 'in vb, columns start with zero
varQty = lstBox.Column(1)
varProcess = lstBox.Column(2)
If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = varSKU")) < CInt(varQty) Then
MsgBox "There is insufficient item inventory of " & varSKU & "to fulfill this order. Please notify administrator.", vbOKOnly
Next
Else
strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
& varProcess & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & varSKU & "'," & varQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
CurrentDb.Execute strRecordSQL
strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU = '" & Me.cbxSKU & "';"
DoCmd.RunSQL strUpdateQtySQL
Next
Else
'Single Item Order
If Me.cbxContractCo = "Contract - Other" Then
strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
& Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
CurrentDb.Execute strRecordWOSQL
Else
strRecordWOSQL = "INSERT INTO WOTracking (Process, ContractCo, Employee, SKU, SKUQty, OrderNo, Date_Time) VALUES ('" _
& Me.cbxProcess.Column(0) & "','" & Me.cbxContractCo.Value & "','" & Me.cbxEmployee.Value & "','" & Me.cbxSKU.Value & "'," & Me.tbxSKUQty & ",'" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
CurrentDb.Execute strRecordWOSQL
strUpdateQtySQL = "UPDATE Inventory SET Inventory.SaleableQty = Inventory.SaleableQty -" & Me.tbxSKUQty.Value & " WHERE SKU = '" & Me.cbxSKU & "';"
DoCmd.RunSQL strUpdateQtySQL
If Me.cbxContractCo = "Contract - Larq" Then
strRecordLarqSQL = "INSERT INTO LarqData (LarqOrder, LarqSerial) VALUES ('" & Me.tbxOrderNo.Value & "','" & Me.tbxSerialNo.Value & "')"
CurrentDb.Execute strRecordLarqSQL
Else
End If
End If
End If
Any insights or advice would we appreciated.