Search Form Example (1 Viewer)

Status
Not open for further replies.

gromit

Registered User.
Local time
Today, 04:01
Joined
Nov 17, 2005
Messages
260
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
 

Attachments

  • dbSearchExample.zip
    23.8 KB · Views: 38,729

b_bill

New member
Local time
Today, 04:01
Joined
Jan 4, 2006
Messages
7
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

Registered User.
Local time
Today, 11:01
Joined
Dec 23, 2005
Messages
18
what version of access is this please
 

planbprojekt

Registered User.
Local time
Today, 16:31
Joined
Mar 3, 2006
Messages
14
Awesome

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

gromit

Registered User.
Local time
Today, 04:01
Joined
Nov 17, 2005
Messages
260
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

Registered User.
Local time
Today, 07:01
Joined
Apr 11, 2006
Messages
13
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

Registered User.
Local time
Today, 07:01
Joined
Apr 11, 2006
Messages
13
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:
 

Mickster

Registered User.
Local time
Today, 04:01
Joined
Feb 10, 2005
Messages
38
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
 
I

inferno

Guest
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

Ben
Local time
Today, 11:01
Joined
Apr 21, 2006
Messages
59
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

Registered User.
Local time
Today, 06:01
Joined
Sep 30, 2004
Messages
38
it works like a charm.

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

thanks
 

jonwentz

Registered User.
Local time
Today, 06:01
Joined
Sep 30, 2004
Messages
38
it works like a charm.

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

thanks
 
D

deb_d

Guest
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

Always confused.
Local time
Today, 04:01
Joined
Jan 22, 2005
Messages
55
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

New member
Local time
Today, 04:01
Joined
Sep 29, 2006
Messages
9
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

Registered User.
Local time
Today, 04:01
Joined
Jul 5, 2006
Messages
15
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.
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom