Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Recordset from Recordset and Pass Recordset to Report

Posted on 2008-06-11
9
Medium Priority
?
1,464 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

598 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