Multi-deimesional arrays into listboxes

[onedunpark]

Hello,

I'm sure someone will be able to pull me out of the mud on this one.


Having defined an ADO Recordset, I'm populating a listbox with multiple
values from the single column pointed to by the Recordset. The user
chooses the relevant value from the list displayed and then I use the
selected value to identify other related information for the
selection.

I'm typically using this to diplay a list of names and then extract
additional info. based on the name chosen

I'd like, however, to bring back other values from the Recordset, load
only the descriptive field into the listbox, and based on the user
selection extract the other values from the Recordset, rather than
having to perform a number of lookups from the database again based on
the selection.

I'm sure I must be able to
1. load the name, index, etc from the recordset (into a
multi-dimensional array?)
2. display only the first column in the array (the name field) into
the list box
3. once the uer has selected the relevant name from the listbox
4. extract the index from the array based on the name selected in the
listbox by the user.


Anyway, the code to create the basic array is as follows:

vcount = 1
With vrset
Do While Not .EOF
ReDim Preserve CmpArray(vcount)
CmpArray(vcount) = !COMPNAME
vcount = vcount + 1
..MoveNext
Loop
End With

Any tips on how to:
1. define/populate the array
2. display only a specific column of the array in the listbox
3. extract other elements of the same row from the array based on the
selection

would be massively appreciated.

Thanks in advance,

Steven Hoggan

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Steve,

The best thing is to load all of the data into a multicolumn listbox, then
using the .BoundColumn property of the list box, you can access the data in
each column for the item that is selected.

Private Sub UserForm_Activate()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close

In the following, you will see how to use the .BoundColumn property to get
each piece of data (Ignore the bit in it about loading the list box):

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Suppliers.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP