Selection criteria not working!!!

[blakemckenna]

I am using CR 8.5. I have a TimeSheet report in which I only want to report on the selected employee. For test purposes, I have 14 records of one employee and 1 record for another employee. However, my report shows all the records instead of the employee I select. I don't know if I need to adjust the setting in the report or my SQL statement. I have attached my code below.

Private Sub PrintTimeSheet()
    Dim oApp As CRAXDRT.Application
    Dim oReport As CRAXDRT.Report
 
    mSQL = "Select  A.*, " & _
                   "B.*, " & _
                   "C.* " & _
             "From tblTime A, " & _
                  "tblPayPeriods B, " & _
                  "tblProjects C " & _
            "Where (A.ppID = B.ppID) and " & _
                  "(A.projectID = C.prjInternalProjID) and" & _
                  "(A.employee = '" & mstrEmployee & "') " & _
            "Order By A.employee, A.projectID"
 
    If OpenDBConnection Then
        Set rs = New ADODB.Recordset
        Set rs = cn.Execute(mSQL)
        Set oApp = New CRAXDRT.Application
        Set oReport = oApp.OpenReport("C:\Reports\rptTimeSheet.rpt", 1)
        oReport.Database.SetDataSource rs, 3, 1
        frmReport.cr1.ReportSource = oReport
        frmReport.cr1.ViewReport
    End If
End Sub

The key is the mstrEmployee field. That value is what makes the entire report unique.

Thanks,

-Blake

[pnish]

Make sure you don't have Save Data With Report checked in your report. You'll find that option either on the File menu or under Report Options on the file menu.

If that doesn't help, check how many records you're passing to the report in the recordset and make sure it only contains the record(s) you want.

[blakemckenna]

Pete,

This think isn't even working at all now. I specified a new query with a date parameter in it and it still pulls all the records when it shouldn't pull any now. Not sure what I'm doin' wrong. Here's is my current SQL statement:

Private Sub PrintTimeSheet()
    Dim oApp As CRAXDRT.Application
    Dim oReport As CRAXDRT.Report
 
    mSQL = "Select  A.*, " & _
                   "B.*, " & _
                   "C.* " & _
             "From tblTime A, " & _
                  "tblPayPeriods B, " & _
                  "tblProjects C " & _
            "Where (A.sessionDate = #" & Format(Now, "mm/dd/yyyy") & "#) and " & _
                  "(A.ppID = B.ppID) and " & _
                  "(A.projectID = C.prjInternalProjID) and " & _
                  "(A.employee = '" & mstrEmployee & "') " & _
            "Order By A.employee, A.projectID"
    
    Debug.Print mSQL
    
    If OpenDBConnection Then
        Set oApp = New CRAXDRT.Application
        Set rs = New ADODB.Recordset
        rs.Open mSQL, cn, adOpenKeyset, adLockPessimistic
        Set oReport = oApp.OpenReport("C:\Reports\rptTimeSheet.rpt", 1)
        oReport.Database.SetDataSource rs, 3, 1
        frmReport.cr1.ReportSource = oReport
        frmReport.cr1.ViewReport
    End If
End Sub

Thanks,

Blake

[pnish]

When you say 'it pulls all the records' do you mean the recordset or the report? Does your report also have a connection to the database or is it only getting its data from the recordset you're passing?

When you execute your query, how many records does it contain? If it contains no records but your report is still displaying all the records, it means either you have saved the data with the report (as I mentioned before) or the report is getting the data from somewhere else, ie directly from a database connection.

One thing you could try so you can see where the data's coming from would be to make a copy of your database, totally change the data in the tables and execute your query off this copied database. Does the report show the new, changed data or is it still showing the original data?

[blakemckenna]

Pete,

Your idea is a good one and I'm going to try it. However, I now have another problem with the same report. I get the error "Server has not yet been opened". It just started so I don't know exactly what I did wrong. Any ideas on this one?

Thanks,

[pnish]

I haven't seen that one before. Ae you using a secured Access database?
If you want to zip up your project and send it to me I could take a look for you. Don't forget to include the database and .rpt file.

[blakemckenna]

Pete,

I know how to reply with an email...but how do you send attachments?

[VBFnewcomer]

Scroll down this page you will find "Go Advanced" button, Click. In the next page scroll down, you will find another button "Manage Attachments" Click. In the following page you will find a button "Browse", click and select the file to upload. Then click "Upload" button. After you see your files in the window, click "Close Window". Now your files are attached to your post. Note: Not all types of files can be uploaded. The supported files list is in the window where you find "Upload" button.

If u wnat to attach files from ur email client like, yahoo, Gmail, etc. then u will find button somewhere which says something like "Attach files" after u click on the compose button.
Hope I was helpful.