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
32 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 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