Searching With SQL on Switchboard

[WhizzkidWallace]

Further to my 'Enhanced Switchboard' thread, I almost have a great solution, but the last hurdle is causing me ENORMOUS frustration.

I have added a field to the 'Switchboard Items' table called 'Description', and it contains a description of what the item does, so the option to print a sales report has 'Print Sales Report' as the description. On the Switchboard I have a button, and some code to search the descriptions field for a user input search string, such as SALES.

Here is the code on the button:

Private Sub cmdMenuSearch_Click()
'   Search for a piece of text, and goto that switchboard
'   Define the variables for the search string and the switchboard number
    Dim intSwitchboard As Integer
    Dim strSearch As String
'   Get the search string from the user
    strSearch = InputBox("Enter search text", "Search for Switchboard Item", "Type search text here..")
    If strSearch = "" Or IsNull(strSearch) Then GoTo err_Error1
'   Create and open a recordset containing the search text
    Dim conTemp As ADODB.Connection
    Dim rsTemp As New ADODB.Recordset
    Set conTemp = CurrentProject.Connection
    rsTemp.ActiveConnection = conTemp
    Dim strSQL As String
    strSQL = "SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber, [Switchboard Items].Description FROM [Switchboard Items] WHERE ((([Switchboard Items].Description) Like '*'+'" & strSearch & "'+'*'))"
    rsTemp.Open strSQL
'   If no records found, report the error
    If (rsTemp.EOF) Then GoTo err_Error2
'   otherwise, go to the switchboard for the first item found
'   I will add code here to loop through all the records that
'   match the criteria, and ask the user which to execute
'   Get the switchboard number
    intSwitchboard = rsTemp!SwitchboardID
'   close the recordset
    rsTemp.Close
    Set rsTemp = Nothing
    Set conTemp = Nothing
'   Go to the selected switchboard
    Me.Filter = "[SwitchboardID] = " & Str(intSwitchboard) & " AND [ItemNumber] = 0"
    Me.FilterOn = True
    Exit Sub
    

err_Error1:
    MsgBox Err.Description
    
err_Error2:
    rsTemp.Close
    Set rsTemp = Nothing
    Set conTemp = Nothing
    MsgBox "No More Items Found!", vbInformation + vbOKOnly
    Exit Sub
    
End Sub

The problem is, it ALLWAYS says 'No More Items Found!"

I just can't see what is wrong. I know its all about the strSQL string, beause if I remove the wildcards, and enter a full known description, it works fine.

Please help

 

[WhizzkidWallace]

oh, and another nice Switchboard enhancement I thought was to use the 'Description' field above as the CONTROLTIPTEXT property for each button. A simple extra line in the Switchboard Code does this job nicely, so that when the user hovers the mouse over the button, a full description of what the button does can pop-up as a help tip.