Unbound Combo Box Record Selector Question

[Megan]

I'm using an unbound combo box on a form as a record selector. I'm
using the combo box to choose certain types of music, i.e.
Alternative, Pop, Rock, etc…and then run a report based on the
selection.

My combo box, cboMuisicType, has a simple query, qryGetType, as a row
source. This query retrieves data from my table, tblMusicTypes. This
table has 2 fields: MusicID and MusicDesc. MusicID is the primary key
and an autonumber. MusicDesc is a description of the music, i.e.
Alternative, Pop, Rock, etc…

tblMusicTypes:

MusicID | MusicDesc
1 Alternative
2 Pop
3 Rock
The combo box is also the criteria of my query, qryGetType:
[MusicForm][cboMusicType]
Right now, I have no problem choosing one of the types of music in my
combo box and generating a report based on that selection. However,
I'd like to add a choice to my combo box that would allow me to choose
all of the types of music and then generate a report. For example,
selecting the word, "All," from my combo box, cboMusicType, would
generate a report based on all of the types of music in my table,
tblMusicTypes.

So I'm wondering what the best/ simplest way to do this is.

Should I add "All" to my table, tblMusicTypes?

Thanks!

Megan

[WindAndWaves]

yes or you can make a union query (do you know how to do this???), with
additional options.

Otherwise you can also change the combobox into a listbox with the
multiselection option set to true.

Let me know what would suit best.

- Nicolaas

[Megan]

"WindAndWaves" <ac****@ngaru.com> wrote in message news:<0A******************@news.xtra.co.nz>...

yes or you can make a union query (do you know how to do this???), with
additional options.

Otherwise you can also change the combobox into a listbox with the
multiselection option set to true.

Let me know what would suit best.

- Nicolaas

I think I know how to do a union query. It's like query1 union query2
union query3, etc... right? But I'm unsure how to use it with a combo
box.

I've never used a listbox before. So, I'll look at some posts here to
see if I can figure it out.

Thanks for the suggestions!!!

Megan

[WindAndWaves]

If your form has space for a list box then it would definitely be better. In
that case your user can make any selection.

Another way to do this is to add an extra field to your table that you call
something like Sel (for select) of the boolean type.

the nice thing about using a tickbox is that it will retain its value, so if
the user wants to (most of the time) print a report for Britney Spears and
Madonna, then it will retain this selection.

Here are some generic functions that I use for controlling listboxes. Note
that you need to set them to multiselect (on the Other Tab) to use these
functions. Also note that they will need some reworking for your
application.

Option Compare Database
Option Explicit
Const ModEro = 9933
'other list operations

Public Function FuCIS(FrmN As String, CtlN As String) As String
'returns a string that tells you how many controls are selected
On Error GoTo ERR
'---
Dim FRM As Form
Dim Ctl As Control
Dim Itm As Variant
Dim X As Long
Dim Y As Long
'---
Set FRM = Forms(FrmN)
Set Ctl = FRM.Controls(CtlN)
'---
For Each Itm In Ctl.ItemsSelected
X = X + 1
Next Itm
If X = 0 Then
FuCIS = "none"
Else
Y = Ctl.Recordset.RecordCount
If X = Y Then
FuCIS = "all"
Else
FuCIS = X & "/" & Y
End If
End If
xit:
Exit Function
ERR:
FuCIS = "N/A"
Resume xit
End Function
Public Function FuSLI(FrmN As String, CtlN As String, REC As Long, bNew)
'selects a particular item on a multi-selection list
'REC = the ID of the item to selected
'bnew identifies whether it should be selected or unselected
Const ProEro = 3: 'On Error GoTo err
'---
Dim FRM As Form
Dim Ctl As Control
Dim Itm As Variant
Dim I As Long
'---
Set FRM = Forms(FrmN)
Set Ctl = FRM.Controls(CtlN)
'---
For I = 0 To Ctl.ListCount - 1
If Ctl.ItemData(I) = REC Then
Ctl.Selected(I) = bNew
End If
Next I
xit:
Exit Function
ERR:
Resume xit
End Function
Public Function MultiSelectW(FRM As Form, Ctl As Control, TBL As String) As
String
'creates a WHERE statement for a multi select listbox
'the WHERE statement can filter for for IDs of the items selected
Const ProEro = 1: 'on error GoTo err
'---
Dim Row As Variant
Dim ID As Long
'---
If Ctl.ItemsSelected.COUNT < 1 Or Ctl.ListCount < 1 Then
MultiSelectW = "Where [" & TBL & "-ID]>=0"
GoTo xit
Else
For Each Row In Ctl.ItemsSelected
ID = Ctl.Column(0, Row)
MultiSelectW = MultiSelectW & "[" & TBL & "-ID]=" & ID & "
OR "
Next Row
MultiSelectW = Left(MultiSelectW, Len(MultiSelectW) - 4)
End If
xit:
Exit Function
ERR:
Call FerrorLog(ERR.Number, 0, ProEro + ModEro): Resume xit
end function
End Function

