User Filters

[bschulze]

Not quite sure how to get started on this and I have been unsuccessful locating a solution on the board. I am new at Access/VB so bear with me.

This particular DB will resemble a Contacts DB. I am creating a Form that is query driven that the user will use to imput information. I would like to give the User functionality of filtering on fields like First Name, Last Name, Email, etc.... I dont beleive Form Filter will work for me because I would like the filter to update "on the fly". For instance if the user was looking for a person with the last name "Smith" and there were 50 people. I would like the user to then be able to filter on the first name field and ONLY see the first names of the people with the last name of "Smith". I hope this makes sense.

I dont have anything to share with the board (as I have not started as of yet), I am however looking for some suggestions to get started....Or if what I am thinking about is not possible maybe a workaround. Thanks in advance

 

[SydneyGeek]

There are a couple of ways to go with this. Possibly the most flexible (if you want to search for parts of fields) is to use textboxes. You will need to put some unbound textboxes in the header of the form. Let's say you decide to filter by LastName and Email. Name one unbound textbox txtLast and the other txtEmail.
In the AfterUpdate event of txtLast, you would have code like this:

Me.Filter = "[LastName] LIKE *'" & Me.txtLast & "'*"
Me.FilterOn = True
Me.Requery

In the AfterUpdate event of txtEmail, you would have this:

Me.Filter = " LIKE *'" & Me.txtEmail & "'*"
Me.FilterOn = True
Me.Requery[/code]

You also need to be able to clear the filters. A button with this in the Click event would do it:
[code=rich]Me.FilterOn = False
Me.Requery[/code]

When users type in all or part of a surname into txtLast and press TAB, the form will filter to show those records. For example, entering [b]har[/b] into txtLast could return Harris, Harmison, Harrison, Sharman, and others.
If you want users to only find the beginning of words, removing the first asterisk in each of the filter lines will do that for you.  

It is also possible to filter on the contents of 2 or more textboxes, but the code is slightly more complex. Feed back if that is what you need. 

Denis

 

[bschulze]

My apologies for just getting back to you. You have the right idea, but I need to tweak it a little bit. I don’t want the user to have to type anything. The users should only have to click to drop down menu to choose the Name, Email, ID, etc.... Also there are going to be several fields to filter and I want the user to be able to filter anyone of them at any time. The Clear Filters button will really come into play because of this reason, so good suggestion. Is there a way to do this? Another option that I would like is an "Enabled" button, as I don’t want the user to be able to edit certain fields unless they have selected this button. I would appreciate any ideas or assistance you can provide. Thanks

 

[VicRauch]

Function UpdateFilter()
With U
  .FrmFilter = ""
  If Len(Nz(CustomerName)) <> 0 Then
    .FrmFilter = .FrmFilter & "(CustomerName Like """ & CustomerName & "*"")"
  End If
  If Len(Nz(Contact)) <> 0 Then
    If Len(.FrmFilter) > 0 Then
      .FrmFilter = .FrmFilter & " AND "
    End If
    .FrmFilter = .FrmFilter & "(Contact Like """ & Contact & "*"")"
  End If
  If Len(Nz(CustomerNumber)) <> 0 Then
    If Len(.FrmFilter) > 0 Then
      .FrmFilter = .FrmFilter & " AND "
    End If
    .FrmFilter = .FrmFilter & "(CustomerNumber Like '" & CustomerNumber & "*')"
  End If
  If Len(Nz(City)) <> 0 Then
    If Len(.FrmFilter) > 0 Then
      .FrmFilter = .FrmFilter & " AND "
    End If
    .FrmFilter = .FrmFilter & "(City Like '" & City & "*')"
  End If
  If Len(Nz(State)) <> 0 Then
    If Len(.FrmFilter) > 0 Then
      .FrmFilter = .FrmFilter & " AND "
    End If
    .FrmFilter = .FrmFilter & "(State Like '" & State & "*')"
  End If
  Me.Section(acDetail).Visible = True
  Me.Section(acFooter).Visible = True
  DoCmd.RunCommand acCmdSizeToFitForm
  Me![Select Job].Form.Filter = .FrmFilter
  Me![Select Job].Form.FilterOn = True
End With
End Function

To understand this code, "U" is a class that I use that holds "everything" that I will use some time down the line. The class "U" also stores everything in a hidden form, so I can either refer to the "U" class, or the same variable name on the hidden form. For example, I can use the hidden form when I'm using one of these stored variables as a criteria within a query.

The form that this code is behind has 5 unbound fields in the main form: CustomerName, Contact, CustomerNumber (text), City, and State. The AfterUpdate event for each of these fields is set to: =UpdateFilter() which causes the filter to be updated, therefore the listing of customers in the subform to be updated too. These same five fields are the fields in the subform too. When this form is initially opened, only the 5 fields in the main form are visible. During the first time this function (UpdateFilter() ) is run, the bottom portion of the form that contains the subform is made visible, and the form is sized to fit the new visible portion of the whole form.

Hope this helps and the code will be of assistance too. If you have any questions about this code, do not hesitate to ask. I'm sure that even if I can't get back to you, there are a lot of others on this board that can help.

 

[SydneyGeek]

If you use the same idea but have combo boxes instead of text boxes, the users can type the first couple of letters to filter down to the desired item and then pick it from the list. The AfterUpdate code then becomes

Me.Filter = "[LastName] ='" & Me.txtLast & "'"
Me.FilterOn = True
Me.Requery

And so on

For LastName, the RowSource would be
SELECT DISTINCT LastName FROM Clients

...assuming you were pulling data from the Clients table

Denis

 

[VicRauch]

The difference with the combo box is after typing 3 letters and then taking that entry is you will get only that entry, not every entry that starts with "vic" which could be quite a few. If you know the first name started with "vic" and the last name was a "j" something, the combo box approach really does not lend itself to allowing that typd of search. But, if you know your customers quite well, the combo box is, in my opinion a much better approach. And if you don't know the base well, then using the text box approach would usually be better, again my opinion. Thanks for the added possibility Denis.

 

[bschulze]

SydneyGeek...Sorry to reopen the post but I have a couple of questions. I scrapped what I started with (project put on hold). Now I am trying to do something very similar but I cant get it to work. I am making a lookup form for Jurisdictions based on State, County, City, and Zip fields. I have the text boxes on the form (In the Detail Section) and a subform to display the results. I am using the method below:

Private Sub txtState_AfterUpdate()
Me.Filter = "[State] LIKE *'" & Me.txtState & "'*"
Me.FilterOn = True
Me.Requery
End Sub

When I move to another tab position I get an error.
{Run-Time error '2448':}
{You can't assign a value to this object.}

Any ideas what may be causing this? I moved the unbound text box to the header and it still did not work. Also do it matter that the subform display a query rather than a table? Any help would be great.

 

[VicRauch]

Do you want to end up with an astric on each side of whatever is in the txtState text box? If so, you need to put the single quote on the outside of the asterics. You have *'TX'* but I think you want '*TX*'
HTH,

 

[bschulze]

you were correct about the single quote...this corrected the error...Now I have a different issue, the filter did not work. Nothing happens when I type in a valid name, the datasheet in the subform stays exactly the same. How does access know that I want to filter that query that is displayed on the subform? This only refers to a field in a query not the actual query itself...does it matter?

 

[VicRauch]

What comes to mind is that you might need to do a re-query on the sub form. That syntax would look like:

Forms!MainForm!SubFormControl.Form.Requery

You will need to put in the names you are using for MainForm and SubFormControl.