A database with a search form that allows the user to enter multiple criteria and then retrieve the matching records using a "Search" button.

Open the form frmSearch and enter criteria in the appropriate fields then hit search (enter just a few or you may get no records).

A variety of search rules (text, numeric, look up) are included for illustration purposes.

Search rules:
a) Lastname, Firstname - Search code used the LIKE keyword and automatically appends a trailing * for wildcards
b) Min age, max age - Search is strictly greater than and less than the values
c) CompanyID, CountryID - Queries for the combo boxes include a UNION so that "<all>" is included as an option. This UNION statement requires the tblzNull table.
d) Color - Multiselect list box uses OR to search for any of the chosen colors. The listbox uses a query on the Clients table to determine the list of colors presented.

- gromit

Hi Gromit,

I like your sample search form and will use something like it in my own project. As you gave permission to adapt this form for personal use, I made the small change of adding the btnSearch_Click event at the very end of the btnClear procedure to repopulate the sub form, as this made sense to me. Let me know if you had some good reason for disincluding this.

Nice work!

Bill

what version of access is this please

hey ,
Nice work man ....but can i have the results to show up on a report ? how can i do that ...?

Sorry for the delay -

1. Adding btnSearch_Click to the end of the btnClear procedure is certainly a reasonable step. There was no particular reason why I did not include this.

2. The version is 2002 (10.0)

3. To send the results to a report, call the report and use the same WHERE filter. E.g.

stDocName = "rptMyReportName"
DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter

- g

im having some trouble sending the results to a report to print in mine, can you add that to the example then repost it? thanks

Still Stuck on this, I entered that string when pulling the report and am getting an error: Sytax error (missing operator) in expression query.... can anyone else help with this? :confused:

A database with a search form that allows the user to enter multiple criteria and then retrieve the matching records using a "Search" button.

Open the form frmSearch and enter criteria in the appropriate fields then hit search (enter just a few or you may get no records).

A variety of search rules (text, numeric, look up) are included for illustration purposes.

Search rules:
a) Lastname, Firstname - Search code used the LIKE keyword and automatically appends a trailing * for wildcards
b) Min age, max age - Search is strictly greater than and less than the values
c) CompanyID, CountryID - Queries for the combo boxes include a UNION so that "<all>" is included as an option. This UNION statement requires the tblzNull table.
d) Color - Multiselect list box uses OR to search for any of the chosen colors. The listbox uses a query on the Clients table to determine the list of colors presented.

- gromit

Hi,

This example could be very useful to me. But could you possibly re-post this example using Access 97, as that is the version that I am currently running.

Thanks

Gromit that’s great I was just the thing I was looking for but.. :-)

Is it possible to that when the user completes there search ands there is more then one person with the same name, that you could select between them and then open up their details in another form? I f anyone can help me with this that would be great I am stumped. cheers

Gromit that’s great I was just the thing I was looking for but.. :-)

Is it possible to that when the user completes there search ands there is more then one person with the same name, that you could select between them and then open up their details in another form? I f anyone can help me with this that would be great I am stumped. cheers

If you change the form so that it shows continuous forms instead of datasheet view you can use the following code on Form Dtl Dbl Click event:

Private Sub Detail_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2"

stLinkCriteria = "[ParentID]=" & Me![parentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I am designing a similar search form, but want to lock it down as much as possible so a subform with locked controls and the above code has worked quite well.

it works like a charm.

but does anyone know how to get it to work in an ADP.

thanks

it works like a charm.

but does anyone know how to get it to work in an ADP.

thanks

Hello,

I am trying to adapt this search form for my own use, but am having trouble with the vb in the btn_search_Click. I am getting "Syntax error in FROM clause".
The error is from:

Me.Loc_Search_Sub.Form.RecordSource = "Select * FROM Loc_Search_q " & BuildFilter

I am pretty new to this, so if anyone could help me out I would appreciate it.

Thanks,
deb

also... sorry, lots of little questions :) is there a way to create a field that tells you how many files you are currently viewing? So it tells me I have x amount of files before my search, and now y amount of files match the criteria.

also, is there a way to hide that file browser bar at the bottom of the search results field? (the bar that allows you to click through the database entires). thanks =D

Hi Gromit or anyone else that can help.

I have tried creating a report using the code you said but i am also having the same error message as sumone else.

error: Sytax error (missing operator) in expression query

Why is this?

Thanks in advance

I had the same problem with printing so I threw the BuildFilter Function into a Msgbox and found the problem to be the WHERE in the statement. Print filters apparently don't like this so I tweaked the code a bit and relocated the WHERE in the filter.

Here's the old code in the Function:

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere


New Code:

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If

End If

BuildFilter = varWhere


Then I modified the code behind the search button because it needs the WHERE in the statement:


' Update the record source
If BuildFilter = "" Then
Me.e_sub_UserQry.Form.RecordSource = "SELECT * FROM e_qryUserQuery_1 " & BuildFilter
Else
Me.e_sub_UserQry.Form.RecordSource = "SELECT * FROM e_qryUserQuery_1 WHERE " & BuildFilter
End If


Works like a charm.
I hope this helps someone else.