Access Query Problem

[tedward]

I have a filtered form with a button which uses “DoCmd.RunCommand acCmdAdvancedFilterSort” to open a query. This code is in the form’s button on click event. When the query opens, how do I save it for example as QueryX. In addition, with each new filtered records I want to use the button to overwrite the query with the same name without being asked for a new save name. Am I asking for the impossible?


Thanks for any help.:banghead::banghead:

 

[Isskint]

tedward,
In the onclick event of the command button that calls the acCmdAdvancedFilterSort add before your code SetNewQuery = TRUE

Then add this the code for the form you call the acCmdAdvancedFilterSort

Private Sub Form_Current()
If SetNewQuery = True Then
Dim strsql As String
Dim NewQry As QueryDef
strsql = "SELECT * FROM [" & Me.RecordSource & "] WHERE " & Me.Filter & "; "
Set NewQry = CurrentDb.CreateQueryDef("qry name", strsql)
End If
SetNewQuery=FALSE
End Sub

SetNewQuery is a public declared boolean variable.

How it works?
When your user clicks your command button to access the filter screen, SetNewQuery is set to TRUE. Then when the user clicks the filter funnel to execute their filter, control is returned to your form (current event) and the code will save the details as a new query. Simply add an inputbox line to get a relevant filter name.

BTW, using acCmdAdvancedFilterSort is the "lazy" way to filter data. I prefer to create a filter form so you can manage things more easily and safely