Using a Public Array to populate a listbox.

[LOUISBUHAGIAR54]

I have a form on which there is a Combobox (Combo13). From the Combobox I can make various choices each one being a medical investigation.

I would like to populate a listbox on the same form, depending on choices which I make from Combo13. I would like to have the list updated so that each time i make a choice from Combo13 the choice is added on to the list.

I would then eventually enter the choices made into a table as I feel that it is not efficient to add the choices to tables before one confirms ALL the entries in the list.

I thought I would create a Public Array to take the choices from Combo13 and populate the list from the array.

I am encountering various problems namely,

Is my plan the best one to accomplish what I want to do ?

How do I create an array so that the choices which I make are not erased when the vba procedure finishes?

How do I reach an empty element of the array to be filled up with a new choice from the combobox.

Many thanks for your help?


LouisB

 

[VilaRestal]

You wouldn't need an array.

First thing to do is make sure the listbox is set to Value List and turn off Value List Edits.

Then on the Combo13 (please rename it) After Update event (or the click of a button):

Dim sCombo As String, sList As String
sCombo = Me.Combo13
sList = Me.List1.RowSource
If Not InStr(sList, sCombo) Then
    If Len(sList) > 0 Then sList = sList & ";"
    Me.List1.RowSource = sList & sCombo
End If

Note that it's important the combobox doesn't have any values with semicolons in, they are the delimiter in the listbox rowsource.

 

[VilaRestal]

Once you have the list and want to enter its items into a table.

You can create an array of its items using the Split function:

Dim vArray As Variant
vArray = Split(Me.List1.RowSource,";")

You can then loop through that array thusly:

Dim i As Integer
For i = 0 To UBound(vArray)
InsertItem vArray(i)
Next i

And write a sub for InsertItem to put that value into your table.

Although it would need some logical error handling:

Private Sub InsertListItems()
    If Len(Me.List1.RowSource) > 0 Then
        Dim vArray As Variant
        vArray = Split(Me.List1.RowSource,";")
        If IsArray(vArray) Then
            Dim i As Integer
            For i = 0 To UBound(vArray)
                 InsertItem vArray(i)
            Next i
        Else
            InsertItem vArray
        End If
    End If
End Sub

Private Sub InsertItem(ByVal sItem As String)
    'TODO: Add code that inserts the item into the table
End Sub

 

[LOUISBUHAGIAR54]

Hi ViliRestal,

The expert is back!
How's your week-end going ?

Many thanks for your reply. I think your method is very intelligent and intuitive. To tell you the truth I have never seen a list box also serving as a depository of data in this way. However my experience with Access is not that great. I am willing to learn though.

I will now study the code to try to comprehend what's happening and will integrate it into my program. There's lots to learn in it.


Many thanks as always.



LouisB

 

[VilaRestal]

Hi,

Thanks, yeah it's a glorious day, watching the Monaco GP and dipping into here from time to time.

The only complications will be if you do need to be storing items with semicolons or quotation marks in them. It can still be done but it may be advantageous to have an array too as a sort of master copy. Extracting the values back out of the listbox might be more effort then.

 

[LOUISBUHAGIAR54]

Hi,

Can you please explain and clarify the place of the following parts of code ?

If Not InStr(sList, sCombo) Then


If IsArray(vArray) Then


InsertItem vArray

InsertItem vArray(i)


Private Sub InsertItem(ByVal sItem As String)


Many thanks.


Louis

 

[VilaRestal]

If Not InStr(sList, sCombo) Then

Checks the value of the combobox is not already in the listbox

If IsArray(vArray) Then

The Split function will produce an array but only if there are semicolons in the string (in the listbox's rowsource). If not, vArray will just be a single string. Not an array. That function - IsArray - is self explanatory.

InsertItem vArray

InsertItem vArray(i)


Private Sub InsertItem(ByVal sItem As String)


InsertTime is a sub you will write that will add an item into the table. By putting it in a sub it means the code can be reused.