Filter to Query

Hey All Again,

Need some more of your expert help.

From FORM, please help me figure out how I can use a command  button to transfer all the filtered FORM fields to a QUERY using VBA

Basically I want user to be able to fileter from a FORM then use a command button to transfer the filtered data to a QUERY for output or viewing

Thanks Much


Who is Participating?
stevbeConnect With a Mentor Commented:
if you use a report you can pass the filter of the form in the wherecondition argument of docmd.openreport

If Me.FilterOn = True Then
    DoCmd.OpenReport ReportName:="MyReport", WhereCondition:=Me.Filter
    DoCmd.OpenReport ReportName:="MyReport", WhereCondition:=Me.Filter
End If

another way to view the data woukld be to create a datsheet form (which looks just like a query) and pass the filter, again, in the WhereCondition argument.

You could also let the users switch the view of the form that is already filtered to datsheet view ... either teach them that his is available from the View menu or you could embed the form as a subform and then on the  main form add a button ...

Private Sub cmdSwitch_Click()
    DoCmd.RunCommand acCmdSubformDatasheetView
End Sub

Try this for start in your button (on click event)

Dim sSQL as string
Dim qry as DAO.querydef

sSQL = me.recordsource

Set qry = currentdb.createquerydef("MyQuery",sSQL)

transfer filtered form fields? how are the users selecting what fields to select, is it from a listbox or something

basically from your selected fields, build your sql


sFields       'contains list of fields

sSql = "SELECT " & sFields & " FROM sometable"

create your query, like shown then use you can dump the output, say use DoCmd.SendObject

MontanajobsAuthor Commented:
Thank You
All Courses

From novice to tech pro — start learning today.