lookup recordset with criteria (1 Viewer)

sashapixie

Registered User.
Local time
Today, 00:04
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
 

MarkK

bit cruncher
Local time
Yesterday, 17:04
Joined
Mar 17, 2004
Messages
8,178
In your second routine you are assigning values to CurrentStock and SelecteStock from whatever row happens to be current in the subform . . .
Code:
CurrentStock = Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2)
SelectedStock = Me.Inventory_Transactions_Orders_subform.Form.Quantity
. . . but then within a loop later on . . .
Code:
Do While Not rsTrans.EOF
   If CurrentStock < SelectedStock Then
. . . you rely on the comparison of those same two values for each iteration of the loop. This can't be right. You are looping thru a recordset, right? I would expect you to use values from that recordset. In your code the values of CurrentStock and SelectedStock will never change during the loop.

Hope this helps,
 

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
Yes I am looping through a record set. What I want to happen is that when looping through the recordset, if the current stock field is less than the quantity field the message box appears.
 

MarkK

bit cruncher
Local time
Yesterday, 17:04
Joined
Mar 17, 2004
Messages
8,178
Yes, but your CurrentStock and SelectedStock variables are not changing with each iteration of the loop. They are set before the loop, and then compared in the loop, but that comparison will not change in the loop, because the values of those variables is never reassigned withing the loop. See what I mean?
 

Accessna

Registered User.
Local time
Yesterday, 17:04
Joined
Oct 4, 2015
Messages
15
You have 2 ready values, just compare between them, No need to make loop or search.

Try This:
Code:
Private Sub CmbPickOrder_Click()
  Dim CurrentStock As Long
  Dim SelectedStock As Long
 
  CurrentStock = Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2)
  SelectedStock = Me.Inventory_Transactions_Orders_subform.Form.Quantity
 
  If CurrentStock < SelectedStock Then
    MsgBox "Order can not be completed due to insifficent stock. Please pick manually"
  Else
    MsgBox "OK"
  End If
End Sub
 

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
This only works if I am on the selected row, the loop is there to search through each row in my sub form without me having to click on each row myself.
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
If for example the field names for CurrentStock and SelectedStock were CS and SS in the record set then maybe

Do While Not rsTrans.EOF
If rsTrans!CS < rsTrans!SS Then

would do what you want.
 

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
I get the debug error Item not found in this collection on the line

If rsTrans!CS < rsTrans!SS Then
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
I used CS and SS just as examples. Please do the following and maybe I can be more precise.

1. Go to the sub forms properties
2. In the Record Source property click on the button with three dots to go to the query
3. Change to the SQL view by right clicking on the title.
4. Copy the SQL and post it so that we can see the field names.
 

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
SELECT Products.ID AS [Transaction ID], [Inventory Transactions].*, [Inventory Transaction Types].*, Products.*, IIf([Inventory Transaction Types]![Add/Remove]="Addition",[Inventory Transactions]![Quantity],IIf([Inventory Transaction Types]![Add/Remove]="Removal",-[Inventory Transactions]![Quantity],0)) AS [Actual Quantity], IIf(IsNumeric([Purchase Order ID]),"Purchase Order",IIf(IsNumeric([Customer Order ID]),"Customer Order")) AS [Order Type], [Unit Cost]*[Quantity] AS [Line Cost]
FROM (Products INNER JOIN [Inventory Transactions] ON Products.ID = [Inventory Transactions].[Transaction Item]) INNER JOIN [Inventory Transaction Types] ON [Inventory Transactions].[Transaction Type] = [Inventory Transaction Types].ID;
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
Sorry but I guess but the SQL didn't help as much as I thought as I cannot relate any of the field names to CurrentStock or SelectedStock. But I can say this. If for example in the loop you wanted to compare SelectStock to the field [Actual Quantity] that would look like:

Do While Not rsTrans.EOF
If rsTrans![Actual Quantity] < SelectedStock Then

So in general to access the fields in the record set, it is the record set name followed by the bang operator (!) followed by the field name. Hope this helps. If not maybe if you tell us more about what SelectedStock and CurrentStock are and how they relate to the rsTrans record set.

What are the Control Sources for Tran saction_Item.Column(2) and Quantity on the Inventory_Transactions_Orders_subform?
 
Last edited:

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
Selected Stock = The quantity field in the table Inventory Transactions, this appears as a field in my subform called Inventory Transactions Orders Subform.

The Current stock =

The Transaction Item field in the table Products, this appears as a field in my subform, taken from the table Inventory Transactions, part of this field is column called Current Stock, this is a field in a query called Inventory Stock Levels.

SQL

SELECT [Current] AS Expr1, Products.[Product Name], [Inventory Stock Levels].[Current Stock]
FROM Products INNER JOIN [Inventory Stock Levels] ON Products.[Product Code] = [Inventory Stock Levels].[Product Code]
ORDER BY Products.[Product Name];

This allows me to select a product and show the stock level for this product in my subform, by setting the column width on this field to show both columns, product code and stock level.

I want to have a message box appear when the quantity field in the subform is greater than the current stock level for the product on the row of the subform datasheet.

I hope this makes more sense.
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
Sorry but I'm still not sure what the relevant field names are. Maybe

Do While Not rsTrans.EOF
If rsTrans![Current Stock] < rsTrans![Quantity] Then


I don't see the Inventory Stock Levels table in the Record Source query. Is column 2 of the combo box bound to a field also named Current Stock. If not you probably want the name of what it is bound to. Also the Record Source has Quantity and Actual Quantity. If you want the actually quantity then put in [Actual Quantity]. You may have to qualify these with [Inventory Transactions] if they are ambiguous i.e., rsTrans![Inventory Transactions]![Quantity]
 

sashapixie

Registered User.
Local time
Today, 00:04
Joined
Sep 30, 2015
Messages
27
SELECT [Current] AS Expr1, Products.[Product Name], [Inventory Stock Levels].[Current Stock]
FROM Products INNER JOIN [Inventory Stock Levels] ON Products.[Product Code] = [Inventory Stock Levels].[Product Code]
ORDER BY Products.[Product Name];

Current Stock is the column of Transaction Item

It is looked up by the below

Me.Inventory_Transactions_Orders_subform.Form.Transaction_Item.Column(2)
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
Yes but that appears to be the Row Source of a combo box, but what in the name of this field in the Record Source of the subform. What field is the combo box bound to.
 

sneuberg

AWF VIP
Local time
Yesterday, 17:04
Joined
Oct 17, 2014
Messages
3,506
This is not going to work if [Inventory Stock Levels].[Current Stock] is not in the subforms Record Source. I think you are going to have to create a query to do this. You can start with the Record Source of the subform and then join the Inventory Stock Levels table to it joining on the Product Code and trim it down to just what you need. In this query you can put the expression Current Stock < Quantity or Actual Quantity in it and for criteria for this expression put in true or -1. This query will only have records if the expression is true in one or more records. Let say you name this query "Not Enough". You code could then be

If DCount("*", "[Not Enough]") > 0 then
msgbox ....

I think this is an easier approach.
 

Users who are viewing this thread

Top Bottom