Using a recordset as a reports data source

[GLepage]

I am trying to use a recordset as a reports data source, in the report
open event I have the line:
Me.RecordSource = rstUserReport.Name

In a standard module:
Option Compare Database
'***********************************************
'Developed & Written by Gene Lepage
'Alstom - Power
'Boliers NAM
'All Rights Reserved.
'
'Adapted from Code Courtesy
'of Andy Baron
'
'In: Dates and up to 3 WHERE statements
'Out: Recordsource for rptUserReport
'
'Created: 4/16/2007
'
'Modified:
'***********************************************

'Declare public recordset variable
Public rstUserReport As DAO.Recordset

'Supply the report with a recordset object based on SQL statment with
user selected cirteria
Public Sub RunUserReport()
Dim strTerm1 As String, strTerm2 As String, strTerm3 As String
Dim dtStart As Date, dtEnd As Date
Dim strSQL As String, strWhere As String, lngCount As Long

'Get the start and end dates for the report
dtStart = Forms![frmSelectRptsAdvanced].[txtStartDate]
dtEnd = Forms![frmSelectRptsAdvanced].[txtEndDate]

'Get the user selected cirteria for the report
strTerm1 = Forms![frmSelectRptsAdvanced].[txtResult1]
strTerm2 = Forms![frmSelectRptsAdvanced].[txtResult2]
strTerm3 = Forms![frmSelectRptsAdvanced].[txtResult3]

'Determine how many field the user selected for the report
lngCount = 0
If strTerm1 = "" Then
lngCount = 0
Else
lngCount = 1
End If

If strTerm2 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

If strTerm3 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

Select Case lngCount
Case Is = 1 'One field used
strWhere = "WHERE (" & strTerm1
Case Is = 2 'Two fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2
Case Is = 3 'Three fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2 & "AND
" & strTerm3
Case Else
Exit Sub
End Select

'Build SQL statement to use as the recordset object
strSQL = "SELECT [PowerSolve All Issues].[Contract Name],
[PowerSolve All Issues].[Ref #], [PowerSolve All Issues].[Issue
Title], [PowerSolve All Issues].[Orig/PM Est], [PowerSolve All Issues].
[Actual Cost], [PowerSolve All Issues].Responsibility, [PowerSolve All
Issues].Status, [PowerSolve All Issues].[Supplier Name], [PowerSolve
All Issues].[WO #], [PowerSolve All Issues].Created, [PowerSolve All
Issues].[Disposition Type], [PowerSolve All Issues].[Problem Desc],
[PowerSolve All Issues].[Technical Resolution], [PowerSolve All
Issues].[Commercial Resolution], [PowerSolve All Issues].[Ref Plant],
[PowerSolve All Issues].[Request Type] "
strSQL = strSQL + "FROM [PowerSolve All Issues] "
strSQL = strSQL + strWhere
strSQL = strSQL + "AND (([PowerSolve All Issues].[Created])
BETWEEN #" & dtStart & "# AND #" & dtEnd & "#));"


'Open the recordset
Set rstUserReport = CurrentDb.OpenRecordset(strSQL)

'Open the report
DoCmd.OpenReport "rptUserReport", acViewPreview

'Reset counter and destroy objects
lngCount = 0
rstUserReport.Close
Set rstUserReport = Nothing

End Sub

I can move through the recordset in code (approx 345 records returned
by this SQL) but when the report tyies to open I get the error
message:

Run-Time Error 2580 - The recordsource specified on this form or
report does not exist.

If I debug.print the rstUserReport.Name I get just a small part of the
SQL statement.

What am I doing wrong - please be gentle it's my first post.

 

[Larry Linson]

Help me understand why you think it advantageous to open a Recordset and use
it as the RecordSource of the Report, instead of just replacing the Report's
RecordSource with the SQL? Alternatively, you can just replace the SQL
Property of a saved Query that you use as the Report's Recordsource before
doing the DoCmd.OpenReport.

Andy Baron is a well-respected, sharp Access developer / consultant, but
some of what he publishes is for the purpose of demonstrating new features
and functions, rather than a recommendation for practical use.

But, if you feel compelled to use a Recordset, have you validated that the
Report's RecordSource is properly referring to the Recordset? Set a
Breakpoint in the Report's Open event and check.

Frankly, I have never found good reason to use a Recordset in this way in
any of my Access work, except to verify that it can be done, and that the
new feature works; there are, presumably, some occasions when it would be
useful, particularly for ADO Disconnected Recordsets which have been
manipulated in code prior to their use in the Report.

Larry Linson
Microsoft Access MVP

