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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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?
0
Milewskp
Asked:
Milewskp
  • 6
  • 6
  • 3
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
ErezMorCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 MVP, Access and Data Platform)Commented:
"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
 
ErezMorCommented:
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 MVP, Access and Data Platform)Commented:
"'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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now