Dynamic Crosstab Report help

Nov 21, 2011
Reaction score
I have a strange problem in a crosstab report called "RptsavvataepilogicrossA4"
The report uses vba code to produse labels for dates (Saturdays) employees
who have worked in a month. The source is a crosstab query Qrsavvataepilogicross.

Query working fine, all the Suturdays worked by employees are shown by "1" in the query.
and the report with the VBA Code.
But when i use parameters by form, report preview comes out whith blank values although i set up parameters in my query specifically
Report only works only when i type the same parameters not in the form but in the query criteria. eg. 2011 and 11 under the date formatted fields (made for choosing year and month).

the code is under on open and is this:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database, Qrydef As QueryDef, fldcount As Integer
Dim rpt As Report
Dim fldname As String, ctrl As Control, ctrl2 As Control

On Error GoTo Report_Open_Err

Set db = CurrentDb
Set Qrydef = db.QueryDefs("Qrsavvataepilogicross")
fldcount = Qrydef.Fields.Count - 1

If fldcount > 6 Then
MsgBox "The number of field is over (5) and only the (5) first fileds will be shown"
fldcount = 6
End If

Set ctrl = Me.Controls("date1")
Set ctrl2 = Me.Controls("total1")
If fldcount >= 2 Then
ctrl.ControlSource = Qrydef.Fields(2).Name
Me("date1_Label").Caption = Qrydef.Fields(2).Name
ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(2).Name & "])"
End If

Set ctrl = Me.Controls("date2")
Set ctrl2 = Me.Controls("total2")
If fldcount >= 3 Then
ctrl.ControlSource = Qrydef.Fields(3).Name
Me("date2_Label").Caption = Qrydef.Fields(3).Name
ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(3).Name & "])"
End If

Set ctrl = Me.Controls("date3")
Set ctrl2 = Me.Controls("total3")
If fldcount >= 4 Then
ctrl.ControlSource = Qrydef.Fields(4).Name
Me("date3_Label").Caption = Qrydef.Fields(4).Name
ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(4).Name & "])"
End If

Set ctrl = Me.Controls("date4")
Set ctrl2 = Me.Controls("total4")
If fldcount >= 5 Then
ctrl.ControlSource = Qrydef.Fields(5).Name
Me("date4_Label").Caption = Qrydef.Fields(5).Name
ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(5).Name & "])"
End If

Set ctrl = Me.Controls("date5")
Set ctrl2 = Me.Controls("total5")
If fldcount = 6 Then
ctrl.ControlSource = Qrydef.Fields(6).Name
Me("date5_Label").Caption = Qrydef.Fields(6).Name
ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(6).Name & "])"
End If

Exit Sub

MsgBox Err.Description, , "Report_0pen()"
Resume Report_Open_Exit
End Sub

Could you help me? Why do i possibly have this issue?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question