finding a record in a listbox part 2

[mgwat69]

I am a volunteer at a retirement center and helped design a kiosk which visitors use to find the room number and telephone number of a resident. We are using a touch screen monitor and a virtual keyboard. Currently the user types the first letter of the resident’s last name and the list box scrolls and highlights the first alphabetical name that starts with that letter. The user then scrolls to find the information of the resident they are looking for. We are trying to modify the application to allow the user to type in the first few letters on the resident’s last name. I found an application on the internet that nearly does what we want, except it does not highlight a name as it scrolls and if the user types a letter that no resident’s name begins with the list box goes blank and there is no way to get the list box contents back. I added a text box and a command button to my original application. Here is the code I have tried as I have adapted it.

Option Compare Database
Option Explicit
Private blnSpace As Boolean

Private Sub Ctl_cmdNewSearch_Click()
Me.txtSearch.Value = ""
Me.txtSearch.SetFocus


Me.Requery.List0
End Sub

Private Sub txtSearch_Change()
'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
Dim strFullList As String
Dim strFilteredList As String

10 If blnSpace = False Then
20 Me.Refresh 'refresh to make sure the text box changes are actually available to use

'specify the default/full rowsource for the control
30 strFullList = "SELECT res_last_name, res_first_name, res_middle_name, spell_build, curr_room_nbr, curr_bed, phone FROM qryKiosk ORDER BY [res_last_name]; "

'specify the way you want the rowsource to be filtered based on the user's entry
40 strFilteredList = "SELECT res_last_name, res_first_name, res_middle_name, spell_build, curr_room_nbr, curr_bed FROM qryKiosk WHERE [res_last_name] LIKE ""*" & Me.txtSearch.Value & _
"*"" ORDER BY [res_last_name]"
'run the search
50 fLiveSearch Me.txtSearch, Me.List0, strFullList, strFilteredList

60 End If

End Sub
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
'IN WHICH CASE WE WANT TO IGNORE THE INPUT

10 On Error GoTo err_handle

20 If KeyAscii = 32 Then
30 blnSpace = True
40 Else
50 blnSpace = False
60 End If
Exit Sub
err_handle:
80 Select Case Err.Number
Case Else
90 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
vbCrLf & "Error " & Err.Number & "(" & Erl & ")"
100 End Select
End Sub

When I type into the text box txtSearch I get the message:
“Error accessing file. Network connection may have been lost”
Then the debugger highlights the line:
Private Sub txtSearch_Change()
Or the line:
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
Also, the cmdNewSearch button does not erase the contents of txtSearch nor does it put focus there.
For some reason the code generator automatically put Ctl in front of cmdNewSearch line
Private Sub Ctl_cmdNewSearch_Click()

[June7]

The interface is an Access project? Is the combobox AutoExpand property set to Yes?

[mgwat69]

We are using Access 2003 and the code is Visual Basic. The application uses a list box not a combo box. The row source is:
SELECT res_last_name, res_first_name, res_middle_name, spell_build, curr_room_nbr, curr_bed, phone FROM qryKiosk ORDER BY [res_last_name]
We tried a combo box when we first designed the kiosk but it only displayed last name when the user typed in a letter

[June7]

You can have combobox or listbox show full name as single value by concatenating the name fields.

SELECT res_last_name & ", " & res_first_name & " " & res_middle_name As FullName, spell_build, curr_room_nbr, curr_bed, phone FROM qryKiosk ORDER BY [res_last_name]

So the combobox AutoExpand property would complete the full name as user types characters.

[mgwat69]

June7
Thanks for your help. I left everything in columns because that is how our users are used to seeing the list box that we are currently using.
The combo box works great with one small thing I do not like. When you first start the application, you have to click on the gray arrow in the top section of the combo box in order to get the bottom of the combo box to show up,. Also if you click on the name in the top of the combo box, the bottom of the combo box disappears. Is there a way to make the bottom of the combo box show up all the time.

[June7]

You can use the Dropdown method of a combobox to cause the drop list to open when the combobox gets focus or is entered. Examples:

Private Sub cbxLabNum_GotFocus()
Me.cbxLabNum.Dropdown
End Sub

Private Sub cbxBrandType_Enter()
Me.cbxBrandType.Dropdown
End Sub

Selecting any item of the droplist, not just the first, should cause it to close. If you want it to stay open then use then also use AfterUpdate event with the Dropdown method.

[mgwat69]

June7,
I tried adding a command button to the form with the following code:

Option Compare Database
Private Sub cmdNewSearch_Click()
Me.Combo0.Value = ""
Me.Combo0.SetFocus
Me.Combo0.Dropdown
End Sub

When I click on it I get the error message:
"Error accessing file. Network connection may have been lost."
Any suggestions?

[June7]

Not familiar with that error.

If you want to provide project for analysis, I will look at.

[mgwat69]

Thank you, I appreciate your continued attention. I surfed the net myself and the only thing I have found so far is to "make sure you are using Access 2003." I double checked and indeed that is the case.

[mgwat69]

June7,
I am not sure what you need from me next

[June7]

Look at my examples. The Dropdown code is in event of the combobox, not a button Click.

If you want further help, post project for analysis.

[mgwat69]

Because of privacy issues I will have to build a substitute table for the main table in the database. This will take me a few days. Can I simply paste the database into a reply when I get it ready or do I have to do something else with it?

[June7]

Run Compact & Repair, zip if still large. Manage Attachments is available below the Advanced post editor.

[mgwat69]

Thank you for your patience
I think I attached OK

[June7]

Need more info. What form is the issue? Had trouble downloading that project. Got error messages about a bunch of forms that would not load. File is corrupted, can't analyse.