Combo Box on Report

  • Thread starter Thread starter Laurie
  • Start date Start date
L

Laurie

I have been trying to set up a report combo box based on the instructions
below that I got from a previous post. The name of my field that I want the
combo box based on is "MgrName". In the row source of the property box I put
the name of my field "MgrName" but the combo box comes up empty. Do I also
need to list each individual managers name in the row source as well?

Also, how do I set up the combo box to include an "All" choice. For
example, the user can either choose to run the report for one Manager or they
can choose to run it for all managers?

Thank you,

Thank you
--
Laurie



Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.
 
Laurie

I'm not sure why you would use a combobox control on a report, since a
report is a layout for a printed output, and the printed output cannot
"respond" to a combobox.

If you'll explain what you are attemtping to accomplish, folks here may be
able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

What I am trying to accomplish is: When a manager wants to run a report,
rather than having to type in the name, they want to be able to pick the name
from a drop down list and run the report. I was trying to accomplish this
by using an unbound parameter form per the instructions below. In addition,
I also want the managers to be able to run an individual manager report (pick
a certain name from the list) as well as a combined report for all managers
(pick all from the drop down list). Hope this explanation makes more sense.

Thank you,
--
Laurie


Jeff Boyce said:
Laurie

I'm not sure why you would use a combobox control on a report, since a
report is a layout for a printed output, and the printed output cannot
"respond" to a combobox.

If you'll explain what you are attemtping to accomplish, folks here may be
able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laurie said:
I have been trying to set up a report combo box based on the instructions
below that I got from a previous post. The name of my field that I want
the
combo box based on is "MgrName". In the row source of the property box I
put
the name of my field "MgrName" but the combo box comes up empty. Do I also
need to list each individual managers name in the row source as well?

Also, how do I set up the combo box to include an "All" choice. For
example, the user can either choose to run the report for one Manager or
they
can choose to run it for all managers?

Thank you,

Thank you
--
Laurie



Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.
Click to expand...
Click to expand...
 
Laurie,

If you are trying to put a combo box on a report and use it, that will not
work.
As the instructions say, you need to create an ubound form to make your
selections.

You can do it in one of two ways. One is to first open the form and then
run the report from the form once the selections are made. The other, as in
the instructions, is to open the form from the report. The instructions you
have should get you through that part.

Now, adding All to a combo box is another issue. For this, you use a Union
query to present the options. Here is an example:

SELECT "(All)" As Dummy FROM tblInitiative UNION SELECT InitPriority FROM
tblInitiative;

Now, that means that in query, you have to test the value of the combo on
the form and use different criteria depending on whether the combo contains
the "(All)" option.
 
Combo boxes are inappropriate in a report. Those
instruction were for the FORM that's used to open the
report. In a report, you should use a text box bound to the
query field you want it to display.

I will assume you really want to use the combo box on the
form as (part?) of the filter for the report's records. To
add an All choice to the list you would change the combo
box's row source query from something like

SELECT MgrName FROM managerstable ORDER BY MgrName

to

SELECT " All" As MgrName FROM managerstable
UNION
SELECT MgrName FROM managerstable
ORDER BY MgrName

Then, in the report's record source query, change the
criteria from
=Forms!theform.thecombobox
to
=Forms!theform.thecombobox OR Forms!theform.thecombobox =
"All"
 
Thank you for your responses. I am somewhat new to writing this type of code
and still in the learning phase. Tried it but still cannot get it to work.
When I go into the combo box row source and click on the 3 dots it brings me
into the query. Do I put the code in the field or the criteria? I also
tried going to Query, SQL Specific, Union and then putting the code there.
Also, when typing the code I was prompted for parenthesis around the code
and I tried several variations but that did not work. Any guidance on where
and how to enter this code would be appreciated.

--
Laurie


Marshall Barton said:
Combo boxes are inappropriate in a report. Those
instruction were for the FORM that's used to open the
report. In a report, you should use a text box bound to the
query field you want it to display.

I will assume you really want to use the combo box on the
form as (part?) of the filter for the report's records. To
add an All choice to the list you would change the combo
box's row source query from something like

SELECT MgrName FROM managerstable ORDER BY MgrName

to

