sashapixie
Registered User.
- Local time
- Today, 14:53
- Joined
- Sep 30, 2015
- Messages
- 27
Hi There
I have the below code that is looking through a recordset on an subform for the value 0 which is located in a column of a lookup field in the subform. The code works when looking up a zero or a specific number.
Private Sub CmbPickOrder_Click()
Set rsTrans = Me.Inventory_Transactions_Orders_subform.Form.Recordset
With rsTrans
.MoveFirst
Do While Not rsTrans.EOF
If Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2) = 0 Then
MsgBox "Order can not be completed due to insufficient stock. Please pick manually"
Exit Sub
.MoveNext
Loop
End With
End Sub
When I change the code to the below:
Private Sub CmbPickOrder_Click()
Dim CurrentStock As Long
Dim SelectedStock As Long
Dim strSQL As String
Set rsTrans = Me.Inventory_Transactions_Orders_subform.Form.Recordset
CurrentStock = Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2)
SelectedStock = Me.Inventory_Transactions_Orders_subform.Form.Quantity
strSQL = SelectedStock > CurrentStock
Debug.Print strSQL
With rsTrans
.MoveFirst
Do While Not rsTrans.EOF
If CurrentStock < SelectedStock Then
MsgBox "Order can not be completed due to insifficent stock. Please pick manually"
Exit Sub
Else
End If
.MoveNext
Loop
End With
End Sub
I have to click the button twice for it to work.
When I remove the exit sub to test each row the message box appears on all rows, but the criteria does not appear on all rows.
I'm picking up code, albeit being self taught, I am not 100% if the code is right.
Any help or insight would be greatly appreciated.
Thanks
Cheryl
I have the below code that is looking through a recordset on an subform for the value 0 which is located in a column of a lookup field in the subform. The code works when looking up a zero or a specific number.
Private Sub CmbPickOrder_Click()
Set rsTrans = Me.Inventory_Transactions_Orders_subform.Form.Recordset
With rsTrans
.MoveFirst
Do While Not rsTrans.EOF
If Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2) = 0 Then
MsgBox "Order can not be completed due to insufficient stock. Please pick manually"
Exit Sub
.MoveNext
Loop
End With
End Sub
When I change the code to the below:
Private Sub CmbPickOrder_Click()
Dim CurrentStock As Long
Dim SelectedStock As Long
Dim strSQL As String
Set rsTrans = Me.Inventory_Transactions_Orders_subform.Form.Recordset
CurrentStock = Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2)
SelectedStock = Me.Inventory_Transactions_Orders_subform.Form.Quantity
strSQL = SelectedStock > CurrentStock
Debug.Print strSQL
With rsTrans
.MoveFirst
Do While Not rsTrans.EOF
If CurrentStock < SelectedStock Then
MsgBox "Order can not be completed due to insifficent stock. Please pick manually"
Exit Sub
Else
End If
.MoveNext
Loop
End With
End Sub
I have to click the button twice for it to work.
When I remove the exit sub to test each row the message box appears on all rows, but the criteria does not appear on all rows.
I'm picking up code, albeit being self taught, I am not 100% if the code is right.
Any help or insight would be greatly appreciated.
Thanks
Cheryl