Problem in report and used code...

[manos39]

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 s[COLOR="Black"]ource 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?

[June7]

You declared the parameters? Check this article http://allenbrowne.com/ser-67.html

Step debug the code.

[manos39]

Yes ofcourse i ve declared the parameters specifficaly

[June7]

Okay, sorry then, no clue.

Step debug did not reveal anything?

I would have to analyse the project.

[manos39]

Could u take a look if i upload the DB?

[June7]

Yes, I will certainly give it a try. You can copy and remove confidential data if any, be sure to run Compact & Repair before upload, zip if too large for forum, up to 2mb zip allowed.

[manos39]

Hello, and thank you for that, i did upload my Db,

You should go to form ypobolesfrm,
type in ETOS "2011" and in MHNAS "10"
do a click under SABBATA on the A4 button,
it calls Qrsavvataepilogicross which is a crosstab query,
to open the RptsavvataepilogicrossA4 in print preview,
Under the dates, and beside employees, there are blank fields.

But if in query Qrsavvataepilogi which the crosstab Qrsavvataepilogicross uses, and under the first two fields you type "2011" and "10" save it and return to ypobolesfrm, you do a click under SABBATA on the A4 button,
(no reffering from form .. just to command to open the print preview of RptsavvataepilogicrossA4), ...the above report works well..
I d like to know what i am doing wrong cause i cant get it work...

[June7]

Sorry, maybe too many obstacles for me. First is the language (Greek?). I clicked the A4 button and get error message:
The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.

I was able to run the query. I input the values in the prompts but records are blank under all but first two columns, just as you describe happens with the form input.

The parameters look perfectly good to me. I am stumped.

Wait, this worked. Change the expressions in query. Instead of Format() use:
ΑΝΑ ΕΤΟΣ3: Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])
ΑΝΑ ΜΗΝΑ3: Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])

[manos39]

Really? I have a look when i go home! Thank you so much... Can you explain how you did it or why it happend? Can you send db back with that issue solved please?

[manos39]

Sorry but i tryied what you said, and print preview shows blanks under dates.... am i doing something wrong? could you send it back working so i see what i am not doing?

[June7]

Okay, to reduce file size I have extracted the objects into another project file. The crosstab query works. I could not test the report.

EDIT: Purpose served, file removed.

[manos39]

The problem is the report . i had the query working but, in print preview, came out with blank v
alues where query had values ! Please test RptsavvataepilogicrossA4... I want a report like the query in which Saturdays of a month in a year, are populated with values of if
an employee worked on a Saturday. Thats why RptsavvataepilogicrossA4 has vba code. The labels representing saturdays are not same in different months.. i did this in my query, have done it with the crosstab, also wiyh the coded report.. but when instead of typing in the criteria in query fields the year and month.,, i refer with criteria in the same fields from a form,... The report which was working has now blanks... It is so strange.. thank you for your effort

[June7]

I can't run the report, too many error popups as described earlier.

Two controls (ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ΧΡ ΣΑΒΒΑΤΟΥ) error that the control source is not valid.

I have never tried to use a crosstab as recordsource for a report. Your solution looks correct.