Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1476
  • Last Modified:

Create Recordset from Recordset and Pass Recordset to Report

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
stevensont
Asked:
stevensont
  • 5
  • 3
1 Solution
 
NizzeKCommented:
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
 
stevensontAuthor Commented:
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
 
stevensontAuthor Commented:
Increasing points.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
stevensontAuthor Commented:
Thanks...I will test both within a few days. I'm on vacation now.
0
 
stevensontAuthor Commented:
I have NOT forgotten about this question. I'll test this solution the week of June 30th when I return to work.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK
0
 
stevensontAuthor Commented:
I think this is the right thing to do!
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now