Filtering a Lookup Box

[jwebster1979]

I have a main form called frmPurchaseOrders, and a subform called
subTranactionSubform. In the Purchase Order form I have what Supplier I am
paying. In the subform I choose the product I am buying from that Supplier.
The problem I am having is that I have a ton of products and it is getting
confusing which one to pick that belongs to the suppier in the Purchase order
form. I was curious if anyone new how to filter the lookup box in my subform
based off of the supplier I pick in the main form.
My Main form lookup box Row Select is as follows: SELECT
[Suppliers].[SuppliersID], [Suppliers].[CompanyName] FROM Suppliers ORDER BY
[CompanyName];

My Subform Lookup Box is as follows: SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier];

Please be specific as to where to place any equations and what to type in to
it. I appreciate anyones help!!!!!

 

[Daryl S]

Jwebster1979 -

If your ProductIdentifiersupplier is the same as the main form's
SuupliersID, then you can update your query in your Subform Lookup Box as
follows (use your correct ComboBoxName on the main form):

SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier]
WHERE [ProductIdentifierSupplier] = Parent.[ComboBoxName].Column(0);

 

[jwebster1979]

I tried that and now the first record on the main form is correct but as I
cycle to the next record with a different supplier it is blank any
suggestions to fix that? My subform product lookup box reads as follows:
SELECT Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier FROM Products,
[Purchase Orders] GROUP BY Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier HAVING
(((Products.Supplier)=[Forms]![Purchase Orders]![Supplier])) ORDER BY
Products.ProductName, Products.ProductIdentifiersupplier, Products.Supplier;

I noticed that it says HAVING instead of WHERE would that make a difference?

Jwebster1979 -

If your ProductIdentifiersupplier is the same as the main form's
SuupliersID, then you can update your query in your Subform Lookup Box as
follows (use your correct ComboBoxName on the main form):

SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier]
WHERE [ProductIdentifierSupplier] = Parent.[ComboBoxName].Column(0);

--
Daryl S

I have a main form called frmPurchaseOrders, and a subform called
subTranactionSubform. In the Purchase Order form I have what Supplier I am
paying. In the subform I choose the product I am buying from that Supplier.
The problem I am having is that I have a ton of products and it is getting
confusing which one to pick that belongs to the suppier in the Purchase order
form. I was curious if anyone new how to filter the lookup box in my subform
based off of the supplier I pick in the main form.
My Main form lookup box Row Select is as follows: SELECT
[Suppliers].[SuppliersID], [Suppliers].[CompanyName] FROM Suppliers ORDER BY
[CompanyName];

My Subform Lookup Box is as follows: SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier];

Please be specific as to where to place any equations and what to type in to
it. I appreciate anyones help!!!!!

 

[Daryl S]

Jwebster1979 -

The HAVING is OK. You may need to requery the lookup box in the subform
whenever the main form record changes. You can do this in the On Current
event of the subform. The code would be something like this (use your combo
box name):

Me.ComboBoxName.Requery

--
Daryl S

I tried that and now the first record on the main form is correct but as I
cycle to the next record with a different supplier it is blank any
suggestions to fix that? My subform product lookup box reads as follows:
SELECT Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier FROM Products,
[Purchase Orders] GROUP BY Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier HAVING
(((Products.Supplier)=[Forms]![Purchase Orders]![Supplier])) ORDER BY
Products.ProductName, Products.ProductIdentifiersupplier, Products.Supplier;

I noticed that it says HAVING instead of WHERE would that make a difference?

Jwebster1979 -

If your ProductIdentifiersupplier is the same as the main form's
SuupliersID, then you can update your query in your Subform Lookup Box as
follows (use your correct ComboBoxName on the main form):

SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier]
WHERE [ProductIdentifierSupplier] = Parent.[ComboBoxName].Column(0);

--
Daryl S

I have a main form called frmPurchaseOrders, and a subform called
subTranactionSubform. In the Purchase Order form I have what Supplier I am
paying. In the subform I choose the product I am buying from that Supplier.
The problem I am having is that I have a ton of products and it is getting
confusing which one to pick that belongs to the suppier in the Purchase order
form. I was curious if anyone new how to filter the lookup box in my subform
based off of the supplier I pick in the main form.
My Main form lookup box Row Select is as follows: SELECT
[Suppliers].[SuppliersID], [Suppliers].[CompanyName] FROM Suppliers ORDER BY
[CompanyName];

My Subform Lookup Box is as follows: SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier];

Please be specific as to where to place any equations and what to type in to
it. I appreciate anyones help!!!!!

 

[jwebster1979]

Thank you so much it worked!!!!!!!!!!!!!!!!

Jwebster1979 -

The HAVING is OK. You may need to requery the lookup box in the subform
whenever the main form record changes. You can do this in the On Current
event of the subform. The code would be something like this (use your combo
box name):

Me.ComboBoxName.Requery

--
Daryl S

I tried that and now the first record on the main form is correct but as I
cycle to the next record with a different supplier it is blank any
suggestions to fix that? My subform product lookup box reads as follows:
SELECT Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier FROM Products,
[Purchase Orders] GROUP BY Products.ProductID, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier, Products.ProductName,
Products.ProductIdentifiersupplier, Products.Supplier HAVING
(((Products.Supplier)=[Forms]![Purchase Orders]![Supplier])) ORDER BY
Products.ProductName, Products.ProductIdentifiersupplier, Products.Supplier;

I noticed that it says HAVING instead of WHERE would that make a difference?

Jwebster1979 -

If your ProductIdentifiersupplier is the same as the main form's
SuupliersID, then you can update your query in your Subform Lookup Box as
follows (use your correct ComboBoxName on the main form):

SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier]
WHERE [ProductIdentifierSupplier] = Parent.[ComboBoxName].Column(0);

--
Daryl S


:

I have a main form called frmPurchaseOrders, and a subform called
subTranactionSubform. In the Purchase Order form I have what Supplier I am
paying. In the subform I choose the product I am buying from that Supplier.
The problem I am having is that I have a ton of products and it is getting
confusing which one to pick that belongs to the suppier in the Purchase order
form. I was curious if anyone new how to filter the lookup box in my subform
based off of the supplier I pick in the main form.
My Main form lookup box Row Select is as follows: SELECT
[Suppliers].[SuppliersID], [Suppliers].[CompanyName] FROM Suppliers ORDER BY
[CompanyName];

My Subform Lookup Box is as follows: SELECT [Products].[ProductID],
[Products].[ProductName], [Products].[ProductIdentifiersupplier],
[Products].[Supplier] FROM Products ORDER BY [ProductName],
[ProductIdentifiersupplier], [Supplier];

Please be specific as to where to place any equations and what to type in to
it. I appreciate anyones help!!!!!