Fill in text boxes in UserForm

[Dennis]

I've got a UserForm and would like to query a database and
input it's results in to this userform. The doc has a
training list to it as does the DB so would like to
minimize the amount of work to just use the db. I assume
this is possible? I've checked out DAO 3.6 and am able to
connect to the db and I'm working on the query the next
part would be to display the result to the text boxes in
the UserForm. Any help would be greatly appreciated.
Thanxs in advance

Dennis

 

[Doug Robbins - Word MVP]

Hi Dennis,

The following procedure will load a listbox on a userform with the records
from a table/query in an Access database

Private Sub UserForm_Initialize()

'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
End Sub

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 consulting basis.

Hope this helps
Doug Robbins - Word MVP