Return Filter and Sql from a query Datasheet (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:03
Joined
May 21, 2018
Messages
8,463
In my DB i have a query viewer which basically is a form with a subform. You can pick any query from the list and it loads read only in the subform. The source objects are queries and not forms in datasheet view. I Like to be able to sort and filter the queries and then export the sorted filtered data. The only way I know to do this is to build a querydef based of the filtered sql and export that query def. This would be easy if these were forms in datasheet view. Is there a way to reference the dynamic datasheet and get the filter and sql? Or is there another way to export the filtered data? The intent was not to have to make a lot of forms based on the queries and just use the queries. Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:03
Joined
Oct 29, 2018
Messages
21,358
Would a recordset clone work for you?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:03
Joined
Feb 19, 2013
Messages
16,553
Is there a way to reference the dynamic datasheet and get the filter and sql?
if your query is a sourceobject to your subform, you can get the filter and order by properties - and you know the recordsource since you assigned it to the subform. The query you would create would look something like

Code:
with subform.form
    newquerydef.sql="SELECT * FROM [" & .recordsource & "] WHERE " & .filter & " ORDER BY " & .orderby
end with

code needs progressing to allow for no filter or orderby

you do need a querydef to export it

edit if you want the query sql as well then you would need something like

Code:
with subform.form
    newquerydef.sql="SELECT * FROM (" & _
                                  currentdb.querydefs(.recordsource).sql & ") AS " & .recordsource & _
                                 " WHERE " & .filter & " ORDER BY " & .orderby
end with
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:03
Joined
May 21, 2018
Messages
8,463
@CJ_London,
Thanks. That is what I was doing, but it was another error that was causing it to fail and making be think I was doing something wrong. This is what I ended up with. The concept works well for a quick way to view, sort, filter and export a filtered/sorted query.

Code:
Private Sub cmdExport_Click()
    Dim strSQL As String
    Dim strFilter As String
    Dim strSort As String
    Dim qdfTest As QueryDef
    Dim frm As Access.Form
    Set frm = Me.subFrmQuery.Form
    strFilter = frm.Filter
    strSort = frm.OrderBy
    
    strSQL = "Select * from " & Replace(Me.subFrmQuery.Form.RecordSource, ";", "")
    If strFilter <> "" Then
      strSQL = strSQL & " WHERE " & strFilter
    End If
    If strSort <> "" Then
      strSQL = strSQL & " ORDER BY " & strSort
    End If
    Set qdfTest = CurrentDb.QueryDefs("ExportQuery")
    qdfTest.SQL = strSQL
    DoCmd.OutputTo acOutputQuery, qdfTest.Name, acFormatXLSX, , True
  End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:03
Joined
May 21, 2018
Messages
8,463
what was the other error?
"The object does not support the recordsource property". But it was a chain of events that caused this line to fail.
 

Users who are viewing this thread

Top Bottom