finding a record in a listbox

[mgwat69]

I am a volunteer at a retirement center. I helped design a kiosk with a listbox that has a query a row source. We use a touchscreen monitor with a virtual keyboard. When the user types a single letter, the listbox scrolls to the first alphabetical name that starts with that letter. We would like to add a textbox which would allow the user to type several letters so that the listbox would scroll accordingly. We also would add a "New Search" button which would erase the textbox and set focus there. I tried the following adapted from a similar application that I found online:

Option Compare Database


Option Explicit

Private Sub TypeDown(strSearch As String)
On Error Resume Next

Dim strSQL As String

If strSearch = "" Then
strSQL = "qryKiosk.res_last_name"
Else
strSQL = "SELECT * FROM qry.Kiosk "_
"WHERE res_last_name Like '" & Me.txtSearch.Text & "*'"
List0.RowSource = strSQL
End If

End Sub

Private Sub btnNewSearch_Click()
Me.txtSearch.Value = ""
Me.txtSearch.SetFocus
End Sub

Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
TypeDown IIf(IsNull(Me.txtSearch), "", Me.txtSearch)
End Sub

When I type into txtSearch I get the error:
“Error accessing file. Network connection may have been lost.” The debugger does not show anything.
Also when I click on the btnNewSearch it does not erase the contents of the txtSearch nor does it put focus there.

Thank you in advance for your help.

[ssanfu]

You are almost there. You need to be aware that there are 2 properties to be concerned with: the .VALUE property and the .TEXT property.

The .VALUE property (the default property) holds the data after it has been committed. The .TEXT value is the property that holds the data until it is committed, which then replaces the current data. (BTW, the old data is still recoverable, to some extent, through the .OLDVALUE property)

In one place you used the .TXT property, but it was in the wrong place.

I also found where there was a misplaced 'dot':

strSQL = "SELECT * FROM qry.Kiosk "_

See the dot in the query name?


I have modified your code; it should run without errors.

Private Sub TypeDown(strSearch As String)
   On Error Resume Next

   Dim strSQL As String

   If strSearch = "" Then
      strSQL = ""
   Else
      strSQL = "SELECT * FROM qryKiosk WHERE res_last_name Like '" & strSearch & "*'"
   End If

   List0.RowSource = strSQL
End Sub

Private Sub btnNewSearch_Click()
   Me.txtSearch.Value = ""
   Me.txtSearch.SetFocus
   List0.RowSource = ""
End Sub

Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
   'if text box is null, replace with an empty string
   TypeDown NZ(Me.txtSearch.Text), "")

'  your way
'  TypeDown IIf(IsNull(Me.txtSearch.Text), "", Me.txtSearch.Text)
End Sub

[mgwat69]

I am still getting the error
"Error accessing file. Network connection may have been lost"
The debugger then highlights the line:
Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
But before you spend any time on that, my supervisor and I are not thrilled with the way the application from which I adapted the code from works. It filters the records instead of scrolling the list box. If you type in a letter and no name begins with that letter, the list box goes blank. I tried a ME.Requery in the search function, but it didn't refill the list box. I am going to surf the internet some more for different code but if you have any ideas, I would welcome them.