Get all values from a listbox

[hbrems]

Dear all,

I have a form where I keep track of computer issues. The form contains a listbox which shows the names of the affected people. At most the list ranges from 1-5 users.

My intention is to send an alert to the affected users by e-mail
when the particular issue has been solved.

My question: how do you put the names from the listbox an array.

For example. The listbox contains:

john
mary

The array would be:

alertReceivers = Array(john, mary)

Kind regards,
Hans B.

 

[Rabbie]

Why don't you use the data source for your list box?

 

[hbrems]

Why don't you use the data source for your list box?

Can you be more specific? The rowsource is a dynamic sql statement...

 

[Rabbie]

Can you be more specific? The rowsource is a dynamic sql statement...

Use the same SQL statement to generate a dataset which will contain the information you are looking for.

 

[Rabbie]

This code populates an array from a recordset.

Set recData = dbs.OpenRecordset(strCriteria)
' Span all of dataset to ensure recordcount is correct
recData.MoveLast
recData.MoveFirst
' Extract data from Query into varArray
varArray = recData.GetRows(recData.RecordCount)
intCols = UBound(varArray, 1) ' extract no of columns
intRows = UBound(varArray, 2) ' extract  no of rows

The array varArraywill hold your data.

 

[hbrems]

Hello Rabbie,

I came up with this:

' fields
Dim usergroup() As String
Dim receivers As String

' create array usergroup
ReDim usergroup(0 To Me.lstusers.ListCount - 1)
For i = 0 To Me.lstusers.ListCount - 1
    usergroup(i) = lstusers.ItemData(i)
Next i

' output array
For Each i In usergroup()
    receivers = receivers & " " & i
Next i

MsgBox receivers