How can I build in an "ALL" for a combo box. 1

[ToyFox]

How can I build in an "ALL" for a combo box.
I want the users to be able to select "ALL", is there an example around....Thx

 

[SkipVought]

Hi,

The way I have always done it...

Select Distinct ListElement
From MyTable
Union ALL
Select Distinct ' ALL'
From MyTable

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ToyFox]

Thanks I'll give it a try..

 

[TheAceMan1]

How are ya ToyFox . . .

See faq702-4538

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ToyFox]

One wrinkle here is that I use the value in a parameter query.
So, I have something like [Forms]![frmTest]![txtAssigned_To] in the query grid...How would I pass the SQL from the <ALL>.
Thanks for the help.

 

[PHV]

In the criteria cell:
=[Forms]![frmTest]![txtAssigned_To] OR [Forms]![frmTest]![txtAssigned_To]='All'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradVT]

Here is a routine I have used for quite a long time. I just plugged it into my appliaction and I didn't write it and cannot remember where I got it.

Set the combo row source type to AddAllToList

Add this module code

Function AddAllToList(c As Control, ID As Long, Row As Long, _
Col As Long, Code As Integer) As Variant

'***************************************************************
' FUNCTION: AddAllToList()
'
' PURPOSE:
' Adds "(all)" as the first row of a combo box or list box.
'
' USAGE:
' 1. Create a combo box or list box that displays the data you
' want.
'
' 2. Change the RowSourceType property from "Table/Query" to
' "AddAllToList."
'
' 3. Set the value of the combo box or list box's Tag property to
' the column number in which you want "(all)" to appear.
'
' NOTE: Following the column number in the Tag property, you can
' enter a semicolon ( and then any text you want to appear
' other than the default "all."
'
' For example
'
' Tag: 2;<None>
'
' displays "<None>" in the second column of the list.
'
'***************************************************************
Static db As Database, rs As Recordset
Static DISPLAYID As Long
Static DISPLAYCOL As Integer
Static DISPLAYTEXT As String
Dim Semicolon As Integer

On Error GoTo Err_AddAllToList

Select Case Code
Case LB_INITIALIZE
' See if the function is already in use.
If DISPLAYID <> 0 Then
MsgBox "AddAllToList is already in use by another Control!"
AddAllToList = False
Exit Function
End If

' Parse the display column and display text from the Tag
' property.
DISPLAYCOL = 1
DISPLAYTEXT = "(All)"
If Not IsNull(c.Tag) Then
Semicolon = InStr(c.Tag, ";")
If Semicolon = 0 Then
DISPLAYCOL = Val(c.Tag)
Else
DISPLAYCOL = Val(Left(c.Tag, Semicolon - 1))
DISPLAYTEXT = Mid(c.Tag, Semicolon + 1)
End If
End If

' Open the recordset defined in the RowSource property.
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(c.RowSource, DB_OPEN_SNAPSHOT)

' Record and return the ID for this function.
DISPLAYID = Timer
AddAllToList = DISPLAYID

Case LB_OPEN
AddAllToList = DISPLAYID

Case LB_GETROWCOUNT
' Return the number of rows in the recordset.
rs.MoveLast
AddAllToList = rs.RecordCount + 1

Case LB_GETCOLUMNCOUNT
' Return the number of fields (columns) in the recordset.
AddAllToList = rs.Fields.Count

Case LB_GETCOLUMNWIDTH
AddAllToList = -1

Case LB_GETVALUE
' Are you requesting the first row?
If Row = 0 Then
' Should the column display "(All)"?
If Col = DISPLAYCOL - 1 Then
' If so, return "(All)."
AddAllToList = DISPLAYTEXT
Else
' Otherwise, return NULL.
AddAllToList = Null
End If
Else
' Grab the record and field for the specified row/column.
rs.MoveFirst
rs.Move Row - 1
AddAllToList = rs(Col)
End If
Case LB_END
DISPLAYID = 0
rs.Close
End Select

Bye_AddAllToList:
Exit Function

Err_AddAllToList:
'Beep: MsgBox Error$, 16, "AddAllToList" '10/5/2006
AddAllToList = False
Resume Bye_AddAllToList
End Function