Unanswered: Dynamic Crosstab Report Blank in Print Preview
I wrote VBA code to take column fields from a crosstab query and put them in the corresponding report. It works well for report view but when I go to print preview, I only get row and column labels but no data.
Name, Code, Project, all dates where records have >DateAdd("d",-14,Date())-Weekday(Date()) such as 2/13, 214, 2/15... 2/24
That's the last two complete weeks. The VBA code was needed because the controls and labels change every week at least (based on the nature of work, not the formula).
Now the part that works in report view but has no values in print preview.
Design View - attached picture
12 Unbounded text boxes named Text1-12 and Labels1-12
The VBA code I use to update labels and controls:
Private Sub Report_Load()
Dim db As Database
Dim i As Long
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("TimeSheetReport_All")
On Error GoTo endThis:
For i = 3 To qd.Fields.Count
' Debug.Print qd.Fields(i).Name
' Debug.Print Me.Controls("Text" & i - 2).Name
Me.Controls("Label" & i - 2).Visible = True
Me.Controls("Label" & i - 2).Caption = qd.Fields(i).Name
Me.Controls("Text" & i - 2).Visible = True
Me.Controls("Text" & i - 2).ControlSource = qd.Fields(i).Name
For i = i To (12 + 2)
Me.Controls("Label" & i - 2).Visible = False
Me.Controls("Text" & i - 2).Visible = False
Set qd = Nothing
Set db = Nothing
I'm hoping I just have it in the wrong place or there's something about print preview I don't know, which could be a lot.
Anything ".controlsource" bugs in print preview mode and not report mode and went to the endThis: error area. It hides unneeded labels and such but it was instead hiding all of them only when I did print preview!
Now I do a .controlsource before the loop for i=1 and if it bugs there then it bypasses endThis. I did it to myself.