Search Button Based from Query (1 Viewer)

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
Hello,

I am in the process of creating a simple database that has a table of data, a query that filters, and a form that searches through the records based on the filters in the query. What I have so far is three combo boxes in the form that I would like to be able to search from. However, the results of data only show up when I make a selection from only one combo box. I would like to be able to search using all three combo boxes that filter based off of each other. Basically - if I select a location in the first combo box, a city in the second combo box, and a category in the third - I want the results to apply all three selections instead of only using the first one to display results.

Here is the SQL of the query:

SELECT DISTINCT tMediaContacts.[City ], tMediaContacts.[Publication Location], tMediaContacts.[Category type], *
FROM tMediaContacts
WHERE (((tMediaContacts.[City ])=[Forms]![fSearch]![cmbCity]) AND ((tMediaContacts.[Publication Location])=[Forms]![fSearch]![cmbLocation])) OR (((tMediaContacts.[Publication Location])=[Forms]![fSearch]![cmbLocation]) AND ((IsNull([Forms]![fSearch]![cmbCity]))<>False)) OR (((tMediaContacts.[City ])=[Forms]![fSearch]![cmbCity]) AND ((IsNull([Forms]![fSearch]![cmbLocation]))<>False)) OR (((tMediaContacts.[City ])=[Forms]![fSearch]![cmbCity]) AND ((tMediaContacts.[Category type])=[Forms]![fSearch]![cmbCategory]) AND ((IsNull([Forms]![fSearch]![cmbLocation]))<>False) AND ((IsNull([Forms]![fSearch]![cmbCategory]))<>False)) OR (((tMediaContacts.[Category type])=[Forms]![fSearch]![cmbCategory]) AND ((IsNull([Forms]![fSearch]![cmbCity]))<>False) AND ((IsNull([Forms]![fSearch]![cmbLocation]))<>False)) OR (((IsNull([Forms]![fSearch]![cmbCity]))<>False) AND ((IsNull([Forms]![fSearch]![cmbLocation]))<>False) AND ((IsNull([Forms]![fSearch]![cmbCategory]))<>False));

Please let me know if you have any suggestions/help, I appreciate it very much.
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Hmm, I wouldn't do this from a query. I would use...
http://allenbrowne.com/ser-62.html

Gives more control over what Users can search on. You can adjust to include a line if you want Users to make selection from all three before viewing data.
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
GinaWhipp - thanks for the quick response. I understand little to no code (new to Access) - how would I go about applying that code to my search form? Should I even bother with trying with a query like at the bottom of that link?
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Well, this is up to you. I can walk thru the code with you or you can walk thru the query at the bottom replacing the names with your field and Form names. Feeling adventerous? :D
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
Well, this is up to you. I can walk thru the code with you or you can walk thru the query at the bottom replacing the names with your field and Form names. Feeling adventerous? :D

Well sure! I trust you that the code is probably the better way, it's just new to me. What all do you need to know?
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
I think I can gleam most of what I need from the query you posted. The only thing that is not entirely clear... are you looking for the Form to look like the one shown in the link or is you Form set up a different way? Is there a Report involved?
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
I can set up the form that way if possible - right now it's just simple with three combo boxes for three search fields and a run query button.
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
:confused: Where were you going to show the results of the query?
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
I haven't made the report yet, I just wanted it to search the data correctly first.
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Okay, well let me get this started and we'll go from there. Will work on shortly, getting a little hungry :(
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Here you go, place this in the Event Procedure of your Command Button once you get your Form set up...

Make sure you do this in a copy of your database so we can test first!

Code:
[FONT=Courier New][COLOR=green][COLOR=green][FONT=Courier New]'Purpose:   This module illustrates how to create a search form, _[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]           where the user can enter as many or few criteria as they wish, _[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]           and results are shown one per line.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'Note:      Only records matching ALL of the criteria are returned.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'Author:    Allen Browne (allen@allenbrowne.com), June 2006.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Option Compare Database[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Option Explicit[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]Private Sub cmdFilter_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Dim strWhere As String                  [/FONT][/COLOR][COLOR=green][FONT=Courier New]'The criteria string.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Dim lngLen As Long                      [/FONT][/COLOR][COLOR=green][FONT=Courier New]'Length of the criteria string to append to.[/FONT][/COLOR]
 
