List box questions...

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

I am used to programming in the VB world, outside of Access. In that world,
it was very easy to add items to listboxes as their were methods like Clear
(to clear all items) and AddItem to add another item, etc. Let's say I want
to cycle through a recordset, which has but a single field, and add values
from all records to the list box? This listbox could get populated many
times as the user makes selections in other controls on the form. The only
way I've found to do it so far is by cycling through the records and
appending to the RowSource property. Is that the best way to do it?

Also, it is a multiselect listbox. How can I determine which items the user
has selected?
 
-----Original Message-----
I am used to programming in the VB world, outside of Access. In that world,
it was very easy to add items to listboxes as their were methods like Clear
(to clear all items) and AddItem to add another item, etc. Let's say I want
to cycle through a recordset, which has but a single field, and add values
from all records to the list box? This listbox could get populated many
times as the user makes selections in other controls on the form. The only
way I've found to do it so far is by cycling through the records and
appending to the RowSource property. Is that the best way to do it?
Click to expand...

You can use the AddItem method rather than appending to the
RowSource. There is no equivalent to the Clear method but
setting the RowSource to "" will achieve the same.
Also, it is a multiselect listbox. How can I determine which items the user
has selected?
Click to expand...
The selected items will be found in the ItemsSelected
property. It is a collection of the Indexes.


Hope That Helps
Gerald Stanley MCSD
 
The most simply way is to prove the sql. I mean, where do those records come
from?

There is *usually* little reason to load up a reocrdset, and THEN set that
data to the listbox. Why not skip the recordset sql, and simply use sql
directly for the listbox.


dim strSql as string

strSql = "select Company from tblCustomers where city = 'Edmonton'"

me.MyListbox.RowSource = strSql

I mean, you could load up a reocrdset with the above sql, and THEN transfer
using .additem (that command exists in a2002 and later), by why write a
bunch of code to do this? That listbox data HAS to come from somewhere..and
99% of time, that data is from a table...so just use sql. It saves tons of
code.

You can also stuff in a delimited string directly into the listbox . This
approach is only good for small lists (say, 50 or less). On the other hand,
who would torture users wit more then 50 entries in a list box
anyway...right!

dim strValueList as string

for i = 1 to 10
if strValueList <> "" then
strValueList = strValueList & ";"
end
strValueList = strValueList & i
next i

me.MyListbox.RowSouce = strValueList

The above would place the numbers 1 to 10 in the listbox. The above assumes
you correct set the list box to Value list. So, for prior versions of
ms-access, you can use the above string idea.

Note also how the listbox is multi-column in ms-access. This is a fabulous
feature.

Check out the following screen shots of using listbox for a grid display:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

last, but not least, in place of a string, ms-access supports the call back
function for a list box. Check out:

http://www.mvps.org/access/forms/frm0049.htm
 
Thank you very much for your lengthy and helpful reply Albert. The reason
for wanting to use a recordset would be because of complex logic not doable
in SQL. In other words, once I pull a set of records, I then perform complex
logic on the resulting values to determine which subset of them I want to
actually add to the list.
 
Back
Top