Using "rs" as the report base (1 Viewer)

[lcook1974]

Hey everyone,

I playing around with search forms and would like to know if you it is possible to use the declared rs (recordset) as the base for the report.

the Docmd.OpenReport doesn't seem to support this or i'm doing it wrong.

Probably the latter.

The rptPatientInfo is one query with no expressions in it.

How do I set the rptPatientinfo query to rs?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From dbo_PatientInfo WHERE '" & strWhere & "'")
If Not IsNull(Me.cmbHosp) Then
    strWhere = "[ReferOrg] = '" & Me.cmbHosp.Column(0) & "'" & " AND "
End If

If Not IsNull(Me.cmbOrganOutcome) Then
    strWhere = "[OrganOutcome] = '" & Me.cmbOrganOutcome.Column(0) & "'" & "  AND "
End If
strWhere = Left(strWhere, Len(strWhere) - 5)

DoCmd.OpenReport "rptPatientInfo", acViewPreview, , strWhere

Larry

 

[DCrake]

You seem to have everything out of sync

For a start

Set rs = db.OpenRecordset("Select * From dbo_PatientInfo WHERE '" & strWhere & "'")

Will cause it to error because strWhere does not contain anything

If Not IsNull(Me.cmbHosp) Then
    strWhere = "[ReferOrg] = '" & Me.cmbHosp.Column(0) & "'" & " AND "
End If

If ReferOrg is a number then it does not need wrapping in quotes.

If Not IsNull(Me.cmbOrganOutcome) Then
    strWhere = "[OrganOutcome] = '" & Me.cmbOrganOutcome.Column(0) & "'" & "  AND "
End If

is overwriting your previous strWhere

Should Be

If Not IsNull(Me.cmbOrganOutcome) Then
    strWhere = [B]strWhere &[/B] "[OrganOutcome] = '" & Me.cmbOrganOutcome.Column(0) & "'" & "  AND "
End If

strWhere = Left(strWhere, Len(strWhere) - 5)

This is ok

DoCmd.OpenReport "rptPatientInfo", acViewPreview, , strWhere

Ok

 

[lcook1974]

Thanks David!! i was wondering why the ReferOrg wasn't showing up correctly...

So how would I set the report to the rs in the VBA? right now it's set to a query...

or do I even need to do that?

 

[DCrake]

Just as you have now.
DoCmd.OpenReport "rptPatientInfo", acViewPreview, , strWhere
You do not need the RS as you are applying the filter condition to the report using the strWhere.

 

[lcook1974]

Awesome! Thank you David!!!

 

[lcook1974]

Hey David,
How would I send the same report to Excel? When I put this in I get an "empty" excel sheet but the report shows just fine.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String
Dim cn As ADODB.Connection
Dim pad As String
    
    Set cn = New ADODB.Connection
    Set db = CurrentDb()
    pad = CurrentProject.path
    If Right(pad, 1) <> "\" Then pad = pad & "\"
 
'it runs but gives me a blank spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrptPatientInfo", pad & "PatientInfo.xls"

Larry