Okay, what is the first control you use for filtering?
What is it's row source?
What is the second control used for filtering?
What is it's row source?
Does the row source in the second control need to be filtered on the value
in the first control?
:
here is the problem I am having....
The form is more to lookup entries already in the database, rather than
enter new data. The field needs to have the
SELECT DISTINCT [space use validation].SUV FROM [space use validation];
as a row source becuase it chooses the values from the query and places
them in the box. the next box is filtered based on the value 1, 2, or
3 selected that is taken from that query of already inputed data. The
row source cannot be the choose statement or the value list becuase it
has to extract these records from the query based on the SELECT
statement. I am using this form as more of a user-friendly type of
query to lookup entries.
i am simply needing a way to incorporate the translation of the 1, 2,
or 3 being pulled by the SELECT statement to the appropriate text
values.
main point..i need the select statement as at least part of the row
source
Klatuu wrote:
I don't know if this is a duplicate or not, not sure the previous got through.
:
This sounds good, but could you help me with the placement of the
statements?
The row source of the unbound combo box is
SELECT DISTINCT [space use validation].SUV FROM [space use validation];
this brings the values of 1 and 2 (since i only have records for those
two choices so far)
I would suggest a value list as the row source:
1, "Sufficient", 2, "Insufficient", "Surplus"
So where do i place the choose or iff statment and how does the choose
statement relate to the associated numbers?
I don't know where you want to use it. What are you wanting it to do for you?
The Choose statement will one of the choices in the list using the first
argument as in index, so if the combo = 1, it returns "Sufficient"
If i am choosing to
display the text (Me.MyCombo.Column(1)) how does it know to look for
the text if the text is not stored in a table anywhere.
That is the purpose of the value list I described above.
Another way would be to create a table with two columns, the number and the
text and use it as the combo's row source. This, in fact, may be better
because if you have to add another option, you only have to add a row to the
table rather than modify your form.
Sorry I am slow with all of this, i cannot wrap my head around this one
for some reason....
Klatuu wrote:
The combo box approach will not prevent you from doing any of the other
things you want. When you want to refer to the number, it would be:
Me.MyCombo
When you want to refer to the text, it would be:
Me.MyCombo.Column(1)
As to the IIf, the combo box would make that even easier:
=IIf([optExample]=1,"Sufficient",IIf([optExample]=2,"Insufficient","Surplus"))
Could be:
=Choose(Me.MyCombo, "Sufficient", "Insufficient", "Surplus")
If the above goes in a query, it would have to be:
=Choose([Forms]![MyFormName]![MyComboName], "Sufficient", "Insufficient",
"Surplus")
:
Thanks, that would work great, but the idea was not to take up space
with a table that displays the names for the three numbers. It is much
better to tell the database to name the numbers a certain thing (the
Iff statement i posted). Also i need that list/combo/text/check
whatever it ends up being to filter the next list box (codes are
already set up for this action). the check boxes would be ideal only
because eventually the user could search by multiples....(ex filter the
next list box by all sufficient and surplus entries). Both tasks are
operational in different forms, i just need to combine the two. the
combo box showing the name is moving in the right direction but does
not apply to the concept of the form. Somehow i need to combine the
SELECT DISTINCT statement with the IFF statement...
Any thoughts?
Klatuu wrote:
I would suggest a Comb rather than a List Box or Check Boxes. Make the Combo
a two column combo:
1 Sufficient
2 Insufficient
3 Surplus
You can set the ColumnWitdths property to 0";.5" so the numeric column is
hidden.
Also ColumnCount = 2 and Bound Column = 1.
(The .5" for the second column is just a guess at how wide it needs to be to
be displayed correctly.
Now, the user will see the text but not the number.
You really don't need a text box to show the text value, it will be
displayed in the combo.
:
Hello,
I am trying to combine two tasks that I can get to perform separately
but not together. I have a list box that filters the contents of a
second list box based on the row source. This is working perfectly the
way I want it. I am using the contents in a table from a field
(optExample) derived on a form from an option group (so it returns
numbers 1, 2, 3). In a normal text box I can get these values to
return the text values associated with the numbers. I would like to
filter a list box based on the values from the field optExample, yet
show the text values associated with the numbers instead of the 1, 2,
3.
In the working example of the first list box, the control source is
unbound and the row source is ...
SELECT DISTINCT [space use validation].optExample FROM [space use
validation];
In the working example of returning text values in place of the number
values the control source of an unbound text box is as follows....
=IIf([optExample]=1,"Sufficient",IIf([optExample]=2,"Insufficient","Surplus"))
How can I combine these so the list box returns Sufficient and
Insufficient rather than 1 and 2 as choices? I need this to still
successfully work with the filtering performed through the form.
If this can be accomplished by check boxes for each Sufficient,
Insufficient, and Surplus this would be another option, if not ideal in
this situation.
Thanks in advance if anyone has suggestions
Annemarie
