Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

MS Access Query Criteria Match Combo Box Values To A value in Another Combo Box On a Form

I have a form and on that form I have a combo box [ProdLocID]  that I want to only show values based on a value in the other combobox [ProductID].

I have products in locations. I want [ProdLocID] to only display the only existing locations for the [ProductID]

ProdLocID is in the table called ProdLocations and ProductId is in the table Products.


What criteria can I place in the query to do this?

Here is what I tried so far but it gives me all the ProdLocID of all the ProductID

Thanks.

SELECT ProdLocations.LocID, Locations.LocNm
FROM Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID
ORDER BY ProdLocations.LocID;

Open in new window

SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is a sample.
combos.accdb
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Ok thanks guys. I just kinda figured this one out before logging back in here to check. I did the check Combo box 1 and requery and it is working good.  BUT I am using this towards a Update Query....

Form Contains fields: ProductID / QtyMove / ProdLocLocID / LocID

On my form I am Using the [ProductID] ComboBox1 to filter [ProdLocLocID] Combo Box 2. After that I am using a Update Query to Subtract [QtyMove] from [ProdLocLocID].

When I run the Update Query if there is 3 [ProdLocLocID]  for [ProductID] then it subtracts from all 3 [ProdLocLocID].

It is because of this filtering and requery. If I just manually type in the [ProdLocLocID] instead of filtering and then choosing it works fine.

So is there another way to filter and match values that would give me one solid value in my Combo Box 2 after choosing so the Update Query works correctly?
Add what you have done to the Controls.accdb and upload to check. Specify what you have, what you get, and what to expect.
I am out of my office right now so it will be later before I can upload anything.

This is my form:
User generated image
ProductID Combo Box 1:
SELECT [Skus].[Sku] & "" & [Conditions].[CondCode] AS [Product SKU], Products.ProductID
FROM SKUs INNER JOIN (Conditions INNER JOIN Products ON Conditions.ConditionID = Products.ConditionID) ON SKUs.SkuID = Products.SkuID;

Open in new window



ProdLocLocID ComboBox 2:
SELECT ProdLocations.ProdLocID, Products.ProductID, ProdLocations.ProductID, Locations.LocNm, ProdLocations.QtyLoc
FROM Products INNER JOIN (Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID) ON Products.ProductID = ProdLocations.ProductID
WHERE (((ProdLocations.ProductID)=[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![ProductID]));

Open in new window



Update Query Deduct Values From table ProdLocations
Where ProdLocLocID = [ProdLocations].[LocID]:
UPDATE (Products INNER JOIN ProdMovements ON Products.ProductID = ProdMovements.ProductID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID SET ProdLocations.QtyLoc = [qtyloc]-[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![QtyMove]
WHERE (((ProdMovements.ProdLocLocID)=[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![ProdLocLocID]));

Open in new window



I expect to be able to only show the Values in the form combobox [ProdLocLocID] that match [ProductID] in the form and then run a Update Query that subtracts [QtyMove] from the [ProdLocations].[QtyLoc] that matches the forms [ProdLocLocID] with [ProdLocations].[LocID]


I hope this all makes sense and thank you for the help.
Waiting!
Add to this file:
combos-2.accdb
Pretty sure I figured it all out. I will update soon.
It ended up being in my update Query not the Combobox that was not correct!

ProductID Combo Box 1:
SELECT Products.ProductID, [Skus].[Sku] & "" & [Conditions].[CondCode] AS [Product SKU]
FROM SKUs INNER JOIN (Conditions INNER JOIN Products ON Conditions.ConditionID = Products.ConditionID) ON SKUs.SkuID = Products.SkuID;

Open in new window


ProdLocLocID ComboBox 2:
SELECT ProdLocations.ProdLocID, ProdLocations.ProductID, Locations.LocNm
FROM Locations INNER JOIN ProdLocations ON Locations.LocID = ProdLocations.LocID
WHERE (((ProdLocations.ProductID)=[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![ProductID]));

Open in new window


Update Query Deduct Values From table ProdLocations
Where ProdLocLocID = [ProdLocations].[LocID]:
UPDATE (Products INNER JOIN ProdMovements ON Products.ProductID = ProdMovements.ProductID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID SET ProdLocations.QtyLoc = [qtyloc]-[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![QtyMove]
WHERE (((ProdMovements.ProdLocLocID)=[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![ProdLocLocID]) AND ((ProdLocations.ProdLocID)=[Forms]![frmStillBinded]![frmStockTransferbinded].[Form]![ProdLocLocID]));

Open in new window

Thank you!