Database help

[Inhumanoid]

I've got an access database named Menu.mdb.
The database contains records with:
ID, Name, Type

I need to make a form with a textbox. When I type a string in a textbox and hit a button it must search the datbase to see if there is a Name containing my string. It does not have to be a match, it must just contain it.If it finds any it should be listed as an item in a listview (wich is also on this form)...

How do I do this ??

[J Staniforth]

The simplest way you could do this would be to use the Access find button when the cursor is on the "Name" field - ensuring the the options Search Only Current Field and Match Any Part of Field are used.

[Ishamel]

Assuming you have already established a connection to the database, you could try this.


Private Sub Command1_Click()
Dim rsRecordSet As RecordSet
Dim objList As ListItem

Set rsRecordSet = myDatabase.OpenRecordSet("Select myField From myTable")

While Not rsRecordSet.EOF
If InStr(UCase(rsRecordSet![myField]), UCase(Text1.Text)) Then
Set objList = ListView1.ListItems.Add(, , rsRecordSet![myField])
End If

rsRecordSet.MoveNext
Wend
End Sub


The help files should also have some examples.

------------------
Ishamel
KBurt59082@AOL.COM



[This message has been edited by Ishamel (edited 01-10-2000).]

[Lyla]

Hi
How about:

Select * From myTable where myField LIKE Text1.text

[LG]

No, Lyla, it won't work. It will return record if you put * at the end of a string. But if it's the second word in a string, you still won't get any records back.

Larisa

[Eclipse DevSoft]

your SQL statement should look like this:

sSQL = "SELECT * FROM myTable where myField LIKE '*" & Text1.Text & "*'"

------------------
Share your knowledge, it is the best way to achieve immortality

[LG]

Yes, Eclipse DevSoft is right.

[Clunietp]

I have compiled all answers thus far into usable code for inhumanoid:

this uses DAO. If you need ADO, let us know:


Dim db As Database
Dim rs As Recordset


'open database
Set db = DBEngine.OpenDatabase("NWind2k.mdb")


'open recordset using SQL LIKE
Set rs = db.OpenRecordset("Select * from Customers where CustomerID like '*" & text1.text & "*'")


'add all results to listbox
Do Until rs.EOF = True
List1.AddItem rs.Fields("CustomerID").Value
rs.MoveNext
Loop


'close db/rs
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


Just modify the database/table/field names to your liking.

Tom

Thanks that was exactly what I was looking for Clunietp

Question It would not work for me until I added tbl in from of the table and fld in front of the field. My table is tblCustomers and the Feild I am searching is fldCompanyName
I left it just Customers in the SELECT and it didnt find the table until I added the tbl in from of it, same thing with the CompanyName field wouldnt work till I added the fld prefix. Why is is I see alot of examples where people dont use the prefiix's and I have to I am Using an Access data base. Is it just who ever created the data base did not need/want to have a tbl prefix. in other words are prefixe's for tables ect not required for access databases?

Ishamel I had done something similar to your example before as a test that I was opening the record set, but that only includes one field in the recordset and I needed to have all fields in the recordset for further use.

Thanks again.