Filter subform based on text boxes

[lewis1682]

I have a form that I am using which has text boxes at the top, such as First Name, Last Name, Email, and more. Below that I have a subform which is linked to my table called Contacts_Info. This shows all my contacts. I want to be able to use the form above to filter the results below automatically when anything is typed into one of the text boxes, I then want to use the filtered results to create reports and mail merges etc. I have seen this done before but I cant remember how to do it, nor can I remember where I found it.

Thank you.

[offie]

Kind of like this? http://blogs.office.com/b/microsoft-...cess-2010.aspx
(For the search part)
Or do you want to just have the items filled out and then press a command button to refresh the query? (http://www.youtube.com/watch?v=CTiA_4Me0cI)

I use the wizard to make reports and then fiddle with them since I'm not doing anything specific with the reports.

I don't know about mail merges but I'm sure googleing it will show up with something.

[lewis1682]

Thank you, the first one is exactly what I wanted to do, but how would I then use the results to create mailing labels for example?

[offie]

I wouldn't know. I don't know if access can do that (doubt it). But you can export the data to another office program and control it remotely with access (vba) to format what you want. What program do you generally use to make mailing labels? If you go to a previous post of mine (has to do with working every other time) there is an example of a code that makes graphs in excel. The specifics would be different but the general steps would be the same. Again, never made mailing labels before so I'm just guessing this would work.

Actually thinking about it maybe reports have a feature like that? I don't know, research it.

Edit: found something http://office.microsoft.com/en-us/ac...005187416.aspx
Also searched through the forums and it seems to be pretty well used so there are lots of posts that can answer your questions.

Also I just re-read your original post, just making sure that you know that you want a query in the subform not the table.

[lewis1682]

I still cant get the filter to filter the results, in the combo box it does show all the first names, however it doesn't filter them, I am using a query called View contacts in the subform. The main form is called Contacts and the subform is called View_Contacts subform. This is the code I am using:

Private Sub cboFilter_Change() ' If the combo box is cleared, clear the form filter.
  If Nz(Me.cboFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
    
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.cboFilter.ListIndex <> -1 Then
    Me.Form.Filter = "[FirstName] = '" & _
                     Replace(Me.cboFilter.Text, "'", "''") & "'"
    Me.FilterOn = True
    
  ' If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[FirstName] Like '*" & _
                     Replace(Me.cboFilter.Text, "'", "''") & "*'"
    Me.FilterOn = True


  End If
  
  ' Move the cursor to the end of the combo box.
  Me.cboFilter.SetFocus
  Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
End Sub

This is what I get when I open the form
As you can see it doesn't only show Anna

Thank you again.

[offie]

Is your combo named cboFilter? If not then either change the name or the code so that they match.

[lewis1682]

I have sorted this out now, I am now using the same query but in the criteria I have put

 Like "*" & [forms]![Contacts]![s_FirstName] & "*"

Then in my form I have added a textbox and called it s_FirstName, then added

 Private Sub s_FirstName_AfterUpdate()Forms!Contacts!View_Contacts_subform.Requery
End Sub

This now does what I wanted it to do, however one more question, do you know how to change the first name into a link so when I click it opens up in a form with that contact displayed, thanks.

[offie]

Not really. If you can figure out how to refer to a selection (like in excel vba) then you could build a query in VBA using criteria from the selected record then using the query to build a form. I would post this as another questiton to see what other people have to say.
Does it have to be on click of the record? I would make another query out of you results query so that the data is filtered, add a field that is [FirstName] & [LastName], show that in a combobox and then in the after update of the combo (or a on click on a command button) open a form or report based on a query that matches the selection. Not pretty but it would get the job done.