Search Function

[IRIman]

Hi

I'm making a database for a library and I get to the phase that I should build a search tool for it. but I'm new to access, so I search the web and find this forum and the example "search multiple fields.zip".
That's a good search form to me but I need a more flexible one:

I want to change the form "frmAdvancedSearch" so that in the first step user can select an item(a field like "Mailling Address") from a combo box (to search only in that subject) and then type the search string into the search field and finally by pressing a button he see the result in the list box.

Can anybody help me?

Thanks a lot !

 

[jal]

See attached. I added a combobox to display the field names, plus the following code:

Private strSaveOriginalSQL As String

Private Sub Form_Open(Cancel As Integer)
strSaveOriginalSQL = Me.SearchList.RowSource
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblSales")
Dim f As DAO.Field
Me.cboFieldNames.RowSourceType = "Value List"
Me.cboFieldNames.AddItem "Search All Fields", 0
Dim i As Integer
i = 1
For Each f In rs.Fields
cboFieldNames.AddItem f.Name, i
i = i + 1
Next
rs.Close
Me.cboFieldNames.LimitToList = True
Me.cboFieldNames = "Search All Fields"
End Sub


And I rewrote the following sub


Private Sub txtSearch_Change()
If Me.cboFieldNames = "Search All Fields" Then
Me.txtSearch2.Value = Me.txtSearch.Text
Me.SearchList.RowSource = strSaveOriginalSQL
Me.SearchList.Requery
Else
Me.SearchList.RowSource = "SELECT * FROM tblSales WHERE " & cboFieldNames & " LIKE '*" & Me.txtSearch.Text & "*'"
Me.SearchList.Requery
End If
End Sub

Everything else I left intact.

 

[IRIman]

Thank you very much, jal !

But there's a tiny problem and I hope you help me.
When combo15 changes to anything except "search all fields" when I type in txtsearch box, in the list box "installation address" chanes into "InstallAddress" and "InstallTown" which are the original fields in tblsales.

Thanks again for your help.

 

[jal]

Thank you very much, jal !

But there's a tiny problem and I hope you help me.
When combo15 changes to anything except "search all fields" when I type in txtsearch box, in the list box "installation address" chanes into "InstallAddress" and "InstallTown" which are the original fields in tblsales.

Thanks again for your help.

When "search all fields" I tell the code to use the original SQL (saved in the listbox's properties window)

strSaveOriginalSQL = Me.SearchList.RowSource

and that SQL is:

SELECT tblSales.CustID, tblSales.ClientName AS [Client Name], [InstallAddress] & ", " & [InstallTown] AS [Installation Address], [MailingAddress] & ", " & [MailingTown] AS [Mailing Address], tblSales.Phone FROM tblSales WHERE (((tblSales.ClientName) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) Or ((([InstallAddress] & ", " & [InstallTown]) Like "*" & Forms!frmAdvancedSearch!txtSearch2 & "*")) Or ((([MailingAddress] & ", "

I didn't write the above code. Instead I wrote the following simpler version (it's only good for searching one field)'

"SELECT * FROM tblSales WHERE " & cboFieldNames & " LIKE '*" & Me.txtSearch.Text & "*'"

The differenence is that the original author is concatenating two fields as to form one field called InstallationAddress. Basically he is writing this:

SELECT InstallAddress + InstallTown as [Installation Address]

You can rewrite my SQL into a longer format as to do the same if you like so that my SQL will be more similar to the original. (The main thing is to leave my WHERE clause intact because that's what causes the search to succeed).