Search in mySQL database

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.

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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.

How are you opening the recordset?

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

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

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.