I have a report that is designed to 1

[kentwoodjean]

I have a report that is designed to list the specific projects employees work on that meet the query criteria of project numbers 901, 902, 704, 708. Just doing this runs the report correctly. This, however, is not how I get to the report. From the switchboard, the user selects the report button. As a result, a form opens with a drop down list of employees along with date fields that automatically populate with the 1st and last dates the employee worked on the projects. Once the employee is selected from the form, the button "preview report" is pushed and the employee report opens. Problem is that the report output does not match the criteria requested. It pulls all the projects, not just the numbered ones indicated. Below is the class module for the "Preview Report" button from the form. If there is something I need to add to this to get my criteria, can you please advise what and where? Thanks.


"Preview Report" Button

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stDocName As String
Dim StrWhere As String

Dim varCheck As Variant

If IsNull(Me.cboCSR) Or IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox ("You must enter all criteria&quot
Exit Sub
End If

varCheck = DCount("CSR", "ProjLog", "CSR = '" & Me.cboCSR & "' And " & _
"([Start Date] Between #" & Me.StartDate & "# And #" & Me.EndDate & "# Or " & _
"[End Date] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)&quot
If IsNull(varCheck) Then
MsgBox ("There's nothing in the report.&quot
Exit Sub
End If
' Launch the report
stDocName = "701/901"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

 

[KenReay]

Hi

It is necessary to know:

The recordsource of the report (ie the query definition)

Is there any code in the on open event of the report to retrict or change the recordsource

because these is nothing in the code you posted to act upon the criteria entered

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kentwoodjean]

Below is the SQL for the query that produces the report:

SELECT ProjLog.CSR, ProjLog.[Start Date], ProjLog.[End Date], ProjLog.[Num Claims Rec], ProjLog.[Num Claims Proc], ProjLog.[Time Used], ProjName.[Project Name], ProjLog.[Num Claims Rev]
FROM ProjLog INNER JOIN ProjName ON ProjLog.[Project Name] = ProjName.[Project Name]
WHERE (((ProjLog.CSR)=[Forms]![SelectEmp]![cbocsr]) AND ((ProjLog.[Start Date]) Between [Forms]![SelectEmp]![StartDate] And [Forms]![SelectEmp]![EndDate]) AND ((ProjName.[Project Name])="701" Or (ProjName.[Project Name])="703" Or (ProjName.[Project Name])="705" Or (ProjName.[Project Name])="901&quot) OR (((ProjLog.CSR)=[Forms]![SelectEmp]![cbocsr]) AND ((ProjLog.[Start Date]) Between [Forms]![SelectEmp]![StartDate] And [Forms]![SelectEmp]![EndDate]));


Also, here is the code for the open event of the report, which immediately directs you to the form where the employee is selected after which time the "Preview Report" key is selected.

Private Sub Report_Open(Cancel As Integer)
'Open the form for name input
DoCmd.OpenForm "SelectEmp", acNormal, , , acFormEdit, acDialog
End Sub

Hopefully this will assist in determining why I cannot obtain the specific items I have set in my parameters.

 

[KenReay]

Hi

Interesting, I have not seen it done that way before, I normally run the report by first openning the form, allowing the user to input the criteria and then having a button on the form which runs the report, I have not tried openning the form from within the report onOpen event

Would you try the following

Comment out the line DoCmd.OpenForm "SelectEmp", acNormal, , , acFormEdit, acDialog in the report on open event

run the form and make the selections you require

with the form still opne, run the report

does it work as you would expect?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kentwoodjean]

I tried as you suggested and it still does not pull the information correctly.

The query that the report is populated from has the following information as a result of the drop down box on the form for CSR and and the auto population of start Date and end date fields based on the CSR selected. I have indicated below the criteria which is on the form for these fields. By eliminating these criteria, the report runs as it should.


Field - CSR
Criteria - [Forms]![SelectEmp]![cbocsr]
or [Forms]![SelectEmp]![cbocsr]


Start Date - Between [Forms]![SelectEmp]![StartDate] And [Forms]![SelectEmp]![EndDate] or
Between [Forms]![SelectEmp]![StartDate] And [Forms]![SelectEmp]![EndDate]

 

[KenReay]

Hi

Sorry, I do not understand what you are telling me in your last post,

but if I may suggest,

If you do it 'my' way, the thing to do is to run the query, ensure it selects the data you want, keep adjusting the query parameters until you haev it right, then teh report is simply presentation and should be no problem

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kentwoodjean]

You are right. Your way works just fine. thanks for your patience and help.