• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

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

0
Dustin Stanley
Asked:
Dustin Stanley
3 Solutions
 
hnasrCommented:
Try:
Tables:
t1(f1)
t2(f1, f2)

Combo1.RowSource: SELECT f1 from t1;

Combo1 After update event:
Combo2.RowSource: SELECT f2 from t2 WHWEW f1=Combo1
0
 
als315Commented:
You should add to criteria reference to your form's field (ProdLocID). Something like Forms!MyForm!ProdLocID (use query builder for proper syntax). Criteria could be:
WHERE (IIf(IsNull(Forms!MyForm!ProdLocID),True,IIf(Forms!MyForm!ProdLocID=Locations.LocID,True,False))=True);

Open in new window

May be you can upload sample?
0
 
Gustav BrockCIOCommented:
All you need is to filter on the selected ProductID:

SELECT 
    Locations.LocID, 
    Locations.LocNm
FROM 
    Locations 
INNER JOIN 
    ProdLocations 
    ON Locations.LocID = ProdLocations.LocID
WHERE
    ProdLocations.ProductID = [Forms]![YourForm]![YourProductCombo]

Open in new window

or:

SELECT 
    Locations.LocID, 
    Locations.LocNm
FROM 
    Locations 
WHERE 
    LocID IN 
        (Select LocID From ProdLocations 
        Where ProductID = [Forms]![YourForm]![YourProductCombobox])
ORDER BY 
    ProdLocations.LocID;

Open in new window

Note please, that you have to requery combobox Location in the AfterUpdate event of combobox ProductID.

/gustav
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
hnasrCommented:
Here is a sample.
combos.accdb
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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?
0
 
hnasrCommented:
Add what you have done to the Controls.accdb and upload to check. Specify what you have, what you get, and what to expect.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I am out of my office right now so it will be later before I can upload anything.

This is my form:
db1.jpg
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.
0
 
hnasrCommented:
Waiting!
Add to this file:
combos-2.accdb
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Pretty sure I figured it all out. I will update soon.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now