Solved

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

Posted on 2016-11-05
11
35 Views
Last Modified: 2016-11-06
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
Comment
Question by:Dustin Stanley
11 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 125 total points
ID: 41875878
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
 
LVL 40

Assisted Solution

by:als315
als315 earned 125 total points
ID: 41875880
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41875918
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 30

Expert Comment

by:hnasr
ID: 41875921
Here is a sample.
combos.accdb
0
 

Author Comment

by:Dustin Stanley
ID: 41876231
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41876236
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
 

Author Comment

by:Dustin Stanley
ID: 41876249
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41876252
Waiting!
Add to this file:
combos-2.accdb
0
 

Author Comment

by:Dustin Stanley
ID: 41876286
Pretty sure I figured it all out. I will update soon.
0
 

Author Comment

by:Dustin Stanley
ID: 41876294
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
 

Author Closing Comment

by:Dustin Stanley
ID: 41876297
Thank you!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question