Displaying add'l value in Combo box

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

I have a combo box that uses a table as its row source. When the combo box
displays, I would like it to display an add'l value not in the table, e.g.
"<All>"
 
I find the easiest way is to provide a listfill function. Here's mine:

Public Function AddAllToList(c As Control, id As Variant, row As Variant,
col As Variant, code As Variant) As Variant
' ListFill function - use as RowSourceType for listbox
' Set listbox.rowsource to correct sql
' Set listbox.tag to (eg) 2;<<All>> to override defaults:
' 1;( All )

Static rs As Recordset
Static InstanceId As Long
Static displayCol As Integer, displayText As String
'
On Error GoTo AddAllToList_Err
'
Select Case code
Case acLBInitialize
If InstanceId Then
ErrBox "AddAllToList is already in use by another control."
AddAllToList = False
Exit Function
End If
'
displayCol = 1
displayText = "( All )"
If Not IsNull(c.Tag) Then
p% = InStr(c.Tag, ";")
If p% = 0 Then
displayCol = Val(c.Tag)
Else
displayCol = Val(Left(c.Tag, p% - 1))
displayText = Mid(c.Tag, p% + 1)
End If
End If
'
Set rs = CurrentDb.OpenRecordset(c.RowSource, dbOpenSnapshot)
'
InstanceId = Timer
AddAllToList = InstanceId
Case acLBOpen
AddAllToList = InstanceId
Case acLBGetRowCount
rs.MoveLast
AddAllToList = rs.RecordCount
Case acLBGetColumnCount
AddAllToList = rs.Fields.Count
Case acLBGetColumnWidth
AddAllToList = -1
Case acLBGetValue
If row = 0 Then
If col = displayCol - 1 Then
AddAllToList = displayText
Else
AddAllToList = Null
End If
Else
rs.MoveFirst
rs.Move row
AddAllToList = rs(col)
End If
Case acLBEnd
InstanceId = False
rs.Close: Set rs = Nothing
Case Else
End Select

AddAllToList_Exit:
On Error Resume Next
rs.Close: Set rs = Nothing
Exit Function

AddAllToList_Err:
ErrBox Err.Description & " - AddAllToList"
AddAllToList = False
Resume AddAllToList_Exit

End Function

To use this function, type the word 'AddAllToList' (without quotes) into the
RowSourceType property of the listbox.
 
Thanks,

I also tried a union query for the record souce using a table with one
record containing the value I want - seems to work fine.
 
Back
Top