[James Fortune]

me**************@hotmail.com (Megan) wrote in message news:<5c**************************@posting.google. com>...

So I'm wondering what the best/ simplest way to do this is.

Should I add "All" to my table, tblMusicTypes?

Thanks!

Megan

Although it's not difficult to put the word "all" in your combobox,
consider letting a blank value in the combobox mean "all." For search
forms with lots of search fields it would complicate things
unnecessarily to put "all" in for each search field. It's just as
easy to check for a Null value as an "all" value. It also makes sense
when you think of filtering records based on a value. No value means
no filtering. Here "no filtering" means having no WHERE clause.
Note: some people like to use the Report's Filter property to specify
criteria. The reason using blank values for "all" works reasonably
well is that people seldom search for values that are Null.

James A. Fortune

[Megan]

Hey-

Thanks for your suggestions and time! I truly appreciate it!

If you don't mind, I'd like to pose this follow-up hypothetical
situation...

1.) What if I add "All" to my table, tblMusicTypes,
tblMusicTypes:

MusicID | MusicDesc
1 Alternative
2 Pop
3 Rock
4 All

2.) Use this SQL statement:

SELECTMusicID, MusicDesc FROM tblMusicTypes UNION SELECT NULL AS
AllChoice, "(All)" AS cboAll FROM tblMusicTypes;

I was reading through the newsgroup and saw a link to the web site,
The Access Web (http://www.mvps.org/access/). On their Form's page,
they had this situation and a possible solution-- a union query as
"WindAndWaves" suggested.

But...I'm unsure of the code, such as the word "AllChoice." Is this an
Access keyword?

Plus, are the "()" needed around the word, All, in the part: "(All)"
AS cboAll FROM tblMusicTypes;

I'm going to try this, and I'll let you know how it goes so that if
anybody else has this situation, they can read this post and find a
solution!
"I'll be Baaack!" :-)

Thanks All!

Megan

[James Fortune]

me**************@hotmail.com (Megan) wrote in message news:<5c**************************@posting.google. com>...

Hey-

Thanks for your suggestions and time! I truly appreciate it!

If you don't mind, I'd like to pose this follow-up hypothetical
situation...

1.) What if I add "All" to my table, tblMusicTypes,
tblMusicTypes:

MusicID | MusicDesc
1 Alternative
2 Pop
3 Rock
4 All
Megan,

The idea behind using the Union Query was to make the word "All"
available without putting it in the table. Sometimes the criteria for
filling a combobox is more complicated than in your case and being
able to add "All" without putting it in a table is desirable. The
Union Query also allows you to put the word "All" at the beginning or
at the end of the combobox regardless of the alphabetic order that
would normally result. You could also get "All" from the table in the
spot you want by including an "ORDER BY MusicID" clause in the SQL
statement used as a RecordSource for the combobox and swapping
MusicDesc values to get the desired order.

2.) Use this SQL statement:

SELECTMusicID, MusicDesc FROM tblMusicTypes UNION SELECT NULL AS
AllChoice, "(All)" AS cboAll FROM tblMusicTypes;

If you don't put the word 'All' in tblMusicTypes then you can use
something like:

SELECT 'All' As MusicDesc, 0 As MusicID FROM tblMusicTypes UNION
SELECT MusicDesc, MusicID FROM tblMusicTypes ORDER BY MusicID;

to use the MusicID to get the desired list order or:

SELECT 'All' As MusicDesc FROM tblMusicTypes UNION SELECT MusicDesc
FROM tblMusicTypes ORDER BY MusicDesc;

to have 'All' as the first entry followed by the rest of the
selections alphabetized.

Hope this helps (hypothetically :-)),

James A. Fortune
"I'll be Baaack!" :-)

Thanks All!

Megan