Dynamic Crosstab Report help

M

Manos39

Joined
Nov 21, 2011
Messages
2
Reaction score
0
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


Report_Open_Exit:
Exit Sub

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


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