f



Add "All" to Combo Box and Use "All" as Criteria in Query

Hi Everyone-

I have a question about how to add and then use the "All" selection in
a combo box. I am trying to figure out how to:

(1) add "All" as a selection to a combo box and then

(2) how to use the selection "All" as criteria for a field in a
query, which is used to generate data for a report.


I use 3 different objects on a form as criteria for a query that is
used to generate a report. 2 of these objects are text boxes, and 1 is
a combo box.

I have a simple form named, frmTeam, which has: 2 text boxes, 1 combo
box, and 2 command buttons. The data from the 2 text boxes and combo
box are used as criteria in a query to generate a report.

The 2 text boxes are used to record a user-defined date range. The text
box named, txtBeginDate, stores the beginning date, and the text box
named, txtEndDate, stores the ending date. The dates from these 2 text
boxes are used in a "Between...And" statement as part of the
criteria in my query.

Between [forms]![frmTeam]![txtBeginDate] And
[forms]![frmTeam]![txtEndDate]


The combo box named, cboTeam, displays 2 fields from the table named,
Team. The combo box displays the fields: TeamID and TeamName. The 1st
column, TeamID, is the bound column. The selection in this combo box is
used, along with the 2 dates in the text boxes, as criteria in my
query.

[forms]![frmTeam]![cboTeam]


The 2 command buttons are very simple and straight forward. One command
button is used to preview my report, and the other command button
closes the form.


Right now, everything works fine. I select a Team, i.e. Atlanta Braves,
enter a Beginning Date (1/1/05) and an Ending Date (7/20/05), and my
query will list the names of all the team members that joined the team
between those 2 dates.

I want to add the selection "All: to my combo box so that I can see
all of the names of people who joined a team between those 2 dates.

For example: If I select "All" in my combo box and 1/1/05 as the
beginning date and 7/20/05 as my ending date, I want to see info such
as:

Atlanta Braves - John Doe
Atlanta Braves - Ray Park
New York Mets - Tom Jones
Texas Rangers - George Bush


I don't know how to correctly add the "All" selection to my combo
box and then use it as criteria for my query.


Tables:

Team
TeamID (Text & Primary Key)
TeamName

TeamMember
TeamMemberID (Text & Primary Key)
LastName
FirstName
JoinDate (Date)



Thanks,

Megan   :-)

0
7/24/2005 3:12:13 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

4 Replies
549 Views

Similar Articles

[PageSpeed] 41

http://www.mvps.org/access/forms/frm0043.htm

0
pietlinden (2926)
7/24/2005 4:12:56 AM
i see how to add "all" to a combo box. but how do i use its value?

for instance:

my combo box displays: teamid | teamname from my table team.

teamid | teamname
1        atlanta braves
2        new york mets
3        florida marlins

whichever team i select, the combo box stores their primary key. if i
select "florida marlins" the value stored in my combo box is "3."

i then use this value of "3" as criteria in my query.

the query joins 2 tables: team and teammember.

the table team has:
teamid (pkl)
teamname

the table teammember has:
teammemberid (pk)
lastname
firstname
joindate (date)
teamid (fk to table team)


so when i select "florida marlins," my combo box saves "3" as its
value. "3" is then used in a query to limit the results.

questions:

if i add "all" to my combo box, what is its value?

how can i pass "all" to my query so that it returns all the teams?

i understand the union query i think, but how do i assign a value to
"all?"

thanks,

megan   :-)

0
7/24/2005 3:31:54 PM
you could write the query dynamically in code, and then only add the where 
clause if teamid >0 or something.
or
your WHERE clause could be based on the teamname instead of the id, and then 
you could base your where clause on "like" rather then "=". Then you could 
put "*" in the combobox under teamname, and it would come out: WHERE 
teamname like '*', giving you all teams.

I know these are not complete solutions, but they might give you some ideas.

<meganrobertson22@hotmail.com> wrote in message 
news:1122219114.720413.38210@z14g2000cwz.googlegroups.com...
>
> i see how to add "all" to a combo box. but how do i use its value?
>
> for instance:
>
> my combo box displays: teamid | teamname from my table team.
>
> teamid | teamname
> 1        atlanta braves
> 2        new york mets
> 3        florida marlins
>
> whichever team i select, the combo box stores their primary key. if i
> select "florida marlins" the value stored in my combo box is "3."
>
> i then use this value of "3" as criteria in my query.
>
> the query joins 2 tables: team and teammember.
>
> the table team has:
> teamid (pkl)
> teamname
>
> the table teammember has:
> teammemberid (pk)
> lastname
> firstname
> joindate (date)
> teamid (fk to table team)
>
>
> so when i select "florida marlins," my combo box saves "3" as its
> value. "3" is then used in a query to limit the results.
>
> questions:
>
> if i add "all" to my combo box, what is its value?
>
> how can i pass "all" to my query so that it returns all the teams?
>
> i understand the union query i think, but how do i assign a value to
> "all?"
>
> thanks,
>
> megan   :-)
> 


0
John
7/24/2005 5:22:41 PM
One way to do this is to build your filter on the fly.  Something like
this... NOT TESTED... so beware.

Sub cmdOpenReport        '<== code behind your button to open your
report
strReport = me.cboReport

          '--- if both date fields are filled in, add the date criteria
to the filter
          if Not IsNull(me.dtStartDate) and Not IsNull(Me.dtEndDate)
Then
                 strFilter = "BETWEEN #" & dtStartDate & "# AND #" &
dtEndDate & "#"
           end if

           '---if the cboTeam combo is not ALL then DO add the filter,
otherwise, leave it off.
           if cboTeam.columns(1)<>"All" then
                strFilter = strFilter + " AND "
                strFilter = strFilter & " Team = " & cboTeamID
           end if

          docmd.openreport "SomeReport", strFilter
end sub

0
pietlinden (2926)
7/24/2005 5:51:12 PM
Reply: