displaying array values in a form list

[incompetence]

Hi,
sometimes it is useful to display calculated values of an array in a list form. Right now I am using a dummy "index" table with values 1....X,
an these values are used to generate a string -return value from a function. This function is placed in the second field of a query. This query is then the data source for the form.

Is there an easier way to make a VBA array the data source for a form?

 

[wazz]

GetRows method. returns all columns and data types, though. watch out for ole objects etc.

 

[incompetence]

Actually, I would like to use it just the other way round, like a (non existing?) method "SetRows".
I have the data in an array and would like to display this array in the form

 

[chergh]

create an appropriate object and use the additem method?

 

[incompetence]

a not so elegant way would be to write it in a temp table and use this table as form source, but thats what I want to avoid, as the information is arelady at hand, I just do not want to write and read it again.

Any one some ideas?

 

[chergh]

Create a listbox on the form and do something like:

for i = lbound(array) to ubound array

ctrListbox.additem item:= array(i)

next i

 

[Banana]

Where are you getting the values from? If this can be created from a query, use that query as the recordset of listbox instead.

 

[incompetence]

Thanks, that works, but restricts the display to a listbox.
It try to use the normal form in list mode with all the differen formatting options.

 

[chergh]

Create a recordset from the query that will be used as the data source of your form and then do something like:

dim rs as recordset

set rs = currentdb.openrecordset("YourQuery")

rs.fields.append "NewField", varchar, 50 'replace this with whatever is appropriate

for i = lbound(array) to ubound(array)

   rs!NewField = array(i)

Next i

 

[LPurvis]

A sound idea from chergh - though just a small point on the aircode.
The example created a DAO recordset - and then appends a field to it (in an ADO syntax). Of course, DAO recordsets can't have new fields appended.
ADO recordsets can - under circumstances where the recordset is not yet open and is hence created from scratch (which is what you want here).

Revised aircode:

Dim rs As ADODB.Recordset
Dim i As Integer

Set rs = New ADODB.Recordset

rs.Fields.Append "NewField", adVarChar, 50
rs.Open

For i = lbound(array) to ubound(array)
    rs.AddNew
    rs(0) = array(i)
    rs.Update
Next i
 
Set Me.Recordset = rs