Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011

    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")
    'Debug.Print qd.Fields.Count
    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
    Next i
    For i = i To (12 + 2)
        Me.Controls("Label" & i - 2).Visible = False
        Me.Controls("Text" & i - 2).Visible = False
    Next i
    Set qd = Nothing
    Set db = Nothing
    End Sub
    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.

    Your expertise would be greatly appreciated!
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  2. #2
    Join Date
    Jan 2011

    untea Simon

    Found it!

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts