Re: Can an IIf Statement return more than one value

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" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TC" <TC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:88743EF3-3368-404D-84B3-BA2F2FF92376@xxxxxxxxxxxxxxxx
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" wrote:

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))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"TC" <TC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A8829982-B7F1-4D90-9427-F43334A6449C@xxxxxxxxxxxxxxxx
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


"KARL DEWEY" wrote:

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.
--
KARL DEWEY
Build a little - Test a little


"TC" wrote:

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







.

Relevant Pages

  • Re: Can an IIf Statement return more than one value
    ... 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 ...
    (microsoft.public.access.formscoding)
  • Re: Error Message Access Query
    ... You may need to use an IIf statement in the ... > This won't work in VBA, because VBA evaluates both parts of the IIf whether ... > it needs to or not, but in the query, only the true or false part is ... >> I am busy with a calculation in a query in access and the ...
    (microsoft.public.access.gettingstarted)
  • Query is too complex
    ... then run query 5 from the new table. ... >>from query3...They all have an iif statement to verify ... >>My thinking was to separate the queries in the first ... I don't know SQL or VBA so if you go there, ...
    (microsoft.public.access.queries)
  • Re: Access crashes when opening form
    ... Now the combo's RowSource has only 2 fields, so the number of fields in Table1 doesn't matter. ... Are you saying that there's some way to create a query ... > You were correct in suspecting SP3. ... >> database works correctly there. ...
    (microsoft.public.access.forms)
  • Re: Default on a prompt
    ... I entered in the rowsource information. ... Where do I put this wherecondition, and how do I even code it. ... Select ID AS CenterID, ... Now you will have the job of convincing the query to feed all values to the ...
    (microsoft.public.access.reports)