Solved

How to base a form on a parameter query

Posted on 2011-03-15
15
330 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

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.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

758 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

17 Experts available now in Live!

Get 1:1 Help Now