Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to base a form on a parameter query

Posted on 2011-03-15
15
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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
Back Up Your Microsoft Windows Server®

Back up 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.

 
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 800 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

722 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