Dynamic number of fields on report

[CSDunn]

Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried this
arrangement for the data I will be presenting, and I get the results I
expect. so everything seems to work okay. The main report displays a single
'row' of data that make up the column headings, and the sub report displays
many rows that make up the main records.

The thing I need to figure out is how to make the physical number of
displayed 'column names' in the main report and associated 'data fields' in
the sub report change based on the report data I need to show. Right now, I
just have static fields in the main and sub report to show the data, but not
all of the fields are populated between the variations of returned data.

The Record Source for the main report is a stored procedure that takes a
@TestShortName parameter. The Record Source for the subreport is also a
stored procedure. The query for the stored procedure of the sub report is
slightly different, but it still takes an @TestShortName parameter. This
@TestShortName parameter equates to the TestID of a Student Test for both
procedures. Each student test has a different number of questions. The main
report is designed to display a questionID (QID) based on the @TestShortName
parameter. So for an @TestShortName of 'SFM2' there would be QID's 1 through
10. But for an @TestShortName of 'HMLM', there would be seventeen total
QID's of A1 through B7. SFM2 would require that there be ten fields in the
main report to show all ten QID's, but HMLM would require that there be
seventeen fields in the main report. The sub report would require the
different number of fields between the two @TestShortName parameters, ten
for SFM2, and seventeen for HMLM. However, the sub report shows question
answers for a given @TestShortName, not QID's. So given different
@TestshortNames, the data returned would look like this:

@TestShortName = 'SFM2':

1 2 3 4 5 6 7 8
9 10 (QID's in the main report)
(studentID) (sName) C I I C I C C C I I
(corresponding question answers in the sub report)

**********************************************
@TestShortName = 'HMLM'

A1 A2 A3 A4 A5 A6 A7
A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's)
(studentID) (sName) C I I I C C C
C I I C C C I C C C
(answers)


I know that I'll need to have the report set up in Landscape to accomodate
the varying results in the main and sub reports, but how do I set up both
reports so that I only have the required number of main and sub report
fields as required by the @TestShortName parameter? It seems like there
should be some sort of Loop structure that could count the number of
distinct QID's given a particular @TestShortName, and then display each
value in a label or text box. Maybe the same for the sub report for question
answers.

The stored procedures are set up to create 75 fields for any @TestShortName.
If an @TestShortName in the main report results in only the first ten QID's
having values, then the other 65 QID's will be NULL. The same would hold
true for the child records in the sub report. So the Loop would have to
ignore NULL values for the main and sub report. Also, for a given
@TestShortName, there can also be NULL values for the first ten QID's, then
the remaining 65 QID's could have values. The child records in the sub
report would match this as well.

How can I set up my report so that I only show the fields I need in the main
and sub reports, and not show fields when they return NULL values, for any
given @TestShortName parameter?

Please let me know if you have any ideas on this.

Thanks for your help!

CSDunn

 

[Allen Browne]

You can CreateControl() if you open the report in design view. Not useful if
you plan to release an MDE.

Just scanning your post, it seems like you want to use only the fields that
are not null at all rows. Presumably that means opening a Recordset, doing a
FindFirst for a non-null value on each one, and outputting the names of the
successful fields to an array of field names. The number of fields is then
UBound - LBound + 1.

Once you have the number of fields you wish to display and their names, you
could use code in the Open event of the report to assign the ControlSource
of your text boxes to the names of the fields in the array, set their Width
to the report width divided by the number of fields to display, and then set
the Visible property to False for the unused text boxes from the 75 you have
available.

This example assumes text boxes name txt0, txt1, ..., and an array of field
names called astrFieldName() with iFieldCount members:

iWidthEach = Int(Me.Width / iFieldCount)
For i = 0 to iFieldCount - 1
With Me("txt" & i)
.ControlSource = astrFieldName(i)
.Left = i * iWidthEach
.Width = iWidthEach
.Visible = True
End With
Next

 

[CSDunn]

Thanks for your help, I will explore these ideas further.

CSDunn

 

[CSDunn]

Allen,
Do you have an example of an Array that I could look at? This array would
need to hold field names, and the field names would need to reflect the
value of the field.

When the report opens, I'll already have an OLE DB connection to the
database (the data comes from a SQL Server 2K database, and this report is
part of an Access ADP which authenticates to SQL Server using the current
domain account). I can use the 'Input Parameters' property of the main and
sub reports to send the @TestShortName parameter to the two stored
procedures.

Would I still need to create another ADO connection in the report in order
to use an Array?

Thanks again for your help, I will also check the MSDN site about the
particulars you mentioned below.

CSDunn

 

[Allen Browne]

If you don't need the array, that's great.

It was suggested only because you needed some way to select which fields had
some data and therefore needed to be in the report from amongst the 75
fields in the query. If you have another way to determine which fields to
include, that's great. (Note that the array was one-dimensional: containing
the names of the fields only, not their values.)