Using a Form to Create Drop Down Selections for Report

  • Thread starter MichelleM via AccessMonster.com
  • Start date
M

MichelleM via AccessMonster.com

I am fairly new to using Access and here's what I want to do:

I want to create a form that allows the user to select a leader, enter a date
and receive data back for only their employees in a report. I know it's
possible, but I don't know how to get started. I have queries created with
the data needed. I just need to be able to select certain records,
preferably by a drop-down.

Any help is appreciated!
 
B

bhicks11 via AccessMonster.com

Easiest way - base the report on a parameter query. Set the criteria for the
date to:

[What date?] and it will prompt the user for the date before running the
report.

Bonnie

http://www.dataplus-svc.com
 
A

Allen Browne

There are 2 ways to do this:

a) Use parameters in your query.
In the Criteria row of the query, refer to the controls on your form, e.g.:
[Forms].[Form1].[Combo0]
Advantage: quick and easy to do.
Disadvantage: really clumsy if there are lots of boxes for critiera, and not
all of them are used.

b) Use code to build the WhereCondition dynamically.
Advantage: really efficient
Disadvantage: requires some VBA coding experience.

Here's a simple example that illustrates both approaches for dates:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive example that you can download and play with to
see how it works:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
 
A

Arvin Meyer [MVP]

Use the dropdown in the form's header, let's call it cboLeaderID to hold the
value of the leader's ID and display the leader name. The bound column of
the combo will be the first column (LeaderID). Use the combo wizard to help
build this.

Now, in the LeaderID column of your query, use a criteria of:

[Forms]![YourFormName]![cboLeaderID]

Base your form on this query, and in the AfterUpdate event of the combo box,
requery the form:

Sub cboLeaderID_AfterUpdate()
Me.Requery
End Sub
 
F

fredg

I am fairly new to using Access and here's what I want to do:

I want to create a form that allows the user to select a leader, enter a date
and receive data back for only their employees in a report. I know it's
possible, but I don't know how to get started. I have queries created with
the data needed. I just need to be able to select certain records,
preferably by a drop-down.

Any help is appreciated!
Click to expand...

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a EmployeeID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the EmployeeID field as well as the
Employee Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the EmployeeID field by setting the Combo box's ColumnWidths
property to 0";1"
Make sure the Combo Box Bound Column is the
EmployeeID column.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's EmployeeID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Employee Name in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
 
J

Jamie Bratt

Thanks for your clear instructions, Fred. I have almost succeeded, but nothing happens when I click my command button on the form I made. There seems to be no command telling the report to continue to run with the selected parameters. Any insight? The only action I've coded for the command button so far is Me.Visible = False, which I entered as an Event Procedure.

Also, is it safe to assume that this will work for multiple selections? For example, if I wanted users to be able to select two EmployeeNames to search for within the given dates, could I simply add the correct fields in the form and tack an "or" function onto the query parameter?

Many thanks!
Jamie
 
Top