Solved Crosstab report - repeating first column for multipage table (1 Viewer)

[Martynwheeler]

Hi,

I have a crosstab that produces a variable number of columns.

[name] - - [skill1] - - [skill2] - -

I would like to create a report based on this. This is to be printed to act as a tick list to be completed while assessing students.

There are 36 different assessments which each have a different set of skills. Each of these needs to be printed for a different class. Hence the reason for using a database approach.

The report will nearly always span two pages (never more) . I'd like the name column to be on the left hand side of each page.

I'm guessing that this isn't a simple matter. I'm happy to try doing it programmatically.

Thought process:
Determine number of skill cols before query
If more than 5 cols do the query twice, dynamically constructing the sql in vba.
Put first query on page and second query on second (is this possible) or do I have to create two reports and merge them?

Any thoughts or hints appreciated.

Thanks
Martyn

 

[arnelgp]

you can also try to create 1 report with 37 columns (a3 size, landscape).
on the Open event of the report you assign to the Textboxes (except the name),
whatever skill is available, your query will produce.

name each textbox sequentially: "txt1", "txt2", "txt3"...
name correspoding label as: "lbl1", "lbl2", "lbl3", ...

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim i As Integer
Set db = Currentdb
' hide first all textbox and label except the name textbox
For i = 2 to 37
    Me("txt" & i).Visible=False
    Me("lbl" & i).Visible=False
Next
With db.OpenRecordset("Select * From (" & Me.RecordSource & ") Where (1=0);", _
        dbOpenSnapshot, dbReadOnly)
    For i = 1 .Fields.Count - 1
         Me("txt" & i+1).Visible = True
         Me("txt" & i+1).ControlSource = "[" & .Fields(i).Name & "]"
         Me("lbl" & i+1).Visible = True
         Me("lbl" & i+1).Caption = .Fields(i).Name

    Next
    .Close
End With
Set db = Nothing
End Sub

 

[arnelgp]

Here is a sample.
notice Table1 has 5 courses.
if you open OnePage Report it will show the report since it has only 6 columns (using Query1, crosstab as source of report).
the columns are [name] and 5 courses.
close the report.

now if you add more courses, to table1 and open OnePage report, the report will be cancelled since there are more than 5 courses to show.
instead TwoPage report will be shown.
note the VBA code on OnePage report.
the disadvantage is that you cannot do this in an .accde.

 

[arnelgp]

another example, but this time, this will run on an .accde
since we are not modying any report at runtime.

see the code on MainReport and subRpt 1 and 2.
always use MainReport when printing, it will automatically switch to RptWith2Pages report
if the columns of the crosstab are more than 6.

 

[Martynwheeler]

another example, but this time, this will run on an .accde
since we are not modying any report at runtime.

see the code on MainReport and subRpt 1 and 2.
always use MainReport when printing, it will automatically switch to RptWith2Pages report
if the columns of the crosstab are more than 6.

Thank you so much, that is amazing.

 

[Martynwheeler]

another example, but this time, this will run on an .accde
since we are not modying any report at runtime.

see the code on MainReport and subRpt 1 and 2.
always use MainReport when printing, it will automatically switch to RptWith2Pages report
if the columns of the crosstab are more than 6.

Hey there,

Thanks for your help so far. It all works with a hard coded crosstab. However, I'd like to pass parameters to the crosstab from the calling form. That works if I open the crosstab with the form open. But if I try to open the report the vba Report Open throws an error at the line

With db.OpenRecordset("Select * From (" & Me.RecordSource & ") Where (1=0);", _

It says no parameter. I am not sure how to pass the parameter to the select statement here. Any help appreciated, thanks.

Martyn

 

[Martynwheeler]

I tried the following and it worked

    ' open db and setup query
    Set db = CurrentDb
    Set qdf = db.QueryDefs("cxtPAG")
    qdf.Parameters("Forms!Form1!Combo4") = Forms!Form1!Combo4
        
    ' open the crosstab recordset and loop though records to assign TextBoxes and Labels
     With qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
        Cancel = (.Fields.Count > maxBox)
        ' determine number of boxes
        k = Min(.Fields.Count - 1, maxBox)
        For j = 1 To k
            Me("L" & j).Visible = True
            Me("L" & j).Caption = DLookup("[title_short]", "Skill", "[skill_id] = " & .Fields(j).Name)
            Me("T" & j).Visible = True
            Me("T" & j).ControlSource = "[" & .Fields(j).Name & "]"
        Next
        .Close
    End With
    If Cancel Then
        DoCmd.OpenReport ReportName:="RptWith2Pages", View:=acViewPreview
    End If
    Set db = Nothing

Would you mind explaining to me the difference between this and your original lines of;

    ' open the crosstab recordset and loop though records to assign TextBoxes and Labels
    With db.OpenRecordset("Select * From (" & Me.RecordSource & ") Where (1=0);", dbOpenSnapshot, dbReadOnly)
        Cancel = (.Fields.Count > maxBox)
        ' determine number of boxes
        k = Min(.Fields.Count - 1, maxBox)
        For j = 1 To k
            Me("L" & j).Visible = True
            Me("L" & j).Caption = DLookup("[title_short]", "Skill", "[skill_id] = " & .Fields(j).Name)
            Me("T" & j).Visible = True
            Me("T" & j).ControlSource = "[" & .Fields(j).Name & "]"
        Next
        .Close
    End With
    If Cancel Then
        DoCmd.OpenReport ReportName:="RptWith2Pages", View:=acViewPreview
    End If
    Set db = Nothing

Thanks

 

[arnelgp]

opening a parameter query as recordset will result to error, that was i read from many references but there is no explanation on it, it simply says as is. now the correct approach is to supply the parameter when using recordset, like what you did.
you can also supply the correct parameter by:

dim p As Parameter
dim rs As DAO.Recordset
With qd
    For Each p In .Parameters
        p.Value = Eval(p.Name)
    Next
    set rs = .Openrecordset
    .Close
End With

 

[Martynwheeler]

Thanks again for your help

 

[Martynwheeler]

Sorry to bring it up again. This all works if there is only one page. However, if I have to go onto two pages and use the page with subreports I get the following error:

It all works ok if I don't have parameters in my query and I use your original idea:

    With db.OpenRecordset("Select * From (" & Me.RecordSource & ") Where (1=0);", dbOpenSnapshot, dbReadOnly)

I am a bit stuck! is it possible to force a report to have two different pages and avoid subreports?

Thanks,

Martyn

 

[Martynwheeler]

Okay, fixed it by including all of the possible columns and then filtering the ones I need.