Search Form Example

[gromit]

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]

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]

what version of access is this please

 

[planbprojekt]

Awesome

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

 

[gromit]

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]

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]

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]

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]

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]

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.

 

[jonwentz]

it works like a charm.

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

thanks

 

[jonwentz]

it works like a charm.

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

thanks

 

[deb_d]

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]

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]

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]

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:

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