User Filters

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:
Rich (BB code):
Me.Filter = "[LastName] LIKE *'" & Me.txtLast & "'*"
Me.FilterOn = True
Me.Requery

In the AfterUpdate event of txtEmail, you would have this:
Rich (BB code):
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

Active Member
Joined
Jun 2, 2005
Messages
289
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Active Member
Joined
Jun 2, 2005
Messages
289

ADVERTISEMENT

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:

Code:
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Active Member
Joined
Jun 2, 2005
Messages
289
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
What comes to mind is that you might need to do a re-query on the sub form. That syntax would look like:
Code:
Forms!MainForm!SubFormControl.Form.Requery
You will need to put in the names you are using for MainForm and SubFormControl.
 

Forum statistics

Threads
1,136,649
Messages
5,676,989
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top