Solved

Pass parameter to query dynamically from form

Posted on 2014-04-27
7
2,952 Views
Last Modified: 2014-04-28
Dear experts -
I have a simple request, but am getting confused.
I want to open a table in datasheet view, setting filter criteria based on a field from the form (e.g., if txtDate is not empty, filter records for that date).
I also want to use default sort criteria (multiple fields).

Opentable doesn't seem to do the job.
Openquery I'm wondering about, since I can't see how I can pass a criteria to it...

Thanks for any help -
0
Comment
Question by:terpsichore
7 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Simplest thing to do is create a copy of the query to be used just with this form, then on the criteria line, use a reference to the form and control. i.e.

=Forms![<formname>]![<controlName>]

So if you had frmCustomer and a control called txtCustID, it would be:

=Forms![frmCustomer]![txtCustID]

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Or you can use the main forms Load event, and the textboxes Afterupdate event to change the Filter property of the subform.  Something like:

Private Sub txt_Filter_AfterUpdate

    me.subForm_ControlName.Form.Filter = me.txt_Filter
    me.subform_ControlName.Form.FilterOn = (Len(me.txt_Filter & "") > 0)

End Sub

and in the main forms Load event:

Private Sub Form_Load

    Call txt_Filter_AfterUpdate

End Sub

And to add an order by clause:

me.subform_ControlName.OrderBy = "[field1], [field2] Desc, [Field3]"
me.subform_ControlName.OrderByOn = true

you could put that in the main forms Form_Load event.  If you want to give your users flexibility in using multiple columns to sort the datasheet, you might want to take a look at the article I wrote several months ago on sorting forms and lists.
0
 

Author Comment

by:terpsichore
Comment Utility
actually, I am not using this query for anything on the form - it is only being used as a popup, from which the user can browse or copy/paste records.
Does that refine the way I would approach this?
Thanks!
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Perhaps this could be done in a subform on the main form.  Depending on where the data for the query comes from, you might be able to just link the main form to the subform in the LinkMasterFields and LinkChildFields properties of the subform control, so that the subform displays records linked to the key value in the main form.

Here is a screen shot of such a form:

Main form and subform
0
 

Author Comment

by:terpsichore
Comment Utility
sorry, maybe i'm still not being clear - the query I am running is independent of anything being used on the form EXCEPT 1 or 2 controls that I want to use as input variables in a standalone query - the user will push a button and invoke the query - if those fields are populated, it will set filter conditions based on that, and then open a query in datasheet mode that the user can browse or copy/paste.
THANKS
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
In the standalone query, refer to the form controls as I showed in my first comment.

As long as the form is open, the query will be able to get the value of the controls, and filter the records based on that.

If you don't want to see the form when the datasheet pops up, you can hide it by setting it's visible property to false.   It just needs to be open, but doesn't matter if it's visible or not.

Jim.
0
 

Author Closing Comment

by:terpsichore
Comment Utility
thank you - this works for me
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Get the ms access version 4 33
Access VBA Form - Filter Multiple and/or 7 32
Access 2010 3 15
Max per month 3 13
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now