Outputting Arrays in Reports

[Sorrells]

Does anyone know how to do this? I can create the array in VBA but I cannot figure how to get the information displayed in a report!

 

[shacket]

I have often wondered the same thing but haven't needed to do it, so I haven't looked into it.

One solution, however, would be to create a table, place the information in the table and print the report based on the created table. You could always delete the table right after printing if you didn't need to keep the information.

Dave

 

[Sorrells]

Dave,

Thanks for your reply. I hate to go to the expense of creating a table when I have all the information needed in this array. In addition, I am almost as clueless as to how to output the array values to the table should I create it. How much better would it be if I could output directly into the report.
I found in Stan Leszynski's book Access97 Expert Solutions a discussion of the "callback function" that can be used to output the values of an array to a combobox on a form. I spent the day yesterday unsuccessfully attempting to use this technique to output to a report.
In the evening, upon reflection, I thought that perhaps the technique does not work for reports. Stan does not say, but a combobox can be used in a report (not that it makes a lot of sense to do so).
At any rate, I am still looking for a direct movement of the array data into the report. If you have any more thoughts on this subject or anyone else, I'd be most appreciative!
Regards, Sorrells

 

[shacket]

If you do figure it out, let me know. I would love to file that away for when I need it.

As for populating a table with an array, you would cycle through the array and use the .AddNew function for the table. It would look something like this VERY oversimplified example:

For i = 0 to ArrayCount
With rs
.AddNew
!Field = Array(i)
.Update
End With
Next i

 

[Pat Hartman]

You can use functions as control sources. So, in the report control where you want the array to be displayed:

=YourFunctionThatCreatesTheArray(AnyParameters)

 

[Sorrells]

Shacket et al:

I am pretty well convinced that I cannnot output an array to a combobox using the "Callback function". I was somewhat successful with a form but never in a report. But it was time to move on.
I followed Shaket's advice to write to a table. I have the values in an array, created a table to populate and have been able to open it in VBA code. Now I need to fill it with the array values.
The code snippit Shacket provided works with some constrains. Below is the code:
For i = 0 to ArrayCount
With rs
.AddNew
!Field = Array(i)
.Update
End With
Next i

Since my array is 2-dim I have this so far for code:
For irow = 0 To 7 '8 questions
For iCol = 0 To 7 '8 values for each question
With q_c
.AddNew
!<Count_1> = Qstn_Counts(irow, iCol)
.Update
End With
Next iCol
Next irow

Please note above that I have substituted an actual field name of a table {Count_1} for the generic <Field> that Shacket used. This does work in populating the first field, but what do I do then?
I am now almost full circle because, if I must hard code the field names, I am almost in as bad shape as I was when looking at creating specific controls on the report and hard-coding to them.
So my current question is: "Is there a way to write each row of the array directly into a record of the table without identifying field names? or in another perspective... What am I doing wrong?

Thanks, Sorrells

 

[Fornatian]

Rather than use specific field names you can refer to their index like this:

Dim ctr as integer

For irow = 0 To 7 '8 questions
For iCol = 0 To 7 '8 values for each question
'increment counter
ctr = ctr + 1
With q_c
.AddNew
'use counter as index value
.fields(ctr) = Qstn_Counts(irow, iCol)
.Update
End With
Next iCol
Next irow

 

[Sorrells]

Hi Shaket,

I began working with your code. It led me into a lot of interesting areas and finally I was successful in placing the values in the array into a table.
Much to my dismay, I found myself no better off in pulling values from the table into the report than I was with the array.
Let me provide a bit of context here. The data in the table represents a count of responses from multi-choice questions. The first field contains the results from question 1 that reads: "What is the length of time for your direct order to be received from Independence, Kansas?” They could have responded with answers ranging from 1 through 7. The first record for the first field contains a value of 10 indicating that 10 of the people surveyed selected answrer1. The second record for field1 contains a value of 13, the number of people who responded with answer 2.
The array is fine. The table that contains these values from the array is also fine.
In my report, I wanted to have a label with each question. Below the question I wanted to add additional labels for the text equivalent of the numeric answers. (All this I can do) Besides these answer labels, I wanted to have a control that will display specific cells of the table (or array). It is here that I am failing miserably!
I guess I'll stew over it in the evening. Any thoughts of course will be appreciated and if my description is lacking, let me know so I can communicate this problem properly.
Regards, Sorrells