Cascading combo box

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

I have a form with two combo boxes. The selection the
user makes in the first combo box is supossed to limit
the values in the second. I have used a query to try to
accomplish this. The problem is that when I first open
the form, the underlying query is running and is
prompting me for the value from the first combo. I want
the form to load, the user to choose the value in the
first combo, then have the query run. Why is this
happening? The only code I have running when the form
loads is a maximize command. I initially wanted to
accomplish this whole thing without using a query, and
instead actually putting something in the second combo's
rowsource that would limit it's values, but I couldn't
find any help with that, so if anyone knows how to do it
that way, that would be great.

Thanks,
Tara
 
hi,
to put value in a combo box, just type them in the combo's
row source like this
"you";"we";"us";"they";"them"
in double quotes seperated by simicolins
 
Thanks for the response! I understand that part, but how
do you take it further and have it limit the values? I
need something like an If, Then statement, but in a
rowsource. Like --> If combo1=2, then "$15,000 or
less", "18,500", and "24,000", If Combo1=3, Then "19,200
or less", "23,500", and "28,000". Does that make any
sense? I know that's not the right wording, but I'm
looking for something that does the same thing
essentially.

Thanks for any help!
Tara
 
Where did you place the query? Is it in the Row Source of the second combo
box or in a stored query and the Row Source set to that stored query? Can
you copy and paste the SQL of the query into a message?
 
It's in a stored query and the row source is set to
that. Here is the SQL:

SELECT utlIncomeLevel.GrpNum, utlIncomeLevel.Income
FROM utlIncomeLevel
WHERE (((utlIncomeLevel.GrpNum)=[Forms]![sbfFamily]!
[Combo52]));

Thanks for any help Wayne!!
 
I'm guessing that since you named the form sbfFamily, that the form is a
subform. If that is the case, then the syntax to the control needs to be
modified.

Forms!NameOfMainForm!NameOfSubformControl.Form!Combo52

A subform isn't open as a stand alone form and is therefore, not part of the
Forms collection. You have to get to the subform through the main form.
Since the query can't resolve the path, it assumes that you've entered a
parameter name that you want prompted for.
 
That was it! Fixing that stopped the query from
running. However, now I can't get the first combo box to
accept a value. The values show up, but when I click on
one, nothing happens. Any ideas?

Thanks so much for your help!
Tara
-----Original Message-----
I'm guessing that since you named the form sbfFamily, that the form is a
subform. If that is the case, then the syntax to the control needs to be
modified.

Forms!NameOfMainForm!NameOfSubformControl.Form!Combo52

A subform isn't open as a stand alone form and is therefore, not part of the
Forms collection. You have to get to the subform through the main form.
Since the query can't resolve the path, it assumes that you've entered a
parameter name that you want prompted for.

--
Wayne Morgan
MS Access MVP


It's in a stored query and the row source is set to
that. Here is the SQL:

SELECT utlIncomeLevel.GrpNum, utlIncomeLevel.Income
FROM utlIncomeLevel
WHERE (((utlIncomeLevel.GrpNum)=[Forms]![sbfFamily]!
[Combo52]));
Click to expand...


.
Click to expand...
 
I actually figured that out. We have two different
versions of the form. One is just to view, the other is
to edit. I was working with the view version, so when I
go into the edit, it works fine. However, when I choose
a number in the first combo, no values show up in the
second combo. Do I need an OnUpdate Event of some sort
in the first combo?

Thanks,
Tara
 
Yes, in the AfterUpdate event of the first combo you need to requery the
second combo.

Example:
Me.cboSecondCombo.Requery
 
Okay, I've figured this last issue out as well. Now the
only thing I'm having an issue with is getting the second
combo box to update the data each time I change the value
in the first combo box.

Tara
 
Okay, did it. Works great. One more thing though...I
can only choose the very first value from the second
combo. For example: 1--> Combo1=2, Combo2= $15,000 or
less, $19,500, and $23,000. 2--> Combo1=3, combo2=
$18,750 or less, $25,000, $28,750. I can only choose
$15,000 or less, in the first example and $18,750 or less
in the second example, none of the other choices.

You've been a huge help to me...hopefully this is the
last of the issues with this!
Thanks!
Tara
 
Okay Wayne, the last couple of times I've posted
something, I figure out the answer before I even check
back here for your response, but I doubt that will happen
this time as I've tried everything I can think of!
I've fixed everything associated with the combo boxes as
far as limiting the values, etc., however I'm getting an
error message when I choose a value from the second combo
that says "The value you entered is not valid for this
field. For example, you may have entered text in a
numeric field, or a number that is larger than FieldSize
setting permits" (I'm trying to get the ID number
associated with the value I choose, to be held in a table
called tblFamily in the field called IncomeLevelID).
Also, I've tried recreating the combo box but I get an
error message halfway through that says "No value given
for one or more parameters" and then I can't continue
with it. I'm ready to scream! You've been more than
helpful, Wayne, but if you could help me this last time
I'd be eternally grateful!

Tara
 
The first line of the query feeding your second combo box is

SELECT utlIncomeLevel.GrpNum, utlIncomeLevel.Income

This will put the GrpNum in the first column and Income in the second. Since
you see Income displayed, I'm assuming you've set the width for the first
column to zero, which is normal. To store the GrpNum in the form's table,
the Bound Column should be 1, the Control Source should be the field you are
wanting to store the number in, and the data type of that field in the
form's table should match the data type of that field in the lookup table,
utlIncomeLevel.
 
Back
Top