Adding "All" To ComboBox

J

Jenn

Can anyone tell me how to add the capability to add "all" to a combo box for
a report?
 
L

Lynn Trapp

Use a Union query as the row source for your combo box:

Select "All"
From SomeTable
UNION
Select SomeField
From SomeTable;
 
J

Jenn

I did not work. maybe I should explain what I am trying to do in a little
more detail. I have a query that is linked to a table. The table has 12
project numbers and a cost that each project number has associated with it.
I created a form that has a combo box. What I want to have happen is allow
the user to select a project number from the box, and have the query run with
that project number criteria. I also want the option of allowing the user to
run the query showing all the project numbers, not just a specified one.

I tried to set things up as you explained. When I run the combo box it comes
back with a "Enter Parameter value for "All" " message. Any more advice?
Thanks in advance!
 
L

Lynn Trapp

As Doug suggested, if you'll post the SQL for your query here then someone
should be able to figure out what your problem is.
 
J

Jenn

Thanks for responding. Here is as much detail as I can think of:


Ok I am trying to include as much detail as possible. I have created a form
that holds a combo box, and a run query button. I want the combo box to
populate from a table and then whatever is in the combo box I want to be the
criteria for a query. Here are the steps I went through…

1. I have a table called Projects. In it there are 3 fields:
ID (auto primary key)
Project Number
Project Description

I want the combo box to display the project Number so I have set the column
widths for the combo box to 0â€,0â€, and 1.5â€

The Row Source Query that I typed is:
Select “All†From PROJECTS UNION Select Project Number From PROJECTS


I want the Project Number in the combo box to feed the query criteria below
but I also want to be able to let the user select “all†and have the query
just run with all the project numbers:


2. I have created a run query button on the form. In the run query button I
have the following VBA coded:


Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String

stDocName = "Project Query"
DoCmd.OpenReport stDocName, acPreview

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

3. In the Query I have the selection of the combo box (comboProjectList)
feeding it with the following as the criteria:

[forms]![testProjectform]![comboProjectList]


Obviously you can see I am a newbie. I know there should be an easy way to
run a query with a specific number as critieria or just to let it run w/out
criteria. I just cant get there! Thanks for your help.
 
Top