Combos with Tens of Thousands of Records Question 1

[Genimuse]

There's an excellent FAQ here, faq181-1202, that describes how to leave the rowsource of a combo blank until the user has typed a number of characters. It's pretty slick, but I keep getting a SELECT syntax error that I don't understand.

My code in the combo's OnChange looks like this:

Private Sub cboDonorList_Change()
    Dim cbo As ComboBox
    Dim sText As String

    Set cbo = Me.cboDonorList
    sText = cbo.Text
    Select Case sText
    Case " "           ' Remove initial space
        cbo = Null
    Case Else          ' Reload RowSource data.
        Call ReloadDonorList(sText)
    End Select
    Set cbo = Nothing
End Sub

My ReloadDonorList, which is also in the Form's module looks like this:

Function ReloadDonorList(strDonor As String)
    Dim strNewStub As String

    strNewStub = Nz(Left(strDonor, conDonorMin), "")
    ' If first n chars are the same as previously, do nothing.
    If strNewStub <> strDonorStub Then
        If Len(strNewStub) < conDonorMin Then
            'Remove the RowSource
            Me.cboDonorList.RowSource = "SELECT [Donor].[ID], [Donor].[LastName] AS FullName WHERE (False);"
            strDonorStub = ""
        Else
            'New RowSource
            Me.cboDonorList.RowSource = "SELECT [Donor].[ID], BuildFullName([Donor].[LastName],[Donor].[FirstName],[Donor].[MiddleName],[Donor].[NickName]) AS FullName FROM Donor WHERE [Donor].[LastName] LIKE """ & strNewStub & "*"" ORDER BY [Donor].[LastName], [Donor].[FirstName], [Donor].[MiddleName], [Donor].[NickName];"
            strDonorStub = strNewStub
        End If
    End If
End Function

and lastly, the BuildFullName function, which is in its own module, looks like this:

Public Function BuildFullName(strLast As Variant, strFirst As Variant, strMiddle As Variant, strNick As Variant) As String
    Dim strFullName As String
    strFullName = ""
    
    If Not IsNull(strLast) Then
        strFullName = strLast
    End If
    If Not IsNull(strLast) And (Not IsNull(strFirst) Or Not IsNull(strMiddle) Or Not IsNull(strNick)) Then
        strFullName = strFullName & ", "
    End If
    If Not IsNull(strFirst) Then
        strFullName = strFullName & strFirst
    End If
    If Not IsNull(strMiddle) And (Not IsNull(strLast) Or Not IsNull(strFirst)) Then
        strFullName = strFullName & " " & strMiddle
    End If
    If Not IsNull(strNick) And (Not IsNull(strLast) Or Not IsNull(strFirst)) Then
        strFullName = strFullName & " (" & strNick & ")"
    End If
    BuildFullName = strFullName
End Function

The error I receive, upon typing a letter, is this:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Except that I'm almost positive things are ok.

Sorry for the big chunks of code. Does anything stand out? All of the fields are simply strings, and if I replace the

LIKE """ & strNewStub & "*""

in the SQL with, say,

LIKE "fred*"

and run it as its own query, it runs fine.

 

[PHV]

Replace this:
Me.cboDonorList.RowSource = "SELECT [Donor].[ID], [Donor].[LastName] AS FullName WHERE (False);"
By this:
Me.cboDonorList.RowSource = "SELECT [Donor].[ID], [Donor].[LastName] AS FullName [highlight]FROM Donor [/highlight]WHERE 1=0;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

Me.cboDonorList.RowSource = "SELECT [Donor].[ID], [Donor].[LastName] AS FullName FROM Donor WHERE 1=0;"

This works, but why use it?

Surely

Me.cboDonorList.RowSource = "" is neater and does the same job?

I've used this method for 7/8 years and have never had a problem, and in fact I don't think that I would have ever have thought up the other convoluted solution.

 

[PHV]

This works, but why use it?
In case cboDonorList.ColumnHeads is true ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

Good point, I hadn't thought of that since I never use column headings.

 

[Genimuse]

Doh! I was looking at the wrong SQL for the problem. Logically, though, the mistake would have to be there, during the first few letters.

Thanks. And good point about just setting the rowsource to "", since I'm not using column headings.