Filter Listbox via Textbox (1 Viewer)

[tmyers]

Today is just not my day for SQL writing apparently.

Private Sub ContractorSearch_Change()

Dim strsql As String
    
    strsql = "SELECT tblContractors FROM tblContractors.Contractor"
    strsql = strsql & " WHERE Contractor LIKE '*" & Me.ContractorSearch & "*'"

    Me.lstContractors.RowSource = strsql
    Me.lstContractors.Requery
    
End Sub

Filtering the lstContractor listbox via ContractorSearch textbox. This is not working in that the moment you start typing, the listbox becomes blank. I assume I made a mistake in the SQL statement.

 

[pbaldy]

In the change event you'd have to use the .Text property of the textbox. Typically you don't want to react to every keystroke, so you'd use the after update event instead of the change event.

 

[tmyers]

In the change event you'd have to use the .Text property of the textbox. Typically you don't want to react to every keystroke, so you'd use the after update event instead of the change event.

You are quite right. I meant to have it as an after update and did on change by mistake.
I did however add .text and it still resulted in the listbox going empty.

 

[pbaldy]

You wouldn't need the .Text property in the after update event, but it shouldn't hurt you. Try using this method to see what strSQL is after setting it but before setting the row source:

Debugging

 

[pbaldy]

Also, what was the row source before this runs? Do you perhaps have a hidden column that you're not including here?

Plus in my experience you don't need the Requery line.

 

[tmyers]

SELECT tblContractors FROM tblContractors.Contractor WHERE Contractor LIKE '*g*'
That is what it output when i put "g" into the textbox

 

[pbaldy]

Check post 5 as I snuck it in while you were typing.

 

[tmyers]

Also, what was the row source before this runs? Do you perhaps have a hidden column that you're not including here?

Plus in my experience you don't need the Requery line.

SELECT tblContractors.ContractorID, tblContractors.Contractor
FROM tblContractors
WHERE (((Exists (SELECT ContractorID FROM tblContractorJob where
tblContractors.ContractorID = tblContractorJob.ContractorID and tblContractorJob.JobID = [Forms]![JobQuote]![JobID]))=False) AND ((tblContractors.IsActive)=True))
ORDER BY tblContractors.Contractor;

 

[pbaldy]

There you go, you have to include the ID field. The first column is probably hidden and you're not populating a second.

 

[tmyers]

I was conflicted on the requery line. I just kinda went with better safe then sorry on it.

 

[tmyers]

There you go, you have to include the ID field. The first column is probably hidden and you're not populating a second.

Doh. That made it work.

 

[pbaldy]

I was conflicted on the requery line. I just kinda went with better safe then sorry on it.

Not a bad philosophy.

 

[pbaldy]

Doh. That made it work.

Excellent! Glad we got it working.

 

[tmyers]

Excellent! Glad we got it working.

Thank you for the help.
Been doing QoL stuff such as search boxes and filters etc. Most of the items I am doing those to I made months ago so even I don't remember what I did to make them work.

Ill probably be cross eyed before the day is out.

 

[pbaldy]

Happy to help. If it takes you months to forget, you're doing way better than I am.