[Webinar] Streamline your web hosting managementRegister Today

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

How to obtain SQL Statement from recordset

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
dataflowjoe
Asked:
dataflowjoe
  • 5
  • 5
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The RecordsetClone may just be the clone of a Table - that is the Recordsource.

mx
0
 
dataflowjoeAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What is the Record Source of the Form ?

mx
0
 
NorieVBA ExpertCommented:
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
 
dataflowjoeAuthor Commented:
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
 
dataflowjoeAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
NorieVBA ExpertCommented:
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
 
dataflowjoeAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
dataflowjoeAuthor Commented:
Not quite what I was looking for but it pointed me in the right direction
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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