find as you type not working (1 Viewer)

[batwings]

Hi there
I'm struggling with the code below,
I followed the instructions but I am getting a Parameter Query request "Enter paramater value" SortOrder. when I try to type a character into the combo box.


I followed the instructions below

1. <LI itxtvisited="1">Create a combo box on a form. <LI itxtvisited="1">Create an event procedure for the combo box's OnChange event. <LI itxtvisited="1">Copy and paste the code, below, into the OnChange procedure <LI itxtvisited="1">Replace "Combo0" with the name of your combo box. <LI itxtvisited="1">Change the strSQL strings to match your table and fields.
2. You're done.

I created a table called tName with 2 fields nameKey (autonumber PK) and Name.

The code used is this that I found on another site but I am confused about the following, below it says Column count 3, I only have 2 columns:-

' Form design settings:
' Set AutoExpand to No
' Column Count 3
' Keyed on column 1 (record primary key)
' Showing column 2 (user-readable data) column 2 width > 0
' First and Second column width=0

Private Sub Combo0_Change()
' Function Description:
' Filter a combo box list as the user types, similarly to how application
' launchers like Colibri, AppRocket and LaunchBar opperate.
    ' e.g. if the list contains the names of U.S. Presidents, and
    ' the user types "gw," then the resulting SQL WHERE clause will
    ' look like "Name Like '*g*w*'" and the resulting list
    ' will include George Washington, George H. W. Bush and
    ' George W. Bush, among others.
    ' The order is preserved, so that typing "wg" creates an SQL WHERE
    ' clause like "Name Like '*w*g*'" and the resulting list would
    ' include George Washington but not the Bushes.
 
' This is accomplished by grabbing the text typed by the user in the
' combo box's edit field, creating an SQL SELECT statement from it,
' and finally applying that SQL statement to the combo box's
' .RowSource property.
 
' Form design settings:
'  Set AutoExpand to No
'  Column Count 3
'  Keyed on column 1 (record primary key)
'  Showing column 2 (user-readable data) column 2 width > 0
'  First and Second column width=0
 
Dim strText, strFind
 
' Get the text that the user has typed into the combo box editable field.
strText = Me.Combo0.Text
 
' If the user has typed something in, then filter the combobox
' list to limit the visible records to those that contain the
' typed letters.
' Otherwise (if the field is blank), the user has deleted whatever
' text they typed, so show the entire (unfiltered) list
If Len(Trim(strText)) > 0 Then
    ' Show the list with only those items containing the typed
    ' letters.
 
    ' Create an SQL query string for the WHERE clause of the SQL
    ' SELECT statement.
    strFind = "Name Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            ' When adding another character, remove the
            ' previous "*," otherwise you end up with
            ' "*g**w*" instead of "*g*w*."
            ' This has no apparent impact on the user, but
            ' ensures that the SQL looks as intended.
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"
 
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    strSQL = "SELECT tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
    strFind & " ORDER BY SortOrder;"
 
    '' NOTE: to remove the order requirement, such that typing "wg"
    '' and "gw" return the same results, the SQL WHERE clause needs
    '' to look like "Name Like '*w* AND *g*'."
    '' The code above should be changed as follows:
    ''For i = 1 To Len(Trim(strText))
    ''   strFind = strFind & "Name Like '*" & Mid(strText, i, 1) & "*' And "
    ''Next
    ''
    ''strSQL = "SELECT tName.nameKey, tName.Name, SortOrder from tblApps Where " & _
    ''Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
 
    ' Filter the combo list records using the new SQL statement.
    Me.Combo0.RowSource = strSQL
 
Else
    ' Show the entire list.
    strSQL = "SELECT tName.nameKey, tName.Name, tName.SortOrder FROM tName ORDER BY tName.SortOrder; "
    Me.Combo0.RowSource = strSQL
End If
 
' Make sure the combobox is open so the user
' can see the items available on list.
Me.Combo0.Dropdown
 
End Sub

Hopefully someone can explain where I am going wrong with this..

 

[WayneRyan]

batwings,

Given your SQL:

SELECT tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
ORDER BY SortOrder;"

I'd say that SortOrder isn't found in your table tName.

Remove it from the code or change it to the proper sort field:

strSQL = "SELECT tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
strFind & " ORDER BY SortOrder;"

Wayne