I am trying to use a recordset as a reports data source, in the report
open event I have the line:
Me.RecordSource = rstUserReport.Name

In a standard module:
Option Compare Database
'***********************************************
'Developed & Written by Gene Lepage
'Alstom - Power
'Boliers NAM
'All Rights Reserved.
'
'Adapted from Code Courtesy
'of Andy Baron
'
'In: Dates and up to 3 WHERE statements
'Out: Recordsource for rptUserReport
'
'Created: 4/16/2007
'
'Modified:
'***********************************************

'Declare public recordset variable
Public rstUserReport As DAO.Recordset

'Supply the report with a recordset object based on SQL statment with
user selected cirteria
Public Sub RunUserReport()
Dim strTerm1 As String, strTerm2 As String, strTerm3 As String
Dim dtStart As Date, dtEnd As Date
Dim strSQL As String, strWhere As String, lngCount As Long

'Get the start and end dates for the report
dtStart = Forms![frmSelectRptsAdvanced].[txtStartDate]
dtEnd = Forms![frmSelectRptsAdvanced].[txtEndDate]

'Get the user selected cirteria for the report
strTerm1 = Forms![frmSelectRptsAdvanced].[txtResult1]
strTerm2 = Forms![frmSelectRptsAdvanced].[txtResult2]
strTerm3 = Forms![frmSelectRptsAdvanced].[txtResult3]

'Determine how many field the user selected for the report
lngCount = 0
If strTerm1 = "" Then
lngCount = 0
Else
lngCount = 1
End If

If strTerm2 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

If strTerm3 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

Select Case lngCount
Case Is = 1 'One field used
strWhere = "WHERE (" & strTerm1
Case Is = 2 'Two fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2
Case Is = 3 'Three fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2 & "AND
" & strTerm3
Case Else
Exit Sub
End Select

'Build SQL statement to use as the recordset object
strSQL = "SELECT [PowerSolve All Issues].[Contract Name],
[PowerSolve All Issues].[Ref #], [PowerSolve All Issues].[Issue
Title], [PowerSolve All Issues].[Orig/PM Est], [PowerSolve All Issues].
[Actual Cost], [PowerSolve All Issues].Responsibility, [PowerSolve All
Issues].Status, [PowerSolve All Issues].[Supplier Name], [PowerSolve
All Issues].[WO #], [PowerSolve All Issues].Created, [PowerSolve All
Issues].[Disposition Type], [PowerSolve All Issues].[Problem Desc],
[PowerSolve All Issues].[Technical Resolution], [PowerSolve All
Issues].[Commercial Resolution], [PowerSolve All Issues].[Ref Plant],
[PowerSolve All Issues].[Request Type] "
strSQL = strSQL + "FROM [PowerSolve All Issues] "
strSQL = strSQL + strWhere
strSQL = strSQL + "AND (([PowerSolve All Issues].[Created])
BETWEEN #" & dtStart & "# AND #" & dtEnd & "#));"


'Open the recordset
Set rstUserReport = CurrentDb.OpenRecordset(strSQL)

'Open the report
DoCmd.OpenReport "rptUserReport", acViewPreview

'Reset counter and destroy objects
lngCount = 0
rstUserReport.Close
Set rstUserReport = Nothing

End Sub

I can move through the recordset in code (approx 345 records returned
by this SQL) but when the report tyies to open I get the error
message:

Run-Time Error 2580 - The recordsource specified on this form or
report does not exist.

If I debug.print the rstUserReport.Name I get just a small part of the
SQL statement.

What am I doing wrong - please be gentle it's my first post.

 

[GLepage]

Help me understand why you think it advantageous to open a Recordset and use
it as the RecordSource of the Report, instead of just replacing the Report's
RecordSource with the SQL? Alternatively, you can just replace the SQL
Property of a saved Query that you use as the Report's Recordsource before
doing the DoCmd.OpenReport.

Andy Baron is a well-respected, sharp Access developer / consultant, but
some of what he publishes is for the purpose of demonstrating new features
and functions, rather than a recommendation for practical use.

But, if you feel compelled to use a Recordset, have you validated that the
Report's RecordSource is properly referring to the Recordset? Set a
Breakpoint in the Report's Open event and check.

Frankly, I have never found good reason to use a Recordset in this way in
any of my Access work, except to verify that it can be done, and that the
new feature works; there are, presumably, some occasions when it would be
useful, particularly for ADO Disconnected Recordsets which have been
manipulated in code prior to their use in the Report.

Larry Linson
Microsoft Access MVP

