Results 1 to 9 of 9

Thread: Database help

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 1999
    Posts
    309

    Post

    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 ??

  2. #2
    Addicted Member
    Join Date
    Jan 1999
    Posts
    165

    Post

    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.

  3. #3
    Lively Member Ishamel's Avatar
    Join Date
    Nov 1999
    Location
    Edinburgh, Scotland
    Posts
    112

    Post

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

    Code:
    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).]

  4. #4
    Addicted Member
    Join Date
    Jul 1999
    Location
    Portland, OR.
    Posts
    226

    Post

    Hi
    How about:

    Select * From myTable where myField LIKE Text1.text


  5. #5
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308

    Post

    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

  6. #6
    Junior Member
    Join Date
    Jan 1999
    Location
    Q.C., Phils.
    Posts
    27

    Post

    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

  7. #7
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308

    Post

    Yes, Eclipse DevSoft is right.

  8. #8
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

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

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

    Code:
        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

  9. #9
    Guest

    Talking Thanks

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width