SQL filter Code Not working

[Paul Cooke]

Hi guys - hopefully someone can point me in the right direction as to why this is not working please?

I have a table called PatientDetails

I have a form called SearchPatient

In my form I have a textbox called txtName
and
A listbox called lstSearchResults

The rowource for the listbox is

SELECT PatientDetails.PatientID, PatientDetails.PatientFirstName, PatientDetails.PatientSurname, PatientDetails.PatientDOB, PatientDetails.HomeAddressPostCode FROM PatientDetails;

When I open the form all the relevant data is shown in the listbox.

In the on change event of the textbox - txtName I have the following code

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 as soon as I type something in the textbox on the form all the data dissapears in the listbox. Even if I delete the values in the textbox the listbox remains empty unless I close and re-open the form.

What I am trying to accomplish is when a letter is typed into the textbox the listbox is filtered down to show only those records relating to the text typed into the textbox.

Hopefully that makes sense and I would be really grateful for any advice or solutions you can offer me as I have spent all day on this now !

Many thanks in advance

Paul

 

[pbaldy]

Add

Debug.Print strSource

right before setting the listbox rowsource. It will print out the finished SQL to the VBA Immediate window. Right off it looks like you won't have a space before the word "FROM".

 

[pbaldy]

Also, I'm not sure how Like and wildcards will work with a date field, if that's what the DOB field is. That value would have be surrounded by # instead of ' as well. If nothing else, you might want to take that one out until you get the text only fields working.

 

[Paul Cooke]

Hi Paul many thanks for the repllies and advice.

I came back to it this morning and after a bit of tweaking I now have the code working as below

Private Sub txtSearchItem_Change()
On Error GoTo Err_txtProperty_Change
Dim strSource As String

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

Me.lstSearchResults.RowSource = strSource

Exit_txtProperty_Change: Exit Sub
Err_txtProperty_Change: MsgBox Err.Number & " " & Err.Description
Resume Exit_txtProperty_Change

End Sub

I tried replacing the PatientDOB value with # instead of ' but it didn't seem to like it for some reason (although I'm not 100% sure I put it in the right place !)

One other question I would like to ask your advice on is if I want to add fields into the strsource from another table how do I add them to the From line?

Many thanks again

Paul

 

[pbaldy]

You'd obviously add the fields to the SELECT clause, and the table to the FROM clause. You'd join the two tables on the key field(s) they share. It might be easiest to create a query in design view and then switch to SQL view to see how it looks, but it would typically look like:

FROM Table1 INNER JOIN Table2 ON Table1.KeyFieldName = Table2.KeyFieldName

 

[Paul Cooke]

[QUOTE
FROM Table1 INNER JOIN Table2 ON Table1.KeyFieldName = Table2.KeyFieldName[/QUOTE]

Thanks Paul I will give it a go tomorrow as a cold beer beckons in a moment !

Just to confirm though I f I understand you correctly Table1.KeyFieldName would be the PK of my table1 and Table2.KeyFieldName would be the Fk in table 2 ?

thanks so much for your replies

 

[pbaldy]

Yes, PK in one FK in the other. It is pub time over there, isn't it? You may run into my daughter; she lives in England and has been known to visit the pubs!

 

[Paul Cooke]

Thanks Paul - and yes it was Pub time and now it suffering time !!

thanks again for all your help !

 

[pbaldy]

No problem; we Paul's have to stick together!