Solved

Create Recordset from Recordset and Pass Recordset to Report

Posted on 2008-06-11
9
1,416 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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