[COLOR=green][FONT=Courier New]   '***********************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   'Look at each search box, and build up the criteria string from the non-blank ones.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   '***********************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   'Text field example. Use quotes around the value in the string.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   If Not IsNull(Me.cmbCity) Then[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       strWhere = strWhere & "([City] = """ & Me.cmdCity & """) AND "[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   If Not IsNull(Me.[/FONT][/COLOR]cmbCategory[COLOR=black][FONT=Courier New]) Then[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       strWhere = strWhere & "([[/FONT][/COLOR]Category type[COLOR=black][FONT=Courier New]] = """ & Me.[/FONT][/COLOR]cmbCategory[COLOR=black][FONT=Courier New] & """) AND "[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   If Not IsNull(Me.[/FONT][/COLOR]cmbLocation[COLOR=black][FONT=Courier New]) Then[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       strWhere = strWhere & "([[/FONT][/COLOR]Publication Location[COLOR=black][FONT=Courier New]] Like ""*" & Me.[cmb[/FONT][/COLOR]Location][COLOR=black][FONT=Courier New] & "*"") AND "[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End If[/FONT][/COLOR]
 
[COLOR=green][FONT=Courier New]   '***********************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   'Chop off the trailing " AND ", and use the string as the form's Filter.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   '***********************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]   'See if the string has more than 5 characters (a trailng " AND ") to remove.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   lngLen = Len(strWhere) - 5[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   If lngLen <= 0 Then     [/FONT][/COLOR][COLOR=green][FONT=Courier New]'Nah: there was nothing in the string.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       MsgBox "No criteria", vbInformation, "Nothing to do."[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Else                    [/FONT][/COLOR][COLOR=green][FONT=Courier New]'Yep: there is something there, so remove the " AND " at the end.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       strWhere = Left$(strWhere, lngLen)[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]       'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]       'Debug.Print strWhere[/FONT][/COLOR]
 
[COLOR=green][FONT=Courier New]       'Finally, apply the string as the form's Filter.[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       Me.Filter = strWhere[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       Me.FilterOn = True[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End If[/FONT][/COLOR]
[/COLOR][/FONT]
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
Do I need to create a report or a subform for the data to display in? I'm getting an error with this line:

Code:
Private Sub cmdFilter_Click()

I named my button cmdFilter however.
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Did you place the code in the Event Procedure section for the Command Button?
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Big OOPS :eek:, remove these lines...

Code:
Option Compare Database
Option Explicit
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
Hmm, removed those lines but still getting the same:

Compile error: Method or data member not found

On the same line as before.
 

Ebweaver

Registered User.
Local time
Today, 12:53
Joined
Jun 11, 2014
Messages
30
Table = tMediaContacts
Form = fSearch
Button = cmdFilter
Combo1 = cmbLocation
Combo2 = cmbCategory
Combo3 = cmbCity
 

GinaWhipp

AWF VIP
Local time
Today, 15:53
Joined
Jun 21, 2011
Messages
5,901
Hmm, typo :p Copy/paste this code

Code:
'Purpose:   This module illustrates how to create a search form, _
           where the user can enter as many or few criteria as they wish, _
           and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit
 
Private Sub cmdFilter_Click()
   Dim strWhere As String                  'The criteria string.
   Dim lngLen As Long                      'Length of the criteria string to append to.
 
   '***********************************************************************
   'Look at each search box, and build up the criteria string from the non-blank ones.
   '***********************************************************************
   'Text field example. Use quotes around the value in the string.
   If Not IsNull(Me.cmbCity) Then
       strWhere = strWhere & "([City] = """ & Me.cmbCity & """) AND "
   End If
 
   If Not IsNull(Me.cmbCategory) Then
       strWhere = strWhere & "([Category type] = """ & Me.cmbCategory & """) AND "
   End If
 
   If Not IsNull(Me.cmbLocation) Then
       strWhere = strWhere & "([Publication Location] Like ""*" & Me.[cmbLocation] & "*"") AND "
   End If
 
   '***********************************************************************
   'Chop off the trailing " AND ", and use the string as the form's Filter.
   '***********************************************************************
   'See if the string has more than 5 characters (a trailng " AND ") to remove.
   lngLen = Len(strWhere) - 5
   If lngLen <= 0 Then     'Nah: there was nothing in the string.
       MsgBox "No criteria", vbInformation, "Nothing to do."
   Else                    'Yep: there is something there, so remove the " AND " at the end.
       strWhere = Left$(strWhere, lngLen)
       'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
       'Debug.Print strWhere
 
       'Finally, apply the string as the form's Filter.
       Me.Filter = strWhere
       Me.FilterOn = True
   End If
 

Users who are viewing this thread

Top Bottom