Primary search and secondary search

[Lau via AccessMonster.com]

I have a search form that shows the results in a list box. If a search is
found, then displays it. If an exact match is not found, do a secondary
search using wildcard. I have the codes below, but they not work. I hope
somebody can shade some lights! Thanks.

------------------------------
Private Sub PerformSearchTerm()
Dim strWhere As String

If IsNull(Me.txtTerm) Then
MsgBox "Please enter term for search", vbOKOnly
Me.txtTerm.SetFocus
Else
' primary search
strWhere = "SELECT TermID, SpeechId, Term FROM tblTerm WHERE [Term] = '"
& Me.txtTerm & _
" ORDER BY Term;"
Me.lstSearchResults.RowSource = strWhere
If Me.lstSearchResults.ListCount = 0 Then
' secondary search
strWhere = "SELECT TermID, SpeechId, Term FROM tblTerm WHERE [Term]
LIKE '" & Me.txtTerm & "*'" & _
" ORDER BY Term;"

Me.lstSearchResults.RowSource = strWhere
If Me.lstSearchResults.ListCount = 0 Then
MsgBox "No records found", vbOKOnly
Me.lstSearchResults.Enabled = False 'not found data
'Me.lstSearchResults.Locked = True
Me.txtTerm.SetFocus
End If

End If 'me.lst

If Me.lstSearchResults.ListCount > 0 Then
Me.lstSearchResults.Enabled = True 'found data
End If

End If
End Sub

 

[Tom van Stiphout]

On Thu, 04 Dec 2008 18:59:16 GMT, "Lau via AccessMonster.com"

ONE small quote character missing in the first strWhere (better:
strSQL), just before ORDER BY.

-Tom.
Microsoft Access MVP

 

[Lau via AccessMonster.com]

ONE small quote character missing in the first strWhere (better:
strSQL), just before ORDER BY.

-Tom.
Microsoft Access MVP

Oh, thank you! That was a hard one, isn't it?