Solved

How to obtain SQL Statement from recordset

Posted on 2011-03-18
12
384 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
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.

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

mx
0
 
LVL 33

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 33

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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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