Solved

Create Recordset from Recordset and Pass Recordset to Report

Posted on 2008-06-11
9
1,441 Views
Last Modified: 2012-05-05
I have not coded anything yet, but was wondering if it was at all possible to use recordset and create another recordset based on additional parameters. It will not be a clone of the original recordset. Once I have narrowed down the recordset, I was thinking of passing the recordset to a report versus creating a temporary table for the report due to the overhead associated the database expanding.
0
Comment
Question by:stevensont
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 3

Expert Comment

by:NizzeK
ID: 21762145
Hi!
In help you can find an example, se attachment.
You can for instance create a query and get the recordset, and then make a new narrower query on that and get the recordset again.
The function CopyQueryNew() has parameters recordset and SQL clause.
It will add the filters etc to the original ones (if any)
Is this what you was looking for?

Best regards
Nils.

Function CopyQueryNew(rstTemp As Recordset, _
    strAdd As String) As QueryDef
 
    Dim strSQL As String
    Dim strRightSQL As String
 
    Set CopyQueryNew = rstTemp.CopyQueryDef
    With CopyQueryNew
        ' Strip extra characters.
        strSQL = .SQL
        strRightSQL = Right(strSQL, 1)
        Do While strRightSQL = " " Or strRightSQL = ";" Or _
                strRightSQL = Chr(10) Or strRightSQL = vbCr
            strSQL = Left(strSQL, Len(strSQL) - 1)
            strRightSQL = Right(strSQL, 1)
        Loop
        .SQL = strSQL & strAdd
    End With
 
End Function
 
Set qdfCopy = CopyQueryNew(rstEmployees, strOrderBy)

Open in new window

0
 

Author Comment

by:stevensont
ID: 21773331
No...that's not it. I'm looking to start with a complete recordset and append records to an empty recordset. My biggest problem is our SQL backend database which has 800K records. Since the Access DB is on a network and used by more than 35 people, I have elected to use VBA to link to the SQL DB. I can use limited parameters to query the SQL DB and create a recordset. However, based on user input, I need to refine the recordset. I was hoping to be able to append to an empty record and then pass the recordset onto a report.
0
 

Author Comment

by:stevensont
ID: 21777889
Increasing points.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 21800032
stevensont,

I am still a little unclear as to why you need to feed one recordset into an empty recordset?

But if your basic question is how to feed a recordset with criteria into an empty recordset, here is some sample code:

Dim rstQueryRecords As DAO.Recordset
Dim rstEmpty As DAO.Recordset
Set rstQueryRecords = CurrentDb.OpenRecordset("SELECT * FROM tblDetailVideos WHERE Category='Drama'")
Set rstEmpty = CurrentDb.OpenRecordset("tblEmpty")

    rstQueryRecords.MoveFirst
   
    Do While Not rstQueryRecords.EOF
        With rstEmpty
            .AddNew
            !MovieTitle = rstQueryRecords!MovieTitle
            !Category = rstQueryRecords!Category
            .Update
            rstQueryRecords.MoveNext
        End With
    Loop
   
    MsgBox "Finished loading Recordset.", vbInformation

'Cleanup
rstQueryRecords.Close
Set rstQueryRecords = Nothing
rstEmpty.Close
Set rstEmpty = Nothing


To use a Recordset as the RecordSource for a report, see this link:
http://www.mvps.org/access/reports/rpt0014.htm

JeffCoachman

0
 

Author Comment

by:stevensont
ID: 21805994
Thanks...I will test both within a few days. I'm on vacation now.
0
 

Author Comment

by:stevensont
ID: 21864579
I have NOT forgotten about this question. I'll test this solution the week of June 30th when I return to work.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21866841
OK
0
 

Author Closing Comment

by:stevensont
ID: 31466144
I think this is the right thing to do!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21922868
;-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question