Filter form by using search form

[Jtorres13]

Hello. I have a form that displays all records from two tables. The form has a main form and a subform. The main form is just records for families and the subform are related records regarding the family members like, birthdate.

I want to be able to click a button that will open a small form with first name, last name, and zip code fields. I will enter the last name and zip code and click a button to apply the filter. This will filter the main form. This in turn refreshes the subform.

I'm totally stuck. I've read the Help info on ApplyFilter Method but can't grasp the concept. Could someone make a small example database? If it's not too much trouble, thank you.

 

[TheAceMan1]

How are ya Jtorres13 . . .

Instead of the form, you can use a [blue]combobox[/blue] for this, placing the combo in the header or footer of the mainform. Use the combobox wizard and select the third option.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Jtorres13]

Thanks you for the combo box suggestion. The number of records and the number of repetitive last names makes it hard and long to find a specific person in the database. The combo box doesn't help because it displays all the last names, even repeating ones.

This is why I need to offer the user three fields to search on: first name, last name, and zip code.

I already have a list box in the form showing the last names for quick browing. So, the combo box is not better than the list box. It's pretty much the same.

I would still want to learn to do the filter, If someone can help with that, I will really appreciate it.

 

[TheAceMan1]

Jtorres13 . . .

Have it your way. Is zipcode on the mainform as well as first/last name?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Jtorres13]

Yes, all three are in the main form.

 

[TheAceMan1]

Jtorres13 . . .

In the [blue]Tag[/blue] property of the three textboxes enter a question mark [purple]?[/purple] (no quotations please).

Then in the [blue]On Click[/blue] event of a command button on the mainform, copy/paste the following:

[blue]   Dim Cri As String, ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]?[/b][/purple]" Then
         If Trim(ctl & "") <> "" Then
            If Cri <> "" Then
               Cri = Cri & " AND ([" & ctl.Name & "] = '" & ctl & "')"
            Else
               Cri = "([" & ctl.Name & "] = '" & ctl & "')"
            End If
         End If
      End If
   Next
   
   If Cri <> "" Then
      Me.FilterOn = False
      Me.Filter = Cri
      Me.FilterOn = True
   Else
      Me.FilterOn = False      
      MsgBox "No Criteria Entered!"
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

here's the filter

=(forms!Inputform.firstnametxtbox) and (forms!Inputform.tlastnametxtbox) or (forms!Inputform.tziptxtbox)

Ian Mayor (UK)
Program Error
If people say I have bad breath, then why do they continue to ask me questions and expect me to answer them?