| re: I need a non-example-specific description of how to do dynamic crosstab reports
Thank you. Here's how I got it to work:
Option Compare Database
Option Explicit
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from qryTableOfGrades")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*ID" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.Label0.Caption = rst.Fields(i).Name
Case 1
Me.Label1.Caption = rst.Fields(i).Name
Case 2
Me.Label2.Caption = rst.Fields(i).Name
Case 3
Me.Label3.Caption = rst.Fields(i).Name
Case 4
Me.Label4.Caption = rst.Fields(i).Name
Case 5
Me.Label5.Caption = rst.Fields(i).Name
Case 6
Me.Label6.Caption = rst.Fields(i).Name
Case 7
Me.Label7.Caption = rst.Fields(i).Name
Case 8
Me.Label8.Caption = rst.Fields(i).Name
Case 9
Me.Label9.Caption = rst.Fields(i).Name
Case 10
Me.Label10.Caption = rst.Fields(i).Name
Case 11
Me.Label11.Caption = rst.Fields(i).Name
Case 12
Me.Label12.Caption = rst.Fields(i).Name
End Select
skip_it:
Next
rst.Clone
Set rst = Nothing
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from qryTableOfGrades")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*ID" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.field0.ControlSource = rst.Fields(i).Name
Case 1
Me.field1.ControlSource = rst.Fields(i).Name
Case 2
Me.field2.ControlSource = rst.Fields(i).Name
Case 3
Me.field3.ControlSource = rst.Fields(i).Name
Case 4
Me.field4.ControlSource = rst.Fields(i).Name
Case 5
Me.field5.ControlSource = rst.Fields(i).Name
Case 6
Me.field6.ControlSource = rst.Fields(i).Name
Case 7
Me.field7.ControlSource = rst.Fields(i).Name
Case 8
Me.field8.ControlSource = rst.Fields(i).Name
Case 9
Me.field9.ControlSource = rst.Fields(i).Name
Case 10
Me.field10.ControlSource = rst.Fields(i).Name
Case 11
Me.field11.ControlSource = rst.Fields(i).Name
Case 12
Me.field12.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub
"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:jNcQc.17460$QA5.13691@nwrddc01.gnilink.net...[color=blue]
> Hello Newsgroup. You have all been very helpful in the past and I thank
> you. I try to ask relevant questions so that they don't just benefit me,
> but also benefit the group. I'm currently overwhelmed by useless examples
> across the web on how to make "dynamic crosstab reports" without myself
> having a basic understanding about how to retrieve and assign[/color]
recordsources,[color=blue]
> etc., from fields in a query to fields in the report. I see all these
> examples and I don't know what I can modify and what is essential. I need[/color]
a[color=blue]
> stripped-down, down to basics, tutorial (without a bunch of examples.) or
> just simple explanation.
>
> Looking all over the Internet I find numerous examples of "dynamic[/color]
crosstab[color=blue]
> reports" based on a crosstab query. Unfortunately, everything I found,
> including Microsoft's stuff, is usually based on a specific example and
> doesn't explain the basics of what's really going on. Usually it is based
> on a form that prompts the user to enter a beginning and ending date. I
> don't care about dates. That's not what my query is about. I just need[/color]
the[color=blue]
> bare-bones code to get the field name and data from a column and plug it
> into the form. I do need to filter the results, but that's not my problem
> right now. I'm not asking anybody to do my work for me. I just need a
> clue. I'm asking, in general terms, how to define and open a datasource
> (recordsource?) in a report, search through the column names and assign[/color]
them[color=blue]
> to the labels or text boxes in the header, and search through the fields
> (columns)each record, and assign them to textboxes for each student (row).
> I think the query will supply the rest of the data with little other
> formatting. I wonder why there isn't an easily understood description[/color]
"out[color=blue]
> there" on how to do this without having to revise an example that isn't in
> any way applicable to my situation. I know about inserting unbounded text
> boxes and all that, but then the examples are so example-specific, that I
> don't get the general idea.
>
> Thanks! Rich Hollenbeck
>
> my situation:
> I will have 40+plus columns to print, so I will want to display
> 10-at-a-time, print them, get the next 10, etc. while keeping the same[/color]
page[color=blue]
> formatting for each set of ten columns.
>
> The columns will be activities in a course. The rows will be students
> within a course. The data intersecting the rows and columns will be the
> score (or grade).
>
> Title of Report: Table of Grades
>
> DataSource: qryTableOfGrades (cross-tab query)
>
> Grouped by: [courses].[courseDescription]
> (or the query's representation of it)
>
> Sorted (within each course) by: [students].[student name]
> (or the query's representation of it)
>
> Each course will perhaps have different activities, so I could filter the
> report by the course displayed in the form from which the report is[/color]
called.[color=blue]
>
>[/color]
| 
