Use of AddAllToList with multiple comboboxes on same form

[SKelso]

I created a database in Access 2003 that contains an unbound search form with several combo boxes. Each combo box is populated with entries from the appropriate table. I want to allow the user to select individual criteria from each combo box, or select <ALL>. If I try to set the RowSourceType of more than 1 combo box to the AddAllToList function, I get the error that the function is already in use by another control.


How can I use the function with multiple combo boxes?

The best idea I have come up with is create multiple functions that are essentially the same function with different names and call each one once. I tried it and it does give the desired results but seems like a very inefficient work around.

Thanks,
Shawn

[missinglinq]

What is the AddAllToList function? I can find no reference to it in ACC2003.

Welcome to TheScripts!

Linq ;0)>

[ADezii]

I created a database in Access 2003 that contains an unbound search form with several combo boxes. Each combo box is populated with entries from the appropriate table. I want to allow the user to select individual criteria from each combo box, or select <ALL>. If I try to set the RowSourceType of more than 1 combo box to the AddAllToList function, I get the error that the function is already in use by another control.


How can I use the function with multiple combo boxes?

The best idea I have come up with is create multiple functions that are essentially the same function with different names and call each one once. I tried it and it does give the desired results but seems like a very inefficient work around.

Thanks,
Shawn

It makes perfect sense to me that a List Filling Callback Function cannot be duplicated as a RowSourceType for a Combo Box. These specifically designed Functions make use of Static Variables to retain their values between successive calls to the Function. Should another Combo Box make an inadvertant call to this Function at the inappropriate time, some unwelcome results may occur. I do not know this for a fact, I'm just stating that it makes sense to me. What about the other alternatives for populating your Combo Box?

1. Table
2. Query
3. Delimited List
4. Open Recordset

How about posting the AddAllToList() Function, with any other relevant information, and we'll see if we can come up with another alternative for the remaining Combo Boxes.

[SKelso]

It makes perfect sense to me that a List Filling Callback Function cannot be duplicated as a RowSourceType for a Combo Box. These specifically designed Functions make use of Static Variables to retain their values between successive calls to the Function. Should another Combo Box make an inadvertant call to this Function at the inappropriate time, some unwelcome results may occur. I do not know this for a fact, I'm just stating that it makes sense to me. What about the other alternatives for populating your Combo Box?

1. Table
2. Query
3. Delimited List
4. Open Recordset

How about posting the AddAllToList() Function, with any other relevant information, and we'll see if we can come up with another alternative for the remaining Combo Boxes.

Thanks for the help. I am populating the combo boxes from various fields within (3) different tables. The AddAllToList function simply adds an option to the list pulled from the table. In my case, the added item is <All>.

Here's the code:

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 should probably admit that this is the first time I have used Access in about 8 years. all of the stuff I did back then was very simplistic, so most of this is new to me.

Thanks Again,
Shawn

[ADezii]

1. From what you are telling me, the Row Sources of these Combo Boxes are populated by 3 different Tables, is this correct?
2. If the above is true, please post the Fields and their Data Types of the simplest Record Source (Table).
3. Post all the relevant information regarding the Combo Box itself (the one with the simplest Row Source), such as: ColumnCount, BoundColumn, ColumnWidths, etc.
4. We'll try to implement the <ALL> option by creating a Union Query unique to each individual Table.