Filter / text Query?

[Paul Cooke]

Hi guys,

could someone tell me if it possible to do the following please (also if it is practable!)

I have a form with a combo box called cboFindPatient and a listbox called
lstpatients.

What I would like to happen is as the user types a letter in the combo any patient whose name begins with that letter will show up in the listbox and as the user continues to type letters the lstbox requery's to show only those names with the relevant letters. So for example

p = Paul, Patrick, Phillip,
Pa = Paul, Patrick
pat = Patrick

I have no got a clue where to start on this or even if it is possible so would be very grateful for any advice or help you can offer.

Many thanks

Paul

 

[pbaldy]

Sure; you use the change event of the textbox (which requires using the .Text property) to reset the row source of the listbox to SQL that uses Like, the contents of the textbox and a wildcard. You know the combo can jump to the selected items in much the same way?

 

[Paul Cooke]

Thanks for the reply Paul - I know a bit of what you mean but could you please just clarify in more basic terms?

Firstly you say use the change event of the text box - is that a typo as I don't have a text box only a cbo and list box on the form or am i missing something here?

Wiih regard to the sql 'Like' would I simply use something along the lines of

Like *""*

I am only just really starting out on queries so do not have that much knowledge on them and learning as I go !

I would assume if I read / understand your post correctly the sql would be the rowsource of the listbox?

Again I would appricate it if you could explain in more basic terms

many thanks

 

[pbaldy]

I meant the combo (didn't understand having both combo and listbox). This type of thing:

Private Sub Text0_Change()
  Me.List2.RowSource = "SELECT [tblHRData].[EmpNum], [tblHRData].[EmpLastName] " _
                     & "FROM [tblHRData] " _
                     & "WHERE EmpLastName Like '" & Me.Text0.Text & "*'"
End Sub

 

[Paul Cooke]

Hi Paul thanks for the reply - I have researched your suggestions and will do a new post on this as it has changed somewhat the code I have now is

Private Sub txtName_Change()
On Error GoTo Err_txtName_Change
Dim strSource As String

strSource = "SELECT PatientFirstName, PatientSurname, PatientDOB, HomeAddressPostCode, PatientID" & _
"FROM PatientDetails " & _
"Where PatientFirstName Like '*" & Me.txtName.Text & "*' " _
& "Or PatientSurname Like '*" & Me.txtName.Text & "*' " _
& "Or PatientDOB Like '*" & Me.txtName.Text & "*' " _
& "Or HomeAddressPostCode Like '*" & Me.txtName.Text & "*' "
Me.lstSearchResults.RowSource = strSource



Exit_txtName_Change: Exit Sub
Err_txtName_Change: MsgBox Err.Number & " " & Err.Description
Resume Err_txtName_Change



End Sub

but it does not work !

Thanks again for your time

 

[pbaldy]

Thoughts posted on the other thread.