Dynamic number of fields in an Access 2000 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)
(studentID) (sName) C I I C I C C C I I (answers)

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

A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's)
(studID)(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

[Brendan Reynolds]

Create the report with the maximum number of controls needed and in the
'Open' event procedure of the form set the Visible property of any redundant
controls to False and the ControlSource property to an empty string.

If you want to get really fancy, you could also adjust the Left and Width
properties of controls to make the used controls fill the space available.

If there are only two possibilities, though, you might be better off just
creating two reports. The amount of code required to re-use the one report
with different numbers of fields might be justified if there were a dozen
different possibilities, but I don't think it would be justified for just
two.

--
Brendan Reynolds

"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

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) (studentID) (sName) C I I C I C C C I I (answers)
**********************************************
@TestShortName = 'HMLM'

A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's) (studID)(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

[DFS]

CS,

Here's some code to dynamically bind report controls at runtime. Not sure
if it will help you in your quest, but feel free:

Private Sub Report_Open(Cancel As Integer)

Set db = CurrentDb()

'LINE UP CONTROLS
For i = 1 To 10
Me("txt" & i).Left = Me("lbl" & i).Left
Me("txt" & i).Width = Me("lbl" & i).Width
Me("sumTxt" & i).Left = Me("lbl" & i).Left
Me("sumTxt" & i).Width = Me("lbl" & i).Width
Next i
'SINCE THE CROSS-TAB RESULTS AREN'T KNOWN UNTIL THE QUERY IS RUN
'BIND THE CONTROLS AND SET LABELS AT RUN TIME

'FIRST BLANK OUT ALL LABELS AND TOTALS
For i = 1 To 10
Me("lbl" & i).Caption = ""
Me("sumTxt" & i).Visible = False
Next i

'BIND CONTROLS IN CONTACT REASON ORDER NUM, AS ENTERED IN REFERENCE TABLE
j = 1
Set rs = db.OpenRecordset("Q_CONTACTS_HOTELS_XTAB")
Set rs1 = db.OpenRecordset("SELECT REASON_CODE AS Reason, ORDER_NUM FROM
T_REASON_LOOKUP ORDER BY ORDER_NUM;")
Do Until rs1.EOF
If j <= 10 Then
For i = 3 To rs.Fields.Count - 1
If rs(i).Name = rs1("Reason") Then
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & j).Visible = True
j = j + 1
Exit For
End If
Next i
End If
rs1.MoveNext
Loop
rs1.Close
rs.Close


End Sub

"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

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) (studentID) (sName) C I I C I C C C I I (answers)
**********************************************
@TestShortName = 'HMLM'

A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 B1 B2 B3 B4 B5 B6 B7 (QID's) (studID)(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

[Chris Dunn]

Thanks for your help!
CSDunn
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!