Search Form Example

gromit · Dec 26, 2005

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

b_bill · Jan 5, 2006

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

bl0u2011 · Feb 6, 2006

what version of access is this please

planbprojekt · Mar 13, 2006

Awesome

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

gromit · Apr 8, 2006

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

tacoboy42 · Apr 12, 2006

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

tacoboy42 · Apr 13, 2006

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?

Mickster · May 10, 2006

gromit said:
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

inferno · May 11, 2006

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

epicmove · May 11, 2006

inferno said:
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:

Code:

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.

jonwentz · May 16, 2006

it works like a charm.

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

thanks

jonwentz · May 16, 2006

it works like a charm.

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

thanks

deb_d · May 22, 2006

Help w/ VB in this form

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

woodman650 · Jun 12, 2006

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

andywhittle · Nov 2, 2006

error on report

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

heather001 · Dec 28, 2006

Print Problem Resolved

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:

Code:

    ' 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:

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:

Code:

' 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.

Search Form Example

gromit

Registered User.

Attachments

b_bill

New member

bl0u2011

Registered User.

planbprojekt

Registered User.

gromit

Registered User.

tacoboy42

Registered User.

tacoboy42

Registered User.

Mickster

Registered User.

inferno

Guest

epicmove

Ben

jonwentz

Registered User.

jonwentz

Registered User.

deb_d

Guest

woodman650

Always confused.

andywhittle

New member

heather001

Registered User.

Similar threads

Users who are viewing this thread