U
UnderGround
Hi,
I have a report that needs to show data from 2 crosstab queries. One
query gets the count of the Jobs that a particular Installer has done
withen a time period. The other gets the count of faults that he has
done. The report has 2 grouping levels. Location, Zone and the
Employess.
SO for eg the report would be like this.
01/2006 02/2006 03/2006
Location 1
Zone 1
Installer 1
Jobs 14 12 9
Faults 5 6 7
Location 1
Zone 1
Installer 2
Jobs 4 2 10
Faults 7 10 1
Now the 2 queries can get different installers. For example there might
be an installer that did a job for that time but not any fault. so the
no of records in boths queries might be different. So what i am doing
is i select the location , zone and the employee name from the table
and assign it to the datasource of the report so that i can get the
desired grouping. then in the code i open the recordset for the both
jobs and faults. and in the detailsOnPrint event i assign the count of
jobs and the count of faults to the unbound text boxes for all the
employees. Now the problem is that the reocrd that the report picks up
from its recordsource might not be in one of the 2 recordset that i
open in the code. so the order gets all messed up and its shows the
wrong count for the wrong employee. I want to do something like
this....
The report gets a employee name from its recordsoure. In the OnPrint
method of the details section i want to find the record for that
employee if i find it then i want to get the count of jobs and assign
it to textbox if i cant find the employee then go to the next employee
in the report. I tired to use the find first method and compare it but
it does not seem to work. Actually its not my code i have to fix it.
The code is as follows. Any ideas. THanks
Dim strSearch As String
For intX = 5 To intColumnCount
On Error GoTo NextRecord:
'''''''''''''''''''''''''''' for employee Jobs fill the text boxes
rstJobsInst.FindFirst ("[Employee]='" & Me.Employee & "' and " &
"[KPIRowName] = 'Jobs'")
strSearch = rstJobsInst("Employee")
If (Me.Employee = strSearch) Then
If rstJobsInst.Fields.Count <= intX - 5 Then
Me("JI" + Format(intX - 4)) = "0"
Else
If intX = 1 Then
Else
If Not rstJobsInst.EOF Then
Me("JI" + Format(intX - 4)) = xtabCnulls(rstJobsInst(intX
- 1))
If (intX <> 5) Then
lngRgJobsInstTotal(intX - 5) = lngRgJobsInstTotal(intX
- 5) + Me("JI" + Format(intX - 4))
Else
strRowName(0) = Me("JI" + Format(intX - 4))
End If
Else
If (intX <> 5) Then
Me("JI" + Format(intX - 4)) = "0"
End If
End If
End If
End If
End If
'''''''''''''''''''''''''''' for employee fautls fill the text boxes
rstMaintsInst.FindFirst ("[Employee]='" & Me.Employee & "' and " &
"[KPIRowName] = 'faults'")
strSearch = rstMaintsInst("Employee")
If (Me.Employee = strSearch) Then
If rstMaintsInst.Fields.Count <= intX - 5 Then
Me("MaintInst" + Format(intX - 4)) = "0"
Else
If intX = 1 Then
Else
If Not rstMaintsInst.EOF Then
Me("MaintInst" + Format(intX - 4)) =
xtabCnulls(rstMaintsInst(intX - 1))
If (intX <> 5) Then
lngRgMaintsInstTotal(intX - 5) =
lngRgMaintsInstTotal(intX - 5) + Me("MaintInst" + Format(intX - 4))
Else
strRowName(1) = Me("MaintInst" + Format(intX - 4))
End If
Else
If (intX <> 5) Then
Me("MaintInst" + Format(intX - 4)) = "0"
End If
End If
End If
End If
Next intX
end sub
I have a report that needs to show data from 2 crosstab queries. One
query gets the count of the Jobs that a particular Installer has done
withen a time period. The other gets the count of faults that he has
done. The report has 2 grouping levels. Location, Zone and the
Employess.
SO for eg the report would be like this.
01/2006 02/2006 03/2006
Location 1
Zone 1
Installer 1
Jobs 14 12 9
Faults 5 6 7
Location 1
Zone 1
Installer 2
Jobs 4 2 10
Faults 7 10 1
Now the 2 queries can get different installers. For example there might
be an installer that did a job for that time but not any fault. so the
no of records in boths queries might be different. So what i am doing
is i select the location , zone and the employee name from the table
and assign it to the datasource of the report so that i can get the
desired grouping. then in the code i open the recordset for the both
jobs and faults. and in the detailsOnPrint event i assign the count of
jobs and the count of faults to the unbound text boxes for all the
employees. Now the problem is that the reocrd that the report picks up
from its recordsource might not be in one of the 2 recordset that i
open in the code. so the order gets all messed up and its shows the
wrong count for the wrong employee. I want to do something like
this....
The report gets a employee name from its recordsoure. In the OnPrint
method of the details section i want to find the record for that
employee if i find it then i want to get the count of jobs and assign
it to textbox if i cant find the employee then go to the next employee
in the report. I tired to use the find first method and compare it but
it does not seem to work. Actually its not my code i have to fix it.
The code is as follows. Any ideas. THanks
Dim strSearch As String
For intX = 5 To intColumnCount
On Error GoTo NextRecord:
'''''''''''''''''''''''''''' for employee Jobs fill the text boxes
rstJobsInst.FindFirst ("[Employee]='" & Me.Employee & "' and " &
"[KPIRowName] = 'Jobs'")
strSearch = rstJobsInst("Employee")
If (Me.Employee = strSearch) Then
If rstJobsInst.Fields.Count <= intX - 5 Then
Me("JI" + Format(intX - 4)) = "0"
Else
If intX = 1 Then
Else
If Not rstJobsInst.EOF Then
Me("JI" + Format(intX - 4)) = xtabCnulls(rstJobsInst(intX
- 1))
If (intX <> 5) Then
lngRgJobsInstTotal(intX - 5) = lngRgJobsInstTotal(intX
- 5) + Me("JI" + Format(intX - 4))
Else
strRowName(0) = Me("JI" + Format(intX - 4))
End If
Else
If (intX <> 5) Then
Me("JI" + Format(intX - 4)) = "0"
End If
End If
End If
End If
End If
'''''''''''''''''''''''''''' for employee fautls fill the text boxes
rstMaintsInst.FindFirst ("[Employee]='" & Me.Employee & "' and " &
"[KPIRowName] = 'faults'")
strSearch = rstMaintsInst("Employee")
If (Me.Employee = strSearch) Then
If rstMaintsInst.Fields.Count <= intX - 5 Then
Me("MaintInst" + Format(intX - 4)) = "0"
Else
If intX = 1 Then
Else
If Not rstMaintsInst.EOF Then
Me("MaintInst" + Format(intX - 4)) =
xtabCnulls(rstMaintsInst(intX - 1))
If (intX <> 5) Then
lngRgMaintsInstTotal(intX - 5) =
lngRgMaintsInstTotal(intX - 5) + Me("MaintInst" + Format(intX - 4))
Else
strRowName(1) = Me("MaintInst" + Format(intX - 4))
End If
Else
If (intX <> 5) Then
Me("MaintInst" + Format(intX - 4)) = "0"
End If
End If
End If
End If
Next intX
end sub