Search Button Based from Query (1 Viewer)

[Ebweaver]

Okay it seems like no other problems with the code, however when I click the filter button nothing happens. Is this when I need a report or subform for the data to show up in? Also, thank you so much for your help on this.

 

[GinaWhipp]

The way this is set up there is no subform. The Command Buttons and the Combo Boxes are in the Header part of the Form and the Form itself is continuous.

 

[Ebweaver]

Yes, I have it set up just like that. The filter command button and combo boxes are in the Form Header - and the default view is set to Continuous Forms.

 

[GinaWhipp]

What is the Record Source of the Form?

 

[Ebweaver]

Oh I see, there was no record source - it's set to tMediaContacts now. I feel dumb. So now when I click my filter button, it will find based on my search criteria - then I need to create a report that I click to open after I hit the filter button?

 

[GinaWhipp]

Happens to the best of us.

Create the report using the same Record Source and make it look the way you want ant then post the name here.

 

[Ebweaver]

Report name is rMediaContacts

 

[GinaWhipp]

Okay, give me a second... having dinner!

 

[GinaWhipp]

This should open the Report the same time the button is clicked...

'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
 
        'Apply Filter to Report
        DoCmd.OpenReport "rMediaContacts", acViewPreview, , strWhere
        DoCmd.Maximize
        Reports![rMediaContacts].Filter = strWhere
        Reports![rMediaContacts].FilterOn = True
   End If

 

[Ebweaver]

I just add that in under the other code before End Sub?

Edit:Nevermind I see it's already all there.

 

[Ebweaver]

It works great! Thank you so much, now I have some designing to do to make it look nice.

 

[GinaWhipp]

No problem, glad to help!