combobox no longer does an autocomplete

[FancyPrairie]

I have a combobox that, when populated, exceeds the 64k limit. To get around the problem I programmed the onchange event so that when the user enters the first character I set the rowsource of the combobox (see code example 1 below). And, as the user continues to type, the combobox autocompletes. This worked fine for about a week and then stopped working. I haved added additional code (see code example 2) to try to resolve the problem. It works on my machine but not on my users' machines. (Note, they have Access 2007 I have 2010)

Why won't my auto complete work now? Is there a work around?

NOTE: I have other comboboxes on the same form that autocomplete ok (the 64k limit is not an issue with them).

Example 1: (This works on my machine (2010) but not on user's (2007))

Private Sub cboSearch_Change()
    Static strChar1 As String
    Dim strSQL As String
    
    If (strChar1 <> Mid(cboSearch.Text, 1, 1)) Then
        strChar1 = Mid(cboSearch.Text, 1, 1)
        strSQL = "SELECT DISTINCT lngID, strFullName From MyTable WHERE strFullName Like '" & strChar1 & "*' ORDER BY strFullName;"
        cboSearch.RowSource = strSQL
    End If
    
End Sub

Example 2: (This works on my machine but not on user's)

Private Sub cboSearch_Change()
    Static strChar1 As String
    Dim strSQL As String
    
    If (strChar1 <> Mid(cboSearch.Text, 1, 1)) Then
        strChar1 = Mid(cboSearch.Text, 1, 1)
        strSQL = "SELECT DISTINCT lngID, strFullName From MyTable WHERE strFullName Like '" & strChar1 & "*' ORDER BY strFullName;"
        cboSearch.RowSource = strSQL

        cboSearch.value = cboSearch.ItemData(0)

        '**** note that it used to work on my machine without the following 3 lines...now they are needed

        cboSearch.SelStart = 0
        cboSearch.SelLength = Len(cboSearch.Text)
        SendKeys strChar1, False
    End If
    
End Sub

 

[kjv1611]

Have you tried creating a new combo-box to replace the existing one, and then see if it gets around the 64k issue? Best I could find in a quick search, it sounds like there is no such issue with modern versions of Access.

Also, I wonder if it could be an odd issue from using multiple versions of Acces to connect to the same database. I had a situation like that a while back, which when converting the database back to .mdb format, the issue went away. Might be worth trying that as well just to see if it corrects the issue that way.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[missinglinq]

Two things to consider:

When apps run fine on one machine and not on another, or run fine in one version and not when run under another version, one the first thing you have to think about are Missing References.

If you haven't checked for this kind of thing before, here are Doug Steele's detailed instructions on how to troubleshoot the problem:

Access Reference Problems

Also, since we're talking about 2007/2010, on the errant machines have the Folders been declared as Trusted? Does any code run on these machines?

Linq ;0)>


The Missinglinq

Richmond, Virginia

The Devil's in the Details!