Solved

How to base a form on a parameter query

Posted on 2011-03-15
15
348 Views
Last Modified: 2013-11-28
I would like to base a form on a parameter query, and I would like to assign the parameters via the OnOpen event of the form.

How can I do this?
0
Comment
Question by:Milewskp
  • 6
  • 6
  • 3
15 Comments
 
LVL 75
ID: 35140806
Well, if your query looks something like what you see below ... and you make that query the Recordsource of the Form, then when you Open the Form ... you will be prompted for the values.  Is this what you mean?

SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1)=[Enter Value for Field1]) AND ((Table1.FIELD2)=[Enter Value for Field2]));


mx
0
 
LVL 75
ID: 35140844
And you can use the PARAMETERS property to specifically indicate what the data types should be, for example: (see image also)

PARAMETERS [Enter Value for Field1:] Text ( 255 ), [Enter Date:] DateTime;
SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1)=[Enter Value for Field1:]) AND ((Table1.YourDate)=[Enter Date:]));

mx
Capture1.gif
0
 
LVL 12

Assisted Solution

by:ErezMor
ErezMor earned 200 total points
ID: 35140848
i'll start by saying you cant use a parameterized query  as a form's record source without filling the inputbox manually
how does the parameter get to the open event?
if you have a global variable (one that is declared in a general module instead of a form's class module) that you can set to the desired value BEFORE you open the form, then you can change the form to use AD-HOC datasource by writing in the open event:
Me.Recordsource="Select * From Mytables where myField=" & myGlobalVariable
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Milewskp
ID: 35141004
Hi mx,
<then when you Open the Form ... you will be prompted for the values>
Right, but I don't want the user to be prompted, I would like the OnOpen event to take care of assigning the parameter values.
0
 
LVL 75
ID: 35141044
"I would like the OnOpen event to take care of assigning the parameter values."

Based on what exactly ?

mx
0
 
LVL 12

Expert Comment

by:ErezMor
ID: 35141369
"Right, but I don't want the user to be prompted, I would like the OnOpen event to take care of assigning the parameter values."
isnt that EXACTLY what i suggested? but dont mind me, i'm jsut passing by...
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35152078
hI ErezMor,
<i'll start by saying you cant use a parameterized query  as a form's record source without filling the inputbox manually>
I would have thought that there is some way to trap the inputboxes via code, but unless another expert shows us how, I will assume you are correct.

<how does the parameter get to the open event?>
I don't want to constrain the possible solutions. My only requirement is that the user isn't required to do it manually.
Here's my application:  When the user clicks cmdNotes on Form1, I want Form2 to open showing only relevant records of Table1. 'Relevant' is based on the value of the ID field of the current record of Form1.

<if you have a global variable (one that is declared in a general module instead of a form's class module) that you can set to the desired value BEFORE you open the form, then you can change the form to use AD-HOC datasource by writing in the open event:
Me.Recordsource="Select * From Mytables where myField=" & myGlobalVariable>
Thanks, that works well.


0
 
LVL 1

Author Comment

by:Milewskp
ID: 35152090
Unless someone comes up with a better solution, I'll award points to ErezMor.
0
 
LVL 12

Accepted Solution

by:
ErezMor earned 200 total points
ID: 35152802
now you're talking, you could have detailed the structure from the start and save yourself quite some hours/days/coding
if your requested "filtering" value for form2 is some field in the current record of form1 then the solution is updating your query
set the value in the query design for the required field from a parameter to :

Forms!form1!yourFilterField

and return to having your form2's recordsource set to the query

the above will work as long as form1 is OPEN while form2 opens and runs (since then is the time the query will look for it's value)

good luck

0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 35152827
"'Relevant' is based on the value of the ID field of the current record of Form1."

Then why not just do this - code to open form:

DoCmd.OpenForm "Form2", , , "[ID] = " & Me.ID

Then your query needs not criteria/prompt at all.  Just open the Form with the WHERE clause in the OpenForm command.  No need for a Global variable.

Change ID  and Me.ID to your actual names.

mx

0
 
LVL 75
ID: 35152850
Noting:

                       DoCmd.OpenForm "Form2", , , "[ID] = " & Me.ID
           Current ID value on Form1 ..................................^^^^^^

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35156123
Hi ErezMor,
<Forms!form1!yourFilterField>
This is even better. Thanks!

Hi mx,
<DoCmd.OpenForm "Form2", , , "[ID] = " & Me.ID>
This is even better again. It won't work in my case, but may help other readers with this problem. In my case, Form2 has no recordsource; instead it has two subforms (one DS-view, the other Form-view) with the same recordsource.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 35157836
Then it can be done still with DoCmd.OpenForm, by passing the OpenArgs parameter, and grabbing that in the Subforms.

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35158112
hi mx,
<Then it can be done still with DoCmd.OpenForm, by passing the OpenArgs parameter...>
Yes it can! You're my hero.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35185434
Thanks all, I'll split the points.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Top 1 of each supplier 55 58
Allow user to edit an outgoing email 2 25
Exporting Access Tables as CSV 3 24
ms access filter query with empty combobox 5 29
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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