How to base a form on a parameter query

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?
LVL 1
MilewskpAsked:
Who is Participating?
 
ErezMorConnect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ErezMorConnect With a Mentor Commented:
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
 
MilewskpAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I would like the OnOpen event to take care of assigning the parameter values."

Based on what exactly ?

mx
0
 
ErezMorCommented:
"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
 
MilewskpAuthor Commented:
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
 
MilewskpAuthor Commented:
Unless someone comes up with a better solution, I'll award points to ErezMor.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"'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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Noting:

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

mx
0
 
MilewskpAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Then it can be done still with DoCmd.OpenForm, by passing the OpenArgs parameter, and grabbing that in the Subforms.

mx
0
 
MilewskpAuthor Commented:
hi mx,
<Then it can be done still with DoCmd.OpenForm, by passing the OpenArgs parameter...>
Yes it can! You're my hero.
0
 
MilewskpAuthor Commented:
Thanks all, I'll split the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.