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.
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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.
ASKER
I am out of my office right now so it will be later before I can upload anything.
This is my form:
ProductID Combo Box 1:
ProdLocLocID ComboBox 2:
Update Query Deduct Values From table ProdLocations
Where ProdLocLocID = [ProdLocations].[LocID]:
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.
This is my form:
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;
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]));
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]));
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.
ASKER
Pretty sure I figured it all out. I will update soon.
ASKER
It ended up being in my update Query not the Combobox that was not correct!
ProductID Combo Box 1:
ProdLocLocID ComboBox 2:
Update Query Deduct Values From table ProdLocations
Where ProdLocLocID = [ProdLocations].[LocID]:
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;
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]));
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]));
ASKER
Thank you!
combos.accdb