search engine form

[seashore67]

I am attempting to create a search engine that will search 4 different tables that are related to each other. I was curious if it was a possibility to create one form that will search all fields for what the user inserts and displays all the info related to that search.

Thanks
Andrew

 

[cghoga]

Yes.

Create a form and include entry fields for each field you want to search by. Do not bind these fields to any recordsource. If you use a combobox or listbox you can set the row source, but do not bind the fields.

In a query join all your tables based on their relationships.
Then, point the criteria for each field you want to search on to the correcponding field on your search form.

For example, if you named your seacrh form frmSearch and one of the fields you want to search by is named ItemNumber on the form.

Then, in the criteria under ItemNumber, it would look like this:

[Forms]![frmSearch]![ItemNumber].value

If you wanted to take it a step further, and allow null search fields on the search form you need the "like" along with the "*" wildcard.

For example:

Like iif([Forms]![frmSearch]![ItemNumber].value is null,"*",[Forms]![frmSearch]![ItemNumber].value)

The * will cause the query to ignore the parameter criteria of that field and treat it as if there were no parameter criteria on that field.

Save the query.

Now, on your search form, you need a button (or other method) to execute the query and get your search results. You can a form or report, and set its recordsource to the query, or you can simply open the query itself.

The command to open a form is:

DoCmd.OpenForm "YourForm"

a report:

DoCmd.OpenReport "YourReport"

a query:

DoCmd.OpenQuery "YourQuery"

Hope this helps you.

 

[seashore67]

Thanks, this worked great but...

I attempted your if statement and it gave me an error: "undefined 'if' in expression".

Not sure what this means although I was curious if there needs to be "then" in the statement.

Thanks for your help!

Andrew

 

[Gazonice]

Hi,

If / then, is slightly different to iif (immediate if) which usually has a true, false output.

eg

A=1

iif(A=1, "True" , "False&quot

the result would be the word "true"

This is usually used in expressions and most often in unbound controls.

Garry

 

[seashore67]

Thanks Garry I thought the double i was a typo...now it works.

Have another question though...I have several entries with the same text as a field and when I do the search, only one of those entries appears. I would luv it if all the entries that has the text in that field appear.

Thanks again,
Andrew

 

[Gazonice]

I think you may need something a little more complex than the explanation given above if you want a more 'search engine like' facility.

Below is the code of a searcher that I use to search an address book (tbl_clients), with excellent results.

The main Form has a text field (Text11) to collect the search criteria, and the continuous subform (subfrm_client) displays the result - (Initially set to 'Hidden') with fields that match those of the source table.

My example only looks through 3 items in one table but you can easily see how you can extend this to cover more fields in more tables...

(The original code is not my work)

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strWhere = "WHERE"
strOrder = "ORDER BY tbl_clients.client_name;"

strSQL = "SELECT tbl_clients.* " & _
"FROM tbl_clients"

If Not IsNull(Me.Text11) Then
strWhere = strWhere & " (tbl_clients.client_name) Like '*" & Me.Text11 & "*' OR"
strWhere = strWhere & " (tbl_clients.postcode) Like '*" & Me.Text11 & "*' OR"
strWhere = strWhere & " (tbl_clients.alternative_name) Like '*" & Me.Text11 & "*' "

Me.subfrm_client.Form.RecordSource = strSQL & " " & strWhere & "" & strOrder

With Me.subfrm_client.Form
.Visible = (.RecordsetClone.RecordCount > 0)
End With

Me.Form.Caption = "Address Book - showing search results for '" & Me.[Text11] & "'"

Else
MsgBox "Please enter a name to search by. ", vbOKOnly + vbInformation, "Missing Search Data"
End If


Hope this helps,

Garry

 

[seashore67]

Thanks garry ill try it and let u know!

Andrew