Easiest way to search access database?

[hipopony66]

Hey, I need some help with this. Here's what I'm trying to do. I have a table in an access database called "Poolpass". This table has the following columns: "Lastname", "FirstName", etc.

This what I want to do:

User selects what they want to search by in a combobox on my form (ie. Lastname, Pass #) Then in a textbox they enter the corresponding information (ie. If they chose to search by Lastname then they would type "Johnson" In the textbox.) When the user clicks the search button, I want to search the column of my table that corresponds to the "Search by" category they selected. When a match is found I want to add all the information from that record to a row in my Listview. Basically, this is a project for me to practice database stuff on. I'm doing this for fun and to help us out at work. What would be the best method to do this by? This is a really basic program so I don't need anything fancy. Here's a look at what the interface looks like below.

[Muddy]

Something like the following (I didint test but should give you the idea):

dim rst as new ado.recordset
 
strSQL = "SELECT * FROM PoolPass WHERE " & strSearchField & "=" & strSearchString
 
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\test.mdb;jet oledb:database"
 
rst.Open strSQL ,cnn, adOpenStatic, adLockOptimistic
rst.Update
' record is now stored in rst
rst.Close
set rst = nothing

[pnish]

Or try something like this:

Dim sql as string
sql = "SELECT * FROM [PoolPass] WHERE "
 
Select Case Combo1.Text
    Case "Lastname"
        sql = sql & "[Lastname] = '" & Text1.Text & "'"
    Case "Firstname"
        sql = sql & "[Firstname] = '" & Text1.Text & "'"
End Select
'
' Now create a recordset using the sql string
'
Set rs = db.OpenRecordset(sql, dbOpenDynaSet)

Hope this helps

[hipopony66]

Ok, I'm a little confused now. Do I not need to connect to the database when my application launches? Also, is SQL the easiest way? Will the search find multiple matches if more then one person has a lastname of say "Johnson"? The code previously posted makes a little sense, but I haven't worked with DB in a long time. Basically, once I have the recordset stored, how can I load each match to the listview? I know how to add items to a listview, but not from a recordset? Can anyone give me any clearer of any example? Any help is extremely appreciated. Thanks

-Jeremy

[hipopony66]

That code you posted makes perfect sense to me except where you say to create the recordset using the SQL string. How do I do that?

[Muddy]

Originally posted by hipopony66
That code you posted makes perfect sense to me except where you say to create the recordset using the SQL string. How do I do that?

cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\test.mdb;jet oledb:database"
 
rst.Open sql ,cnn, adOpenStatic, adLockOptimistic
rst.Update

[hipopony66]

Private Sub cmdSearch_Click()
 
Dim sql As String
 
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\poolpass.mdb;jet oledb:database"
 
sql = "SELECT * FROM [PoolPass] WHERE "
 
Select Case cboSearchBy.Text
    Case "Last Name"
        sql = sql & "[Lastname] = '" & txtSearchString.Text & "'"
    Case "First Name"
        sql = sql & "[Firstname] = '" & txtSearchString.Text & "'"
    Case "Address"
        sql = sql & "[Address] = '" & txtSearchString.Text & "'"
    Case "City"
        sql = sql & "[City] = '" & txtSearchString.Text & "'"
    Case "Zip"
        sql = sql & "[Zip] = val(txtSearchString.Text)"
    Case "Phone No."
        sql = sql & "[Phone] = '" & txtSearchString.Text & "'"
    Case "Pass No."
        sql = sql & "[Pass No.] = val(txtSearchString.Text)"
End Select
 
'Now I need to create recordsets for the matches I got I'm assuming?
 
'And now I need to add them to the listview

[pnish]

Private Sub cmdSearch_Click()
 
Dim sql As String
Dim rst As ADODB.Recordset
 
cnn = "Provider=Microsoft.Jet.OLEDB.4.0; _
Persist Security Info=False;Data Source=c:\poolpass.mdb; _
jet oledb:database"
 
sql = "SELECT * FROM [PoolPass] WHERE "
 
Select Case cboSearchBy.Text
    Case "Last Name"
        sql = sql & "[Lastname] = '" & txtSearchString.Text & "'"
    Case "First Name"
        sql = sql & "[Firstname] = '" & txtSearchString.Text & "'"
    Case "Address"
        sql = sql & "[Address] = '" & txtSearchString.Text & "'"
    Case "City"
        sql = sql & "[City] = '" & txtSearchString.Text & "'"
    Case "Zip"
        sql = sql & "[Zip] = " & val(txtSearchString.Text)
    Case "Phone No."
        sql = sql & "[Phone] = '" & txtSearchString.Text & "'"
    Case "Pass No."
        sql = sql & "[Pass No.] = " & val(txtSearchString.Text)
End Select
'
'    Create the recordset
'
Set rst = New ADODB.RecordSet
rst.Open sql ,cnn, adOpenStatic, adLockOptimistic
'
'    Loop through each record and do whatever
'
Do While Not rst.Eof
    Debug.Print rst![LastName], rst![FirstName]
Loop
'
'    Close the recordset & release its resources
'
rst.Close
Set rst = Nothing
 
End Sub

This code will return multiple records if there's more than one match. I haven't used Listviews much, so I'll leave that bit to you.

I've changed the lines above where you used Val(), they were incorrect before.