Solved

Create Recordset from Recordset and Pass Recordset to Report

Posted on 2008-06-11
9
1,448 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
Industry Leaders: 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!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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