I have a crosstab report that has 30 label boxes as headers and 30 text boxes for the data in the detail section. These are looped through and set when the report is opened. The problem is if there are more than 30 columns generated by the crosstab query then they are not on the report.
I have struggled with a way to do this.
1) I thought of possibly opening the report and then reopening it after it is closed and generating the rest of the columns. This is confusing to the user if they look at it in print preview mode b/c not all of the data is there.
2) I could insert another 30 column labels but what if there are more than 60 columns?
What I would like is to have the report reuse the first 30 columns for 31-60. This way it would be possible to have an unlimited amount of columns.
Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim iCount As Integer
Dim fld As Field
On Error GoTo Err_ErrorTag
iCount = 1
Set rst = CurrentDb.OpenRecordset("qryTimesheet_GroupByEmployee_ThenActivity")
For Each fld In rst.Fields
'// These are the Rows. We want to start with the Column Headers.
If fld.Name <> "Employee" And fld.Name <> "Employee_EmpID" And fld.Name <> "TotalOfTimeTotal" Then
Me.Controls("txt" & iCount).ControlSource = "=([" & fld.Name & "]/[TotalOfTimeTotal])*100"
Me.Controls("lbl" & iCount).Caption = fld.Name
iCount = iCount + 1
'// This was added because only 30 controls fit on the report. However there are more columns being left out.
If iCount > 30 Then
Exit For '// Exit For Next
'// This hides the controls if there are less then 30.
Do Until iCount > 30
Me.Controls("txt" & iCount).Visible = False
Me.Controls("lbl" & iCount).Visible = False
Me.Controls("lbl" * iCount & "a").Visible = False
Me.Controls("line" & iCount).Visible = False
iCount = iCount + 1
On Error Resume Next
MsgBox Err.Description & vbCrLf & vbCrLf & " Error #: " & Err.Number & vbCrLf & "Error Line: " & Erl & vbCrLf & "Procedure: Report_Open " & vbCrLf & " Location: Report_rptTimesheet_EmployeeActivity_EmployeTotals", vbExclamation, "Error"