SELECT " All" As MgrName FROM managerstable
UNION
SELECT MgrName FROM managerstable
ORDER BY MgrName

Then, in the report's record source query, change the
criteria from
=Forms!theform.thecombobox
to
=Forms!theform.thecombobox OR Forms!theform.thecombobox =
"All"
--
Marsh
MVP [MS Access]

I have been trying to set up a report combo box based on the instructions
below that I got from a previous post. The name of my field that I want the
combo box based on is "MgrName". In the row source of the property box I put
the name of my field "MgrName" but the combo box comes up empty. Do I also
need to list each individual managers name in the row source as well?

Also, how do I set up the combo box to include an "All" choice. For
example, the user can either choose to run the report for one Manager or they
can choose to run it for all managers?


Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
ProviderID field and the Provider Name.
Name the Combo Box 'FindProvider'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source, on the [ProviderID]
field criteria line write:
forms!ParamForm!FindProvider

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

When ready to run the report, open the report.
The form will open and wait for the selection of the Provider and the
entry of the starting and ending dates wanted.
Click the command button and the report will run.
When the report closes, it will close the form.
Click to expand...
Click to expand...
 
Laurie said:
Thank you for your responses. I am somewhat new to writing this type of code
and still in the learning phase. Tried it but still cannot get it to work.
When I go into the combo box row source and click on the 3 dots it brings me
into the query. Do I put the code in the field or the criteria? I also
tried going to Query, SQL Specific, Union and then putting the code there.
Also, when typing the code I was prompted for parenthesis around the code
and I tried several variations but that did not work. Any guidance on where
and how to enter this code would be appreciated.
Click to expand...


The UNION query can only be entered/edited in SQL view or
directly in the combo box's RowSource. Be sure to change
the names I used to the names you have.

Then criteria I suggested go in the Criteria row under the
field you are using to filter the report's record source
query.
 
Thank you that worked. However, when running the report the list does not
show, I have to type in the first initial of the last name. Is there a way
to make the list visible. I have checked all of the property boxes and
Visible is set to yes.

Thank you
 
Laurie said:
Thank you that worked. However, when running the report the list does not
show, I have to type in the first initial of the last name. Is there a way
to make the list visible. I have checked all of the property boxes and
Visible is set to yes.
Click to expand...


List? What list? The only list I have seen you mention is
a combo box on a report and everyone said that wont work,

What are you trying to do and where are you tring to do it?

If you are talking about the combo box on the form that is
used to select a name or All, I would need to know what you
have in the form combo box's RowSource and the other
List/Column properties.

OTOH, you said "it" worked, but I'm not clear on what did
and did not work.
 
Sorry I meant to say the combo box on the parameter form.

In the row source of the combo box I have: SELECT " All" As MgrName FROM
tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName

The Bound and Column are set to 1. The column width is set to 0";1"

When I run the report and the parameter form opens the combo box does not
show any names, it appears to be an empty box. In order to get a name to
come up I have to type in the first letter of the name, for example if the
name is Abbott I have to type in A to get the name. The names are hidden
until you type the first letter. What I am trying to do is when I run the
report and the parameter form opens I would like to see all the names in the
combo box and then just select one of the names rather than having to type
the first letter of the name I want the report for. How do I get all of the
names to show in the box when I click on the down arrow?

Thank you
 
Laurie said:
Sorry I meant to say the combo box on the parameter form.

In the row source of the combo box I have: SELECT " All" As MgrName FROM
tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName

The Bound and Column are set to 1. The column width is set to 0";1"

When I run the report and the parameter form opens the combo box does not
show any names, it appears to be an empty box. In order to get a name to
come up I have to type in the first letter of the name, for example if the
name is Abbott I have to type in A to get the name. The names are hidden
until you type the first letter. What I am trying to do is when I run the
report and the parameter form opens I would like to see all the names in the
combo box and then just select one of the names rather than having to type
the first letter of the name I want the report for. How do I get all of the
names to show in the box when I click on the down arrow?
Click to expand...


All those details really help. The problem is that the
ColumnWifths property has 0" for the first (and only)
column. It should either be empty (list is same width as
the combo box) or something big enough to display the names.
 
Back
Top