Here is the code that I am working with. It appears that the problem is with
the On Change event for the input, which shows that the value that I see in
the code is always one character behind what has been keyed (just like the
most recent character has not reached my On Change event code). When the
first character is keyed, the program sees a null "txtSearch" value.
Regardless, when I build my update query, the value in the LIKE clause in
never correct. Let me know what other information you need. Thanks...
CODE:
Option Compare Database
Dim qryProj As String, qryDesc As String
Private Sub Form_Open(Cancel As Integer)
' The List Box is displayed in either of two sequences-- by Item #, or
by Description
qryProj = "SELECT [Item Number], [Description],[ID] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = True " & _
"ORDER BY [Item Number],[Description]"
qryDesc = "SELECT [Item Number], [Description] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = True " & _
"ORDER BY [Description],[Item Number]"
Me!lstSearch.RowSource = qryProj
Me!lstSearch.Requery
End Sub
Private Sub btnSearch_Click()
' This should be entered when the "Search" button is clicked. This is an
alternate method
If Len(Me![txtSearch]) > 0 Then
If Me!optSeq.value = 1 Then
' sequence is by Item #
lstSearch.RowSource = "SELECT [Item Number],[Description] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = true AND [Item Number] LIKE '" &
Me![txtSearch].value & "*' " & _
"ORDER BY [Item Number],[ID]"
Else
' sequence is by Description
lstSearch.RowSource = "SELECT [Item Number],[Description] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = true AND [Description] LIKE '" &
Me![txtSearch].value & "*' " & _
"ORDER BY [Description]"
End If
Me!lstSearch.Requery
Me.Repaint
End If
End Sub
Private Sub optSeq_AfterUpdate()
' This code determines which sequence to use for the List Box display
(lstSearch)
If Me!optSeq.value = 1 Then
' change sequence to Item #
Me!lstSearch.RowSource = qryProj
Else
'change sequence to description
Me!lstSearch.RowSource = qryDesc
End If
Me!lstSearch.Requery
End Sub
Private Sub txtSearch_Change()
' This should be entered with each key stroke in the 'txtSearch' text box
If Len(Me![txtSearch]) > 0 Then
If Me!optSeq.value = 1 Then
lstSearch.RowSource = "SELECT [Item Number],[Description] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = true AND [Item Number] LIKE '" &
Me![txtSearch].value & "*' " & _
"ORDER BY [Item Number]"
Else
lstSearch.RowSource = "SELECT [Item Number],[Description] " & _
"FROM [Approved Item Numbers] " & _
"WHERE [Status] = true AND [Description] LIKE '" &
Me![txtSearch].value & "*' " & _
"ORDER BY [Description]"
End If
Me!lstSearch.Requery
Me.Repaint
End If
End Sub