I am trying to use a recordset as a reports data source, in the report
open event I have the line:
Me.RecordSource = rstUserReport.Name

In a standard module:
Option Compare Database
'***********************************************
'Developed & Written by Gene Lepage
'Alstom - Power
'Boliers NAM
'All Rights Reserved.
'
'Adapted from Code Courtesy
'of Andy Baron
'
'In: Dates and up to 3 WHERE statements
'Out: Recordsource for rptUserReport
'
'Created: 4/16/2007
'
'Modified:
'***********************************************

'Declare public recordset variable
Public rstUserReport As DAO.Recordset

'Supply the report with a recordset object based on SQL statment with
user selected cirteria
Public Sub RunUserReport()
Dim strTerm1 As String, strTerm2 As String, strTerm3 As String
Dim dtStart As Date, dtEnd As Date
Dim strSQL As String, strWhere As String, lngCount As Long

'Get the start and end dates for the report
dtStart = Forms![frmSelectRptsAdvanced].[txtStartDate]
dtEnd = Forms![frmSelectRptsAdvanced].[txtEndDate]

'Get the user selected cirteria for the report
strTerm1 = Forms![frmSelectRptsAdvanced].[txtResult1]
strTerm2 = Forms![frmSelectRptsAdvanced].[txtResult2]
strTerm3 = Forms![frmSelectRptsAdvanced].[txtResult3]

'Determine how many field the user selected for the report
lngCount = 0
If strTerm1 = "" Then
lngCount = 0
Else
lngCount = 1
End If

If strTerm2 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

If strTerm3 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If

Select Case lngCount
Case Is = 1 'One field used
strWhere = "WHERE (" & strTerm1
Case Is = 2 'Two fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2
Case Is = 3 'Three fields used
strWhere = "WHERE (" & strTerm1 & "AND " & strTerm2 & "AND
" & strTerm3
Case Else
Exit Sub
End Select

'Build SQL statement to use as the recordset object
strSQL = "SELECT [PowerSolve All Issues].[Contract Name],
[PowerSolve All Issues].[Ref #], [PowerSolve All Issues].[Issue
Title], [PowerSolve All Issues].[Orig/PM Est], [PowerSolve All Issues].
[Actual Cost], [PowerSolve All Issues].Responsibility, [PowerSolve All
Issues].Status, [PowerSolve All Issues].[Supplier Name], [PowerSolve
All Issues].[WO #], [PowerSolve All Issues].Created, [PowerSolve All
Issues].[Disposition Type], [PowerSolve All Issues].[Problem Desc],
[PowerSolve All Issues].[Technical Resolution], [PowerSolve All
Issues].[Commercial Resolution], [PowerSolve All Issues].[Ref Plant],
[PowerSolve All Issues].[Request Type] "
strSQL = strSQL + "FROM [PowerSolve All Issues] "
strSQL = strSQL + strWhere
strSQL = strSQL + "AND (([PowerSolve All Issues].[Created])
BETWEEN #" & dtStart & "# AND #" & dtEnd & "#));"

'Open the recordset
Set rstUserReport = CurrentDb.OpenRecordset(strSQL)

'Open the report
DoCmd.OpenReport "rptUserReport", acViewPreview

'Reset counter and destroy objects
lngCount = 0
rstUserReport.Close
Set rstUserReport = Nothing

End Sub

I can move through the recordset in code (approx 345 records returned
by this SQL) but when the report tyies to open I get the error
message:

Run-Time Error 2580 - The recordsource specified on this form or
report does not exist.

If I debug.print the rstUserReport.Name I get just a small part of the
SQL statement.

What am I doing wrong - please be gentle it's my first post.- Hide quoted text -

- Show quoted text -

Thank you for responding. I changed the recordsource property of the
report to just the SQL statement built by the procedure and it works
as desired.

The intent of this report is to allow the user to select up to 4
different fields to search on and use cirteria like <,>,=, Like, Not
and a typed-in search term.

Gene Lepage

 

[Larry Linson]

Thank you for responding. I changed the record-
source property of the report to just the SQL
statement built by the procedure and it works
as desired.

I'm glad you got it working as desired.

The intent of this report is to allow the user to
select up to 4 different fields to search on and
use cirteria like <,>,=, Like, Not
and a typed-in search term.

It's easy to get "carried away" with user-specified criteria. I worked on
one system where the original authors had included a (very extensive)
criteria form that was used with [every | almost every] report. There were
so many choices that it was easy for a user to add criteria that eliminated
every record -- it was the number one "pain" for user support calls.

Larry Linson
Microsoft Access MVP