Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filter to Query

Posted on 2004-08-12
4
Medium Priority
?
1,010 Views
Last Modified: 2012-05-05
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

Janxx


0
Comment
Question by:Montanajobs
4 Comments
 
LVL 8

Expert Comment

by:arcross
ID: 11783689
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)

0
 
LVL 39

Accepted Solution

by:
stevbe earned 2000 total points
ID: 11783772
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
Else
    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

Steve
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11783808
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

e.g.

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

0
 

Author Comment

by:Montanajobs
ID: 11784157
Thank You
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question