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
26 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 39

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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now