How do I get Function AddAllToList to work for me

[mmaule]

How do I get this Microsoft Function to work with a ComboBox that has a source of SELECT DISTINCT StatusUnit.System FROM StatusUnit ORDER BY StatusUnit.System; and using a single column?
The Function is suppose to place "(All)" at the top of the list, but all I get is a blank space.

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
' strDisplayText = "(All)"
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 by another control!"
AddAllToList = False
Exit Function ' AddAllToList
End If ' lngDisplayID
' Parse the display column and display text from Tag property.
intDisplayCol = 1
strDisplayText = "(All)"
If Not IsNull(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 ' intSemiColon
End If ' Not IsNull
' 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 = intDisplayCol - 1 Then
AddAllToList = strDisplayText
Else
AddAllToList = Null
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 ' AddAllToList
Err_AddAllToList:
MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList"
AddAllToList = False
Resume Bye_AddAllToList
End Function ' AddAllToList

[ranman256]

I too use the ALL option in my combo, but THIS got over complicated.

The combo should be assigned to a query. (not built internally)
One of the options should be, when sorted, the "(All)" will be on the top.
(I used a UNION query to marry the regular choices with the ALL choice)

The combo after update should run either the chosen pick or the ALL event.

[mmaule]

Thank you for your reply.
Sorry I didn't acknowledge your reply sooner, I didn't receive a notice and thought no one replied, so I was going to send my request in a different format.
I am not versed in using Unions.
I know I could just add another record with "(All)" in the appropriate field, but then I have to test for and ignore it in my output.
Its just a shame that I don't know enough to interpret the AddAllToList function and tweek it.

[rpeare]

this function is expecting you to pass it these values:

ctl As Control,
lngID As Long,
lngRow As Long,
lngCol As Long,
intCode As Integer

Are you in fact doing that?
What is the value you have in your combobox TAG property?
what is the code you are using to call this function?

[mmaule]

I found the problem with the AddAllToList function. The function was expexting two columns and I only needed the text from one column to build a filter.
In the function the Case acLBGetValue has:
If lngCol = intDisplayCol - 1 Then
AddAllToList = strDisplayText
All I had to do was get rid of the "- 1", and the function works great.

The passed values, the function looks for are provided by the Access application.

Thank you for your replies.