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
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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