Listbox content

[mark curtis]

Dear all,

I have a form with an unbound listbox on.
I have 3 combo's
I have 2 text boxes.

What I want to do is let a user select an entry from 1 combo or enter data in 1 of the text boxes and then on an event like keyDown only return/populate records to the listbox that match the criteria.

I know that there is some SQL I could use for populating the RowSource but I am not to good on the SQL Syntax?

Thanks
Mark

 

[mission2java_78]

Look up 'LIKE' queries.

Jon

 

[mark curtis]

Jon, it is gonna take more than a like query because I have a set of fields I always want the list box to show but I want to filter that list based on one criteria either in the text box or combo?

 

[mission2java_78]

See my first reply...

If your listbox will show ALL records at first...and you want some tree type or flow down logic based on criteria then you still need a like query ... or a set query.

For instance you have a bunch of customers..your form opens up at first (on load / on open)

strSQL = "SELECT * FROM Customers" 'first show all customers...

Me.lstBox.Recordsource=strSQL

this will first display all records.

Then if you have a combo box...you can do this in the after update event

If IsNull(Me.cboMyCBO) then
'do nothing
else
strSQL = "SELECT * FROM Customers WHERE CustomerID= " & Me.cboMyCBO
Me.lstBox.Recordsource=strSQL
Me.lstBox.Requery
end if

This will handle the combo box...

Now for the text field..if your looking for character by character you need a like query

strSQL = "SELECT * FROM Customers WHERE CustomerName LIKE '" & Me.txtField.Value & "*'"
Me.lstBox.RecordSource=strSQL
Me.lstBox.Requery

Jon