Parameter queries with mainform/subforms

[Guest]

I need some help with parameter queries associated with main form/subform.
My database includes an ER staff table and staff assignments table (positions
in the ER) that staff are assigned to each shift. I have a query that counts
the number of times that they are assigned to each position and have used it
to produce a report and also a look-up main form with subform. The main form
allows me to select a staff name and the subform then displays the counts of
all of their assignments. It is great. I have changed the query to a
parameter query that permits users to select a date range for both reports.
The problem is that I am prompted for a date range each time I select a new
staff name on the main form. Is there a way to set the parameter date range
once and then select a staff to view the results and then select another
staff on the main form without having to enter the parameter date range again?

 

[Duane Hookom]

Add text boxes on the form for the users to enter beginning and ending
dates. This allows you to provide defaults, use calendars to pick dates,
etc.

 

[Steve Schapel]

Chuck,

I wouldn't use a Parameter Query for this. On the main form, put a
couple of unbound comboboxes, where the user can enter the date range
required, and then refer to these textboxes in the criteria of the
applicable date field in your query, probably the syntax will be like
this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]

 

[Guest]

Steve,

Great suggestion. It worked like a charm and I can think of many other uses
in other applications that I have developed. I have also applied this to a
report but it is not quite as neat in that the user is prompted for a date
range without a professional looking form into which to enter the Start Date
and End Date but it still works.

Again, thanks for the time and guidance.

Chuck Moore

Chuck,

I wouldn't use a Parameter Query for this. On the main form, put a
couple of unbound comboboxes, where the user can enter the date range
required, and then refer to these textboxes in the criteria of the
applicable date field in your query, probably the syntax will be like
this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]

--
Steve Schapel, Microsoft Access MVP

I need some help with parameter queries associated with main form/subform.
My database includes an ER staff table and staff assignments table (positions
in the ER) that staff are assigned to each shift. I have a query that counts
the number of times that they are assigned to each position and have used it
to produce a report and also a look-up main form with subform. The main form
allows me to select a staff name and the subform then displays the counts of
all of their assignments. It is great. I have changed the query to a
parameter query that permits users to select a date range for both reports.
The problem is that I am prompted for a date range each time I select a new
staff name on the main form. Is there a way to set the parameter date range
once and then select a staff to view the results and then select another
staff on the main form without having to enter the parameter date range again?

 

[Guest]

Steve,

A year after your suggestion I have the same problem. I found your reply and
used it. However it does not work. I probably are to new in Access
programming to realise how to do it.
In my main form I did put two unbound text boxes. The queries (for one main-
and two subforms) have the criterias mentione by you.
If you put DateFrom and DateTo in the appropriate text boxes, what then? Do
I hit enter?
ia ma lost, sorry.
Thanks for helping

Bernhard.

Steve,

Great suggestion. It worked like a charm and I can think of many other uses
in other applications that I have developed. I have also applied this to a
report but it is not quite as neat in that the user is prompted for a date
range without a professional looking form into which to enter the Start Date
and End Date but it still works.

Again, thanks for the time and guidance.

Chuck Moore

Chuck,

I wouldn't use a Parameter Query for this. On the main form, put a
couple of unbound comboboxes, where the user can enter the date range
required, and then refer to these textboxes in the criteria of the
applicable date field in your query, probably the syntax will be like
this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]

--
Steve Schapel, Microsoft Access MVP

I need some help with parameter queries associated with main form/subform.
My database includes an ER staff table and staff assignments table (positions
in the ER) that staff are assigned to each shift. I have a query that counts
the number of times that they are assigned to each position and have used it
to produce a report and also a look-up main form with subform. The main form
allows me to select a staff name and the subform then displays the counts of
all of their assignments. It is great. I have changed the query to a
parameter query that permits users to select a date range for both reports.
The problem is that I am prompted for a date range each time I select a new
staff name on the main form. Is there a way to set the parameter date range
once and then select a staff to view the results and then select another
staff on the main form without having to enter the parameter date range again?