Need help with ListBox Sql

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box and when i select an item i want to perform this sql
which is the Row Source for a ListBox:

SELECT Ingredients.IngredientName
FROM Ingredients
WHERE (((Ingredients.IngredientID) In (Select
Recipes2Ingredients.IngredientID From Recipes2Ingredients Where
Recipes2Ingredients.RecipeID=Me.Combo10.Column(1))))
ORDER BY Ingredients.IngredientName;


it says i have an undefined function. i assume this is
Me.Combo10.Column(1). if i hard code a value it works so i guess the rest of
the sql is ok. how do i tell it to use the value from the Combo10?
 
brianv,

Are you using the query grid for this, or coding it in Vba editor? What is
the rowsource for Combo10? What is the bound column set to on the property
sheet? 1? Also, in the vba editor, Column references are zero based, meaning
that the first column of your combo is referenced as Me.Comb10.Column(0)
2nd column as ...Column(1)
3rd as ...Column(2)
etc.

Brian
 
Here is what i have.
Tbl Recipes
RecipeId autoNumber
RecipeName text

1 to many

Tbl Recipes2Ingredients
Recipes2IngredientsId autoNumber
RecipeId number
IngredientId number

many 2 1

Tbl Ingredients
IngredientId autonumber
IngredientName text

Combo10:
Control Source: is blank (Unbound)
RowSource:
SELECT RecipeName, RecipeId FROM Recipes ORDER BY RecipeName;

List16 is the Listbox:
Control Source is blank (unbound)

Row Source:
SELECT Ingredients.IngredientName
FROM Ingredients
WHERE (((Ingredients.IngredientID) In (Select
Recipes2Ingredients.IngredientID From Recipes2Ingredients Where
Recipes2Ingredients.RecipeID=Me.Combo10.Column(1))))
ORDER BY Ingredients.IngredientName;



I am not sure what the query grid is but my guess is that i am using it. i
am not in vb. this select stmt appears on the Row Source line of the
property box. if this was in vb i think i could refer to the combo box. i
am not sure if i can refer to it from the poperty row source. basically what
i am trying to do is select a row from the combo box. then based on that
select i want the form to display the associated ingredients for the selected
recipe. what i planed on doing was select the combo item then hit a refresh
button. the select stmt would then get re-executed based on the newly
selected combo item.



--
thanks
brian


Brian Bastl said:
brianv,

Are you using the query grid for this, or coding it in Vba editor? What is
the rowsource for Combo10? What is the bound column set to on the property
sheet? 1? Also, in the vba editor, Column references are zero based, meaning
that the first column of your combo is referenced as Me.Comb10.Column(0)
2nd column as ...Column(1)
3rd as ...Column(2)
etc.

Brian
Click to expand...
 
brianv,

ok, I'd change the properties for combo10, so that you can easily refer to
it without its Column index in either case.

Combo10 RowSource:
"SELECT RecipeID, RecipeName FROM Recipes ORDER BY RecipeName;
Bound column = 1
Column Count = 2
Column Widths: 0";1.5" (hides index, only displays the recipe name)

List16 Row Source:
SELECT Ingredients.IngredientName
FROM Ingredients
WHERE (((Ingredients.IngredientID) In (Select
Recipes2Ingredients.IngredientID From Recipes2Ingredients Where
Recipes2Ingredients.RecipeID=Forms![YourFormNameHere]!Combo10)))
ORDER BY Ingredients.IngredientName;

* on the second to last line, substitute [YourFormNameHere] with the actual
name of your form. See if that'll work for you.

HTH,
Brian


brianv said:
Here is what i have.
Tbl Recipes
RecipeId autoNumber
RecipeName text

1 to many

Tbl Recipes2Ingredients
Recipes2IngredientsId autoNumber
RecipeId number
IngredientId number

many 2 1

Tbl Ingredients
IngredientId autonumber
IngredientName text

Combo10:
Control Source: is blank (Unbound)
RowSource:
SELECT RecipeName, RecipeId FROM Recipes ORDER BY RecipeName;

List16 is the Listbox:
Control Source is blank (unbound)

Row Source:
SELECT Ingredients.IngredientName
FROM Ingredients
WHERE (((Ingredients.IngredientID) In (Select
Recipes2Ingredients.IngredientID From Recipes2Ingredients Where
Recipes2Ingredients.RecipeID=Me.Combo10.Column(1))))
ORDER BY Ingredients.IngredientName;



I am not sure what the query grid is but my guess is that i am using it. i
am not in vb. this select stmt appears on the Row Source line of the
property box. if this was in vb i think i could refer to the combo box. i
am not sure if i can refer to it from the poperty row source. basically what
i am trying to do is select a row from the combo box. then based on that
select i want the form to display the associated ingredients for the selected
recipe. what i planed on doing was select the combo item then hit a refresh
button. the select stmt would then get re-executed based on the newly
selected combo item.
Click to expand...
 
Back
Top