How to retrieve data from access table according to listbox contains

[anampathik]

How to retrieve data from access table according to listbox contains

I have a Data Base Project in Visual Basic 6.0
I have a access table named Table1 and a ListBox named List1 and a ComboBox named Combo1.
There are multiple List in the ListBox and ComboBox both.
Now I pass two list item from another code [Code] to ListBox now there are "Mr" and "Dr" in ListBox and I select "Title" in the ComboBox
Now How how can I retrieve data from Table 1 (Title Field)
The following code did not work.
Sub List()
'Database connection already Defined
For LI = 0 To List1.ListCount - 1
RecordSet.Open ("select * from Table1 where " & Combo1.Text & _
" like '" & List1.List(LI) & "%'"), Connection, adOpenDynamic, adLockOptimistic
Next LI
End Sub

Further Clarification

ListBox Contains
Mr
Dr

Data in Table1
Mr. SKB
Dr. GRB
Mr. LNK
Ms. RPK

Result (anywhere like flexgrid, textbox etc)
Dr. GRB
Mr. SKB
Mr. LNK

[GremlinSA]

#1 the column that this data is in is it called 'Title' ????

#2 In your code snip (BTW please use CODE tags) i dont see you doing anything with the data from the Recordset.. (putting it into a flexgrid etc...)

[dglienna]

Why would you want to open the table connection once for each record in the table? If everyone did that, it'd kill performance. Find ALL records, then FILTER the data in the query

(Unless you do have a million records, in which case you'd load 50 filtered records at a time(

[DataMiser]

Definitely do not open a new recordset for every record in a loop. Also you must close a recordset before you can open it again.

You can filter or you can just load all the records into a recordset and loop through them for matches. With your small data set this would work fine but would not be a good idea with a large data set.

[WoF]

If we assume your combobox contain the field names and the listbox the possible choices, you would make up the query string like this:
Further assume a Recordset named Rec and an open connection Con.

  dim sql as string
  sql = "SELECT * FROM Table1 WHERE (" & Combo1.Text & " LIKE '" & List1.List(List1.Listindex) & "')"
  Rec.Open sql, Con, adOpenDynamic, adLockOptimistic
  Set FlexGrid.DataSource = Rec

You first build a proper SQL string to query the wanted data, then you open the recordset with this string which will already load all the records which match the query. No loop is needed.
The last lign assigns the recordset as DatSource for an assumed FlexGrid to display the found records.

Please note how you use the selected item from the listbox.

[anampathik]

dim sql as string
sql = "SELECT * FROM Table1 WHERE (" & Combo1.Text & " LIKE '" & List1.List(List1.Listindex) & "')"
Rec.Open sql, Con, adOpenDynamic, adLockOptimistic
Set FlexGrid.DataSource = Rec

This code returns only one search criteria (one at once Either "Mr" or "Dr")
When I select "Mr" in Listbox it returned
Mr LNK
Mr SKB
And when I select "Dr" in Listbox it returned
Dr GRB
But when I select both lists it returned only one item, i select at last.
How to retrieve the data according to selected lists ("Mr" and "Dr")?
Which can show
Dr GRB
Mr LnK
Mr SKB

[anampathik]

"List" does not means Listbox but contains "row" of it. I have only one list box "List1"

[dglienna]

Need some LOGIC to test which is which. Then, one statement can be

& " LIKE ('" & List1.List(List1.Listindex) & OR List1.List(List1.Listindex +1)"')"

Also, a string can contain Dr, MD, or even Dr OR MD