Can an IIf Statement return more than one value

[Guest]

I have a form that has two cascading combo boxes on it. I would like to have
a third combo box that is populated based on the data entered in the two
combo boxes. Here is my current layout
The two cascading combos are “cboModel†and “cboHousingâ€. The third combobox
is “cboStroke†for its row source I was just using an IIf Statement and that
works but I can only get it to return one value and I would like to have up
to three or more values in combobox.
Here is what I have tried so far.
Stroke: IIf(Forms!frmQuote!SubfrmQuote!cboModel=1,24,
IIf(Forms!frmQuote!SubfrmQuote!cboModel=1 And
Forms!frmQuote!SubfrmQuote!cboHousing=109.88,36 AND 24,0))

I know in the "And in the true part of the nested IIf is not being used
correctly I am just using this to try and explain that if true I would like
to return two values for the combobox.
Thank you for your help
Cheers
TC

 

[Mal Reeve]

I find the easiest way to get the source for this is to build a query on the
same datasource as your form, and add some sample data to filter by (for
where ever cboModel and cboHousing gets their data).
Run the query to make sure you get the results you expect.
Take a look at the SQL view - and copy this code to use for your third combo
datasource - but replace the WHERE stuff with
[Forms!frmQuote!SubfrmQuote!cboModel] etc...

HTH

Mal.

 

[Guest]

If I understand you correctly you are using the combo the wrong way. A combo
is a 'pull-down' and you select from the the list presented. The combo may
use an if statement in it's row source but a WHERE statement would be better.
If you use a list box it has multi-select. Search the Access news group for
'listbox multiselect' to learn more.

 

[Guest]

Karl,
I am sorry that I was not a little clearer, I am still relatively new to
this. I am only looking to select one record from the combo. What I am trying
to do is populate the combo's "pull down" using the IIf statement. The
problem I am having is I need the "true" part of the statement to return more
than one value to the "pull down" of the combo.
I am not sure if this is even possible. If not can you please recommend an
alternative.
Thank you for all of your help.
Tim

 

[Douglas J. Steele]

Are you saying that you want the IIf statement to provide the RowSource
(i.e.: the list of values that are to appear in the pull down), or are you
saying that you want the IIf statement to select multiple rows from the
combo?

For the latter, it's not possible: combo boxes can only have a single row
selected.

For the former, regardless of the data type of the rows, the RowSource
property is a string, so you just need to put a semi-colon delimited string
as the options for the IIf statement.

Stroke: IIf(Forms!frmQuote!SubfrmQuote!cboModel=1,"24",
IIf(Forms!frmQuote!SubfrmQuote!cboModel=1 And
Forms!frmQuote!SubfrmQuote!cboHousing=109.88,"36; 24",Null))

 

[Guest]

Doug,
Thank you for the help. When I switched over to your recomendation my combo
now just displayed "24;36" I would like to be able to select either 24 or 36
from the combo. Also I need to strore these values as a number not text.
Thank you once again.

 

[Douglas J. Steele]

Sounds as though you're trying to use the query in which that IIf statement
appears as the RowSource for your combo box (with the RowSourceType set to
Table/Query). That won't work.

My expectation was that you'd open the query in VBA, and pass whatever's
returned as the RowSource for the combo box, with the RowSourceType set to
Value List.

If you really want to use the query as the RowSource for your combo, you're
going to have to structure the query in such a way that it returns a
different number of rows depending on what's in the controls on the form.

 

[Guest]

Doug,
I am new to VBA but would like to continue to learn more. What would be the
best way to open the query on VBA I am guessng an "On Click" Or 'On Focus"
also can you recomend a sample code that I could work with to get this to
work. And if that is too much info/ work for this forum can you recomend a
good resouce to help me figure this out.
Thanks for all of your help. Your other post in the forum have helped me out
a bunch in the past.
TC

 

[Douglas J. Steele]

Let's step back a minute.

Exactly what are you trying to accomplish? There may be better alternatives.

 

[Guest]

I have a form that uses eight cascading combo’s, all of the combo boxes are
filtered by what is selected in the combo “cboModelâ€. One of the combo’s
(“cboStrokeâ€) needs to have and additional filter, it needs to be based on
the current fields “cboModel†and “cboHousingâ€.
For example
If the Model =1 the combo would have three values to choose from 24,36,48
If the Model =1 and the Housing=109.88 the combo would only allow you to
choose 24,36
I initially tried to use an IIf statement as the Row Source with the Row
Source Type set to Table/Query. That would work if I only wanted to the Combo
to have one value. I need the combo to list up to four values.
I hope this help to better explain what I am trying to do. Thank you for
your patience.
TC

 

[Douglas J. Steele]

Do you have a table anywhere that contains all of the possible values? If
not, create one. Then you may be able to use the technique outlined in
http://office.microsoft.com/en-ca/access/HA011730581033.aspx?pid=CH063650361033

 

[Guest]

Doug,
Thanks for the help. It was a very easy fix, I am not sure why we always
look for the complex solutions.
Cheers
TC