I have found this code to add the word "all" to a combo box. I suspect the code has been written for A97 and as I'm using A2000 I can't seem to get it to work. The error I'm getting is a type mismatch when the form with the combo box on it opens. Because this code seems to want the combo box properties for Record source type changed from Table/Query to AddAllToList, this seems to be causing the problem.
Here's the code that I have put in the module of the form with the combo box on it:
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"
AddAllToList = False
Resume Bye_AddAllToList
End Function
Thankyou for any replies
Rachael
Here's the code that I have put in the module of the form with the combo box on it:
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 (

' 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"
AddAllToList = False
Resume Bye_AddAllToList
End Function
Thankyou for any replies
Rachael