how do I fill an unbound text box from array

[Mike D]

Private Sub Form_Open(Cancel As Integer)

Dim rs As New ADODB.recordset
rs.Open "tblcase", CurrentProject.Connection

Dim rowarray As Variant
rowarray = rs.GetRows(rs.RecordCount)
rs.Close

Me.txt1 = rowarray("fieldname from table")
End Sub

I need the textbox txt1 filled from a value in the array

 

[J. Clay]

Your array is a two dimensional array with a zero base with the first
element as the field and the second element as the row. Therefore you need
to use the proper format.

Me.txt1 = rowarray(0,0)

This would be equal to the first column of the first row of data.

Typically, you will want to cycle through an array using a for loop.

For x = 0 to UBound(rowarray,2)
me("txt" & x) = rowarray(0,x)
Next

This would scan through the array and place the first columns in text boxes
names "txt0", "txt1", etc. for each subsequent row.

Obviously you need to define limits and where to put things, but this is the
basis of arrays.

HTH,
J. Clay

 

[Emilia Maxim]

Private Sub Form_Open(Cancel As Integer)

Dim rs As New ADODB.recordset
rs.Open "tblcase", CurrentProject.Connection

Dim rowarray As Variant
rowarray = rs.GetRows(rs.RecordCount)
rs.Close

Me.txt1 = rowarray("fieldname from table")
End Sub

I need the textbox txt1 filled from a value in the array

Mike,

an array can have only a numerical index. GetRows returns a
two-dimensional 0-based array. The first dimension represents the
fields in the order the fields are defined in the recordset. This
means the order of the fields either in the table or in the query,
depending what was used to open the recordset.

So you have to know the order of the fields in the table as defined in
design view, and use the ordinal number of the needed field minus 1.
For ex if it's the first field in the second record:
Me!txt1 = rowarray(0, 1)

You'll find an example in the Help to GetRows.

Please note: in the code above you may have more records in rs, and
thus more elements in rowarray. As it is, it is not defined from which
record you are reading the value.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de