Search in mySQL database

[Fraps]

Hi there,

currently I have a problem to develop a search module ( "more advanced" ) into the project which uses mySQL databases. I've made only one criteria and cannot move forward with this.

At the moment I can search only by the number of a record ( Person No. )

Here is the search click function.

Private Sub cmdSearch_Click()
On Error GoTo esearch
Person.Search txtPersonNo

If rs.EOF Then
    MsgBox "No record found.", vbInformation
End If
Exit Sub

esearch:
MsgBox Err.Description, vbCritical
End Sub

This is my public function to save the data:

Public Function Save(LastName As String, FirstName As String, MiddleName As String, Address As String)
On Error GoTo iError
If frmPerson.Insrt = True Then
    cn.Execute "INSERT INTO Person(LastName, FirstName, MiddleName, Address) VALUES('" & LastName & "', '" & FirstName & "', '" & MiddleName & "', '" & Address & "')"
    MsgBox "Record sucessfully added.", vbInformation
Else
    cn.Execute "UPDATE Person SET LastName='" & LastName & "', FirstName='" & FirstName & "', MiddleName='" & MiddleName & "', Address='" & Address & "' WHERE PersonNo=" & rs(0)
    MsgBox "Record sucessfully updated.", vbInformation
End If

Unload frmDataEntry
rs.Requery
getRecord
Exit Function

iError:
MsgBox Err.Description, vbCritical
End Function

And here is the search function:

Public Function Search(PersonNo As Integer)
rs.Requery
rs.Find "PersonNo=" & PersonNo
getRecord
End Function

The public function to get the data looks as follow:

Public Function getRecord()
With frmPerson
    On Error GoTo egetrecord
    .lblPersonNo = "Person No.: " & rs(0)
    .lblLastName = "Last Name: " & rs(1)
    .lblFirstName = "First Name: " & rs(2)
    .lblMiddleName = "Middle Name: " & rs(3)
    .lblAddress = "Address: " & rs(4)
    Exit Function

egetrecord:
    .lblPersonNo = "Person No.: "
    .lblLastName = "Last Name: "
    .lblFirstName = "First Name: "
    .lblMiddleName = "Middle Name: "
    .lblAddress = "Address: "
End With
End Function

Any idea how to add last name, first name, address?
Thanks for any ideas.

[abhijit]

How are you opening the recordset?

Regarding the ADO rs.Find method, you might want to read this.

[Fraps]

Thanks abhijit. It's not so simple as I thought.

Does anybody know some pleaces where I could download some sources
( simple address books or something ) with MySQL?

Add, remove, edit, search

Thanks

[si_the_geek]

MySQL is virtually irrelevant - all that changes is the Connection String, and some minor SQL syntax.

What matters is ADODB code, and you can find several examples in our FAQs - such as the "ADO Tutorial" (parts of which are similar to what you have already), and "...Further Steps" which extends it in various ways - including a search feature.