list box with more than one RowSourceType?

[ChasW]

Is it possible to use a query or table as a RowSource for a list box
as well as be able to add values to the RowSource in VB as you can if
the type was Value List?

If not, how can I go about extracting a column of values from a table
or query and add them to a list for use with the Value List type in
VB?

Simply put, I need the column of values from the query/table, which
changes frequently, as well as 3-4 additional options which ideally
should appear at the top of the list box at runtime.

Thanks in advance for any tips or suggestions.
Chas

 

[Douglas J. Steele]

No, it's not possible.

You could loop through the original table or query and manually add the
values to the Value List, you could add the new values to the original table
(If you do this, you'll need to do a Requery to get them to show up in the
listbox) or you could create a UNION query that adds the additional options
to the query:

SELECT -3, "MyFirstOption"
FROM MyTable
UNION
SELECT -2, "MySecondOption"
FROM MyTable
UNION
SELECT -1, "MyThirdOption"
FROM MyTable
UNION
SELECT Id, Field1
FROM MyTable

Note that you do need to include a FROM clause in the subselects, even
though you're only passing literals. The fact that you're using UNION
(rather than UNION ALL) means that duplicates will be eliminated.

 

[ChasW]

No, it's not possible.

You could loop through the original table or query and manually add the
values to the Value List, you could add the new values to the original table
(If you do this, you'll need to do a Requery to get them to show up in the
listbox) or you could create a UNION query that adds the additional options
to the query:

SELECT -3, "MyFirstOption"
FROM MyTable
UNION
SELECT -2, "MySecondOption"
FROM MyTable
UNION
SELECT -1, "MyThirdOption"
FROM MyTable
UNION
SELECT Id, Field1
FROM MyTable

Note that you do need to include a FROM clause in the subselects, even
though you're only passing literals. The fact that you're using UNION
(rather than UNION ALL) means that duplicates will be eliminated.

Thank you.

What I went ahead and did was create a query that groups by the field
that i will be using values for the list box, thus removing duplicate
entries. Then I opened that query as a recordset and copied the row
values into an array. Then iterated that array copying each element
as part of the RowSource for the list box.

It took a while how to figure out how to do it, but it works well and
seems to have been a worthwhile learning experience to boot.

Thanks again,
Chas