Combo Box values from Query?!

[bartoki]

Depending on which ID is being referenced in a subform, I want to restrict the list of values in a Combo Box using a query:

• I have a subform [Bonus Subform] that is linked to Table!Bonus.

• I have a query [Bonus ID to valueList] that associates each Bonus ID to a list of values. The way this query looks in Datasheet mode is list of IDs on the left, and usable values on the right. Because each ID can have anywhere from 1 to 20 possible values (depending on another query...details are not important), there are often multiple instances of each ID (Bonus ID 87, for example, has 13 possible values that I want in the combo box, so there are 13 instances of 87 in the left column of this query).

Here's the problem. When I set the row source for the Combo Box to the the query [Bonus ID to valueList], it does not restrict the list to just the current ID. Instead, it lists the possible values for ALL Bonus ID's.

I currently have this Combo Box set up like this:
Name: comboIndMult
Source: indMult (field on Table:Bonus...so what the user selects from this combo will be stored in this field)
RowSource: SQL statement with (column 1)Table:Bonus:bonusID joined to query [Bonus ID to valueList] via bonusID and displaying field (column 2) [valueList].
Column Count: 2
Column Widths: 0";2"
Bound Column: 1

So shouldnt this Combo Box be restricted to ONLY the values with the current Bonus ID?!

I hope this description makes sense. Thanks for any help! I've been tearing my hair out for the last two days over this.

Thanks!
-DK

 

[jebry]

Hi DK!

Is this a query that you have saved or is it SQL that you typed into the RowSource? Either way it would be useful to post the actual SQL. No where in your description do you indicate that you have limited the query in anyway using a Where clause. So you would need to add the Where clause referencing the textbox on the subform.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[bartoki]

No problem! Here is the SQL.
The query [BonusID to valueList] looks like this:

SELECT Bonus.bonusID, IndMultValues.valueList
FROM (Bonus INNER JOIN [BonusID to GradeRating] ON Bonus.bonusID = [BonusID to GradeRating].bonusID) INNER JOIN (IndMultRouting INNER JOIN IndMultValues ON IndMultRouting.routedGradeRating = IndMultValues.gradeRating) ON [BonusID to GradeRating].Expr1 = IndMultRouting.gradeRating
GROUP BY Bonus.bonusID, IndMultValues.valueList
ORDER BY IndMultValues.valueList;

I was hesitant to post this because it involves other tables (IndMultRouting, etc.) and complicates describing this issue. I'm pretty sure this query is not the problem. The point of this query was to associate each Bonus ID to each allowable value, and it effectively does so (verified by looking at the results in datasheet mode).

The problem, I belive, lies in how I am using this query in my Combo Box. Here is the SQL for my combo box row source:

SELECT Bonus.bonusID, [BonusID to valueList].valueList
FROM Bonus INNER JOIN [BonusID to valueList] ON Bonus.bonusID = [BonusID to valueList].bonusID
GROUP BY Bonus.bonusID, [BonusID to valueList].valueList
ORDER BY [BonusID to valueList].valueList;

There is no WHERE statement, or anything limiting the results because I thought that it would automatically limit the list of values to only the ones that have the current Bonus ID in the left column. No? I did try a few WHERE strategies, but with no success. For example, I tried:

SELECT
FROM Bonus INNER JOIN [BonusID to valueList] ON Bonus.bonusID = [BonusID to valueList].bonusID
WHERE (((Bonus.bonusID)=[Forms]![Bonus subform]![Bonus ID]))
GROUP BY [BonusID to valueList].valueList
ORDER BY [BonusID to valueList].valueList;

[Forms]![Bonus subform]![Bonus ID]) is a text box whose source is Table:Bonus:bonusID.

I'm confused, to say the least

Thanks for whatever help you can provide Jeff! Im close to my wits end.

 

[bartoki]

...and I should say that when I tried
WHERE (((Bonus.bonusID)=[Forms]![Bonus subform]![Bonus ID]))
in my row source as a limiting strategy, it returned no entries.

...thanks again!

 

[bartoki]

I finally figured it out! All it needed was a requery. Very elusive. Thanks for the help Jeff and for affirming that the proper way to do this was, in fact, with a WHERE referencing a textbox in the subform.

Thanks again.
-DK