Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 12-26-2005, 01:09 PM   #1
gromit
Registered User
 
Join Date: Nov 2005
Posts: 260
Thanks: 0
Thanked 8 Times in 3 Posts
gromit is on a distinguished road
Search Form Example

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
Attached Files
File Type: zip dbSearchExample.zip (23.8 KB, 32183 views)

gromit is offline  
The Following 6 Users Say Thank You to gromit For This Useful Post:
atg (07-26-2012), Gerrit.B (01-23-2013), jadehawk (02-13-2013), Lennon (03-05-2012), tjnichols (04-30-2012), WaiveIt (01-24-2014)
Old 01-05-2006, 08:02 AM   #2
b_bill
Registered User
 
Join Date: Jan 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
b_bill is on a distinguished road
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
b_bill is offline  
Old 02-06-2006, 06:02 AM   #3
bl0u2011
Registered User
 
Join Date: Dec 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
bl0u2011 is on a distinguished road
what version of access is this please

bl0u2011 is offline  
Old 03-13-2006, 08:17 AM   #4
planbprojekt
Registered User
 
Join Date: Mar 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
planbprojekt is on a distinguished road
Awesome

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
alvito.8k.com
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
planbprojekt is offline  
Old 04-08-2006, 04:56 AM   #5
gromit
Registered User
 
Join Date: Nov 2005
Posts: 260
Thanks: 0
Thanked 8 Times in 3 Posts
gromit is on a distinguished road
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
gromit is offline  
Old 04-12-2006, 01:01 PM   #6
tacoboy42
Registered User
 
Join Date: Apr 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
tacoboy42 is on a distinguished road
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 is offline  
Old 04-13-2006, 07:56 AM   #7
tacoboy42
Registered User
 
Join Date: Apr 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
tacoboy42 is on a distinguished road
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?

tacoboy42 is offline  
Old 05-10-2006, 08:27 AM   #8
Mickster
Registered User
 
Join Date: Feb 2005
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Mickster is an unknown quantity at this point
Quote:
Originally Posted by 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
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
Mickster is offline  
Old 05-11-2006, 02:00 AM   #9
inferno
Guest
 
Posts: n/a
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
 
Old 05-11-2006, 09:47 AM   #10
epicmove
Ben
 
Join Date: Apr 2006
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
epicmove is on a distinguished road
Quote:
Originally Posted by 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
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.
epicmove is offline  
Old 05-16-2006, 05:16 AM   #11
jonwentz
Registered User
 
Join Date: Sep 2004
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
jonwentz is an unknown quantity at this point
it works like a charm.

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

thanks
jonwentz is offline  
Old 05-16-2006, 05:17 AM   #12
jonwentz
Registered User
 
Join Date: Sep 2004
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
jonwentz is an unknown quantity at this point
it works like a charm.

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

thanks
jonwentz is offline  
Old 05-22-2006, 08:13 AM   #13
deb_d
Guest
 
Posts: n/a
Question 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
 
Old 06-11-2006, 04:21 PM   #14
woodman650
Always confused.
 
Join Date: Jan 2005
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
woodman650 is on a distinguished road
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
woodman650 is offline  
Old 11-02-2006, 04:40 AM   #15
andywhittle
Registered User
 
Join Date: Sep 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
andywhittle is on a distinguished road
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

andywhittle is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Record Search Form by options axsnub Forms 2 05-17-2006 02:56 AM
Search Button in Form trish Forms 0 07-27-2005 07:45 AM
Problems mixing numeric fields and text fields in Query by Form search method Sharky II Forms 4 01-17-2004 02:50 PM
Having a 'professional' search form Sharky II Forms 32 08-29-2003 03:40 PM




All times are GMT -8. The time now is 08:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World