Save Advanced Filter/Sort?

[gray]

Hi

I prefer to hide menus and toolbars so I give my users a custom button on forms to open the AdvancedFilterSort window. The AdvancedFilterSort opens with a (default?) of "My_FormName_Filter1".

I also launch a small form "Apply_Filter_Form" which has Apply and Cancel buttons. The Apply button calls:-

DoCmd.Close acForm, "Apply_Filter_Form"
RunCommand acCmdApplyFilterSort
DoCmd.Close acQuery, "My_FormName_FormFilter1"

Works perfectly but what I'd like to do is give them the option of saving this as a Query when they click the Apply button.

When I tried to test this in the Apply button:-

DoCmd.CopyObject , "My_Test_Filter", acQuery, "My_FormName_FormFilter1"

it failed with :-

Can't find the object "My_FormName_FormFilter1"

I guess that "My_FormName_FormFilter1" is not really a Query as such?

Anyone know how to do this please?

 

[bob fitz]

Hi Gray

I think you can create the query with the CreateQueryDef method. I have copied the example below from the Access Help file.

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()
    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef
 
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
 
    With dbsNorthwind    ' Create temporary QueryDef.

        Set qdfTemp = .CreateQueryDef("", _
                                      "SELECT * FROM Employees")    ' Open Recordset and print report.
        GetrstTemp qdfTemp    ' Create permanent QueryDef.

        Set qdfNew = .CreateQueryDef("NewQueryDef", _
                                     "SELECT * FROM Categories")    ' Open Recordset and print report.

        GetrstTemp qdfNew    ' Delete new QueryDef because this is a demonstration.
 
        .QueryDefs.Delete

        qdfNew.Name

        .Close

    End With

End Sub
 
 
Function GetrstTemp(qdfTemp As QueryDef)
    Dim rstTemp As Recordset
 
    With qdfTemp
        Debug.Print .Name
        Debug.Print " " & .sql    ' Open Recordset from QueryDef.

        Set rstTemp = .OpenRecordset(dbOpenSnapshot)
 
        With rstTemp    ' Populate Recordset and print number of records. .MoveLast

            Debug.Print " Number of records = " & _
                        .RecordCount

            Debug.Print

            .Close

        End With

    End With

End Function

 

[bob fitz]

Hi Gray

This is the first time I've used this forum and the code I posted in my last post dosen't look very clear. If you search the Access help file for "CreateQueryDef Method" you will get a good explanation and an example of how to use it.

 

[gray]

Thanks Bob.... I shall have a read....

P.S.
I had the same formatting problems to start with.... if you want to add in code to your posts highlight the block of code lines and use # symbol on the format menu ...or type
{CODE}
xxxxx
xxxxx
xxxxx
{/CODE}
but substitute wavy brackets with square ones []

 

[gray]

Hi

Had a quick look thru' but I don't think it's quite what I need... which is:-

Call AdvancedFilterSort to open default filter (in query design view_.. this would then have existing filters in it.

Permit user to edit the filter/sort

Either Apply the newly edited default filter or offer option to save this as another, permanent, filter and apply that instead.

I would have thought that copyobject would do this but it can't find the default filter... e.g. My_FormName_FormFilter1 ?? I wonder if that is because My_FormName_FormFilter1 is itself a temp query?

 

[gray]

Sussed it... for anyone else out there banging their head on the table....

I've added the following to my Apply button click event on my Apply_Filter_Form....

 Msg = "Save as permanent query?"
 
UserResponse = MsgBox(Msg, vbYesNo, Me.Form.Caption)
 
If UserResponse = vbYes Then
        DoCmd.RunCommand acCmdSaveAsQuery
End If
 
DoCmd.Close acForm, "Apply_Filter_Form"
RunCommand acCmdApplyFilterSort
DoCmd.Close acQuery, "My_FormName_FormFilter1"

HTH someone