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
[email protected]



[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.