Using a recordset as a reports data source

G

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.
 
L

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

GLepage said:
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.
Click to expand...
 
G

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
Click to expand...
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:
'***********************************************
Click to expand...
'Declare public recordset variable
Public rstUserReport As DAO.Recordset
Click to expand...
'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
Click to expand...
'Get the start and end dates for the report
dtStart = Forms![frmSelectRptsAdvanced].[txtStartDate]
dtEnd = Forms![frmSelectRptsAdvanced].[txtEndDate]
Click to expand...
'Get the user selected cirteria for the report
strTerm1 = Forms![frmSelectRptsAdvanced].[txtResult1]
strTerm2 = Forms![frmSelectRptsAdvanced].[txtResult2]
strTerm3 = Forms![frmSelectRptsAdvanced].[txtResult3]
Click to expand...
'Determine how many field the user selected for the report
lngCount = 0
If strTerm1 = "" Then
lngCount = 0
Else
lngCount = 1
End If
Click to expand...
If strTerm2 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If
Click to expand...
If strTerm3 = "" Then
lngCount = lngCount
Else
lngCount = lngCount + 1
End If
Click to expand...
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
Click to expand...
'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 & "#));"
Click to expand...
'Open the recordset
Set rstUserReport = CurrentDb.OpenRecordset(strSQL)
Click to expand...
'Open the report
DoCmd.OpenReport "rptUserReport", acViewPreview
Click to expand...
'Reset counter and destroy objects
lngCount = 0
rstUserReport.Close
Set rstUserReport = Nothing
Click to expand...
End Sub
Click to expand...
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:
Click to expand...
Run-Time Error 2580 - The recordsource specified on this form or
report does not exist.
Click to expand...
If I debug.print the rstUserReport.Name I get just a small part of the
SQL statement.
Click to expand...
What am I doing wrong - please be gentle it's my first post.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
L

Larry Linson

GLepage said:
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.
Click to expand...

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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report SQL Statement Issue 4
Recordset findfirst 2
Return table fieldname on recordset 1
Skip password protect files and open non protected files 9
coding help - please 9
Exporting data to multiple textfiles 1
ADO Recordset Issues on Open Method 0
Query by Form Text Box String 0

Top