List Box - using with an ARRAY

[ds2728]

Hello,

I would like to know how to fill a list box using an array instead of a table or query.

All help files and books are very vague about this and only say that you use a function and place the name of your function into the data type in the list box porperties.

Thanks,

Dave

 

[Spellman]

Just put the following code into a function

For i=0 to UBound(Arrayname)
combox.additem arrayname(i)
next

Hope this works. I haven't tried it but I assume it will.

 

[jebry]

Hi Dave!

I looked up RowSource in Access help and found a lot of information about the public function method(including an example). There was too much information to copy here but if you go to the help index tab and type in RowSource, you should be able to find what you want. If it turns out that you do not have a complete install of the help files, e-mail me and I will send you a text file with the information in it. If you are having trouble understanding what Access wants, let us know and we can help there also.

One last thing, if you let us know what exactly you are trying to get into the list box, we may be able to come up with an easier way of handling it.

hth Jeff Bridgham
bridgham@purdue.edu

 

[ds2728]

Jeff,

Thanks, I will check the help files closer..

I am allowing the users to print receipts by a batch number. I have a table that has the all batch numbers, date of batch, printed, printed date, and printed by. I am displaying this list of batches in one list box and allow the user to pick which batch(es) they would like to print.

The first list box is labeled "available batches"
the second list box is labeled " selected batches"

The user can move a batch number from the available list box to the selected batches list box. You can also remove them from the selected batches list box also.

Once all selected batches have been added to the selected batches list box, the user then clicks on a print button. This is where I build the sqlstr using the user input in the selected list box.

I have this working, but I resorted to using a permenant table to work with the second list box in the process.

I really wanted to use arrays to manipulate the data instead of a a table.

Thanks, I hope this description is not to confussing.

Dave

 

[jebry]

Hi Dave!

Actually, you do have two other options to accomplish this. First, add another field(yes/no type) to your batches table called selected, making sure that they all start out as false. In your first list box use a rowsource:

Select BatchNumber From YourTable Where Selected = False

And for the second use:

Select BatchNumber From YourTable Where Selected = True

Then in the doubleclick event for the first list box use:

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select Selected From YourTable Where BatchNumber = '" & YourFirstListBox
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

rst!Selected = True
Set rst = Nothing
YourFirstListBox.Requery
YourSecondListBox.Requery

Obviously you will put similar code in the doubleclick event of the second list box setting selected to false.

Second, you could only have one list box and set it's multiselect property to allow more than one selection. Then you use the ItemsSelected property to get your information for the sqlstr:

Dim varItem As Variant

For Each varItem In YourListBox.ItemsSelected
'do your query stuff here accessing the information using YourListBox.Columns(0, varItem)
Next varItem

hth Jeff Bridgham
bridgham@purdue.edu

 

[ds2728]

Jeff,

Thanks I will give it a try.

I have another project that I will need to use the same type of concept.

Thanks Again,

Dave