Add an "All" option to a combo box

[MrsSpalla]

Oh help!

I'm trying to add the option of choosing "all" to my combo box. My row source is pulling the data correctly from my table and the form works wonderfully. But then when I try to add in my code to add an "All" function, it stops working. Any ideas?

Here's the code I'm using. It came from msdn.microsoft.com/en-us/libr....12).aspx#Y515:

Function AddAllToList(ctl As Control, lngID As Long, _
lngRow As Long, lngCol As Long, _
intCode As Integer) As Variant
Static dbs As Database, rst As Recordset
Static lngDisplayID As Long
Static intDisplayCol As Integer
Static strDisplayText As String
Dim intSemiColon As Integer
On Error GoTo Err_AddAllToList
Select Case intCode
Case acLBInitialize
' See if function is already in use.
If lngDisplayID <> 0 Then
MsgBox "AddAllToList is already in use!"
AddAllToList = False
Exit Function
End If
' Parse the display column and display text
' from the Tag property.
intDisplayCol = 1
strDisplayText = "(All)"
If Not IsNull(ctl.Tag) And (ctl.Tag <> "") Then
intSemiColon = InStr(ctl.Tag, ";")
If intSemiColon = 0 Then
intDisplayCol = Val(ctl.Tag)
Else
intDisplayCol = _
Val(Left(ctl.Tag, intSemiColon - 1))
strDisplayText = Mid(ctl.Tag, intSemiColon + 1)
End If
End If
' Open the recordset defined in the
' RowSource property.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(ctl.RowSource, _
dbOpenSnapshot)
' Record and return the lngID for this
' function.
lngDisplayID = Timer
AddAllToList = lngDisplayID
Case acLBOpen
AddAllToList = lngDisplayID
Case acLBGetRowCount
' Return number of rows in recordset.
On Error Resume Next
rst.MoveLast
AddAllToList = rst.RecordCount + 1
Case acLBGetColumnCount
' Return number of fields (columns) in recordset.
AddAllToList = rst.Fields.Count
Case acLBGetColumnWidth
AddAllToList = -1
Case acLBGetValue
If lngRow = 0 Then
If lngCol = 0 Or lngCol = intDisplayCol Then
AddAllToList = strDisplayText
End If
Else
rst.MoveFirst
rst.Move lngRow - 1
AddAllToList = rst(lngCol)
End If
Case acLBEnd
lngDisplayID = 0
rst.Close
End Select
Bye_AddAllToList:
Exit Function
Err_AddAllToList:
MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList"
AddAllToList = False
Resume Bye_AddAllToList
End Function

I then change my row source type to "AddAllToList".

And then the combo box quits working.

Any suggestions would be appreciated!!!

 

[John Big Booty]

Perhaps the discussion and link in this thread will give you some pointers.

 

[Rx_]

This one of many methods- combo box with Areas - dozens of reports use the same combobox.

See Attachment: Wells are grouped in an Area. The combo box displays a area name and <Print All>
ID_Area is returned or 0 is returned. (or multiple selections)

SELECT Wells_Areas.ID_Area, Wells_Areas.Area FROM Wells_Areas GROUP BY Wells_Areas.ID_Area, Wells_Areas.Area ORDER BY Wells_Areas.Area UNION SELECT 0,'<Print All>'   FROM wells_Areas
ORDER BY 2;

My preference for generating a report is to collect the combobox selection, turn it into a SQL Ready text, then pass that into a Function.
The function completes the SQL Statement using the SelectedAreas Text in the Where statement:

SelectedAreas = ""
For Each varItm In Me.lstRegionsReports.ItemsSelected
    If SelectedAreas = "" Then
           SelectedAreas = Me.lstRegionsReports.ItemData(varItm)
    Else
           SelectedAreas = SelectedAreas & ", " & Me.lstRegionsReports.ItemData(varItm)
    End If
Next
 
If SelectedAreas = "0" Then
  SQLInFunction = " Not In (1000) "
Else
  SQLInFunction = " In (" & SelectedAreas & ") "
End If

' SelectedAreas passed into function to become ID_Area
[B]WHERE [/B]((([002_PW_Report].Activity)='A') AND (([002_PW_Report].ID_Area) " [B]& ID_Area &[/B] "))"