Open Orders with matching Order Item Lines from Item form.

[Louverril]

I have an Item form and I want to add a button that will open my Order form
and show all orders that have an order line for that item.

The order form shows the order header with the order lines as a SUBFORM. It
gets opened from loads of other buttons on other forms - for example to show
all orders for a customer. To do this I use "DoCmd.OpenForm stDocName, , ,
stLinkCriteria". This has always meant setting a simple string eg.
stLinkCriteria = "[Invoice.Customer_ID]=" & Me![Customer_ID].

This time though I need to be able to read the order line records to match
on the item as this only appears against the order line - can't use the
header record. So would need something different.

How can I do this?

Thanks Lou

 

[Ken Sheridan]

In the procedure to open the orders form assign a string expression to the
stLinkCriteria variable using the IN operator and a subquery which returns
all Order_ID values from the OrderLines table where the Item_ID value matches
the current item in the items form:

stLinkCriteria = _
"Order_ID IN(SELECT Order_ID " & _
" FROM OrderLines " & _
WHERE Item_ID = " & Me.Item_ID & ")"

You'll need to change the table and column names to your own ones of course.
Also this assumes that Item_ID is a number data type column, not text. If
the latter you'd need to wrap its value in quotes characters when building
the string expression:

WHERE Item_ID = """ & Me.Item_ID & """)"

Ken Sheridan
Stafford, England

 

[Louverril]

Ken,

Thank you so much for this.

I had tried variations on (ignore syntax):
stLinkCriteria = "SELECT [Order].Order_ID, Order_Line.Item_No" & _
' " FROM [Order] INNER JOIN (Item INNER JOIN Order_Line ON Item.Item_No
= Order_Line.Item_No) ON [Order].Order_ID = Order_Line.Order_ID" & _
' " WHERE Order_Line.Item_No = '" & Me!Item_No & "'"

but I was missing the IN sql option.


Thanks your suggestion works a treat.

In the procedure to open the orders form assign a string expression to the
stLinkCriteria variable using the IN operator and a subquery which returns
all Order_ID values from the OrderLines table where the Item_ID value matches
the current item in the items form:

stLinkCriteria = _
"Order_ID IN(SELECT Order_ID " & _
" FROM OrderLines " & _
WHERE Item_ID = " & Me.Item_ID & ")"

You'll need to change the table and column names to your own ones of course.
Also this assumes that Item_ID is a number data type column, not text. If
the latter you'd need to wrap its value in quotes characters when building
the string expression:

WHERE Item_ID = """ & Me.Item_ID & """)"

Ken Sheridan
Stafford, England

I have an Item form and I want to add a button that will open my Order form
and show all orders that have an order line for that item.

The order form shows the order header with the order lines as a SUBFORM. It
gets opened from loads of other buttons on other forms - for example to show
all orders for a customer. To do this I use "DoCmd.OpenForm stDocName, , ,
stLinkCriteria". This has always meant setting a simple string eg.
stLinkCriteria = "[Invoice.Customer_ID]=" & Me![Customer_ID].

This time though I need to be able to read the order line records to match
on the item as this only appears against the order line - can't use the
header record. So would need something different.

How can I do this?

Thanks Lou