Creating a search form in Access

[ethomas]

Hello there.

I have a question (actually two) that I would like to know about something I would like to do using MS Access.

Here's the thing: I built a MySQL database for our client database and we use MS Access 2002 as a graphical frontend. Now, I have created forms to access the data, add records and all that... but what I would really like is to have a search from for the database so that we can enter some data into a field and all records that match that data are retrieved and displayed (preferably in a form view as well).

My question(s) is/are 1. Can this be done in the way that we're using Access (as a graphical frontend to a MySQL database)? and 2. How do I get started?

Mind you, I have very little knowledge in VB scripting so please, I beg you... be gentle with me.

Any and all suggestions are greatly appeciated. Thanks!

 

[TimKThailand]

You can create a passthru query to help. The trick is you have to modify the SQL on-fly based on a user's input.

Dim dbs As Object, qdf As Object
Dim strSQL As String

If Me.txtName <> &quot;&quot; Or Not IsNull(Me.txtName) Then
strSQL = &quot; Where CustName Like '%&quot; & Me.txtName & &quot;%' &quot;
End If

If Me.txtLastName <> &quot;&quot; Or Not IsNull(Me.txtLastName) Then
If strSQL <> &quot;&quot; Then
strSQL = strSQL & &quot; And LastName Like '%&quot; & Me.txtLastName & &quot;%' &quot;
Else
strSQL = &quot; Where LastName Like '%&quot; & Me.txtLastName & &quot;%' &quot;
End If
Endd If
...

strSQL = &quot;Select * From Table1&quot; & strSQL
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(&quot;YourPassThruQueryName&quot
qdf.SQL = strSQL
qdf.Close
dbs.Close
Set qdf = Nothing
Set dbs = Nothing
' Now you can open a new form based on the passthru
' to show the result.
DoCmd.OpenForm &quot;frmShow&quot;,...

Hope this helps you get started.