Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to obtain SQL Statement from recordset

Posted on 2011-03-18
12
Medium Priority
?
415 Views
Last Modified: 2012-05-11
Hi Guys, this has got to be simple but it's alluded me so far.
How do I get the SQL statement from a recordset object?

I'm wanting to set it to a variable but I don't know what the property name is for the SQL Text....

dim strSQL as string
strSQL = Me.RecordsetClone.sqltext

Thanks in advance,

DataFlowJoe
0
Comment
Question by:dataflowjoe
  • 5
  • 5
  • 2
12 Comments
 
LVL 75
ID: 35169200
From the RecordsetClone ?

Don't think you can.  But ... in general you can do this:

Dim strSQL As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("SomeSavedQueryName")
strSQL = qdf.sql
MsgBox strSQL

mx
0
 
LVL 75
ID: 35169231
The RecordsetClone may just be the clone of a Table - that is the Recordsource.

mx
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169308
The recordsetclone that I'm trying to obtain the sql statement from is in a subform which is also filtered by the Link Master Field.
If I could get the sql text from the recordsetclone I could then pass this into a report by using the onopen event to give me the same listing of records which could be printed out.
It would be great if I could set the recordsetclone object to a variable and pass this into the report recordsource but I know this can't be done. However if I had the SQL statement then this can be passed into the report through the onopen report event.

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 75
ID: 35169322
What is the Record Source of the Form ?

mx
0
 
LVL 35

Expert Comment

by:Norie
ID: 35169358
Why do you need the SQL?

Can't you set the report up to use a parameter query that takes values from the form/sub-form?
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169381
The recordsource is dynamically created depending on what options the user clicks. The options filter an activity list based on 2 date parameters and then this is further filtered automatically by virtue of the Link Master Field. The recordsourceclone object contains the data I need but I can't get at the SQL statement.
I can loop through the recordsourceclone and manipulate the data programatically. I thought it would be a simple matter to extract the SQL statement from it.... obviously not!

Thanks
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169405
Why do you need the SQL?

Can't you set the report up to use a parameter query that takes values from the form/sub-form?

Mmmh good point, is there a way of assigning the report recordsource to be the same as the subform?
0
 
LVL 75
ID: 35169419
The only reason to use the RecordsetClone would be if you have further filtered the subform by using the built in Right Click options - Filter By, Filter Excluding ... etc, which would be reflected in the RecordsetClone.  I do this all the time.  However, if this is the case, you can use the same general Recordsource for the Report ... then set the Report to the Filter property of the Subform ... basically.

   
    Dim sRpt As String
    Dim rpt  As Report
    Dim sFilter As String
     sFilter = Me.fsub1.Form.Filter    ' example = setting from Main form
    sRpt = "YourReportName"
   
    DoCmd.OpenReport sRpt, acViewPreview
    Set rpt = Reports(sRpt)
    DoEvents
    With rpt
         .Visible = False
         
         .Filter = sFilter
         .FilterOn = True
         .Visible = True
    End With
   
    Set rpt = Nothing

0
 
LVL 35

Expert Comment

by:Norie
ID: 35169428
Are you saying the recordsource for the subform is dynamically created?

If it is why can't you dynamically create the recordsource for the report?

Mind you I dont' quite see why you would be dynamically creating the recordsource for the subform - perhaps I'm missing something.
0
 
LVL 2

Author Comment

by:dataflowjoe
ID: 35169490
The subform is automatically filtered by the Master Link Field I was hoping to pass that into the report as well. Basically whatever the subform recordsetclone is, that's what i need for the report recordsource. Hence the thought that there maybe an SQLText property associated with the recordsetclone or even simply assign the recordsetclone to the recordsource of the report like the following...

Private Sub Report_Open(Cancel As Integer)
On Error Resume Next
    Me.RecordSource = Forms![frmUsers]![frmCompanyActivities].Form.recordsetclone
End Sub
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1500 total points
ID: 35176304
The reason this does not work ...

Me.RecordSource = Forms![frmUsers]![frmCompanyActivities].Form.recordsetclone

is because the RecordSource property is a String value ... query/table name or an SQL statement.
RecordsetClone is an Object, and does not happen to have an SQL property ... as does say the QueryDef object.

I would suggest trying what I posted @ http:#a35169419  ... which I have used in many places.

mx

0
 
LVL 2

Author Closing Comment

by:dataflowjoe
ID: 35335771
Not quite what I was looking for but it pointed me in the right direction
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

877 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