Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Button for Filter on Form (2002, sp2)

    I have a form with volunteer info with a subform for volunteer hours. On the form I'd like to have a button to click to filter by last name, and then a button to remove the filter. Is there a way to have just a "filter for" type box, where they would type the last name? I'd like to skip the step of having to apply the filter of "filter by form" if possible.
    egghead

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button for Filter on Form (2002, sp2)

    I would create a query that lists unique last names, and use this as row source of a combo box. That way, when the user starts typing a last name, Access will complete it if possible, saving keystrokes. You can filter the form in the After Update event of the combo box:

    Private Sub cboLastName_AfterUpdate
    If IsNull(Me.cboLastName) Then
    ' Remove filter
    Me.Filter = ""
    Me.FilterOn = False
    Else
    ' Filter form
    Me.Filter = "LastName = " & Chr(34) & Me.cboLastName & Chr(34)
    Me.FilterOn = True
    End If
    End Sub

    If the user selects/types a name, the form will be filtered for that name. If the user clears the combo box, the filter will be removed.

    Of course, you must substitute the name of the combo box and of the LastName field you have.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button for Filter on Form (2002, sp2)

    I've partially got it set up, but have some questions on it. Access has such a learning curve!

    I have a query called "Unique Last Names" with one field "Last" set to unique values.

    I made a copy of my main form/subform which has the volunteer info on the main and their hours as a subform. In the copy, which is called "Volunteer Hours Data Entry" I deleted the Last field, created a combo box, and set the row source to my Unique Last Names Query. So, in form view, the last field is blank with a drop down, but the rest of the main form is filled in with the data from record one (the volunteer with the first name of Gary).

    In the Module objects I clicked New which brought up a window with the Module 1 window in it. At the top of the typing area it has "Option Compare Database". Do I delete that before pasting in your "Private Sub . . ."? Then, what do suggest it gets saved as?

    In the After Update row of the Properties for the combo box, what goes there? The name of the module?

    Thanks in advance; I'm amazed at your knowledge!
    egghead

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button for Filter on Form (2002, sp2)

    In the first place, I would restore the text box bound to the Last field. This text box displays the last name from the current record; the combo box is solely meant to specify the filter. The combo box should have a different name than the field itself.

    The code I provided does not go into a module created from the Modules tab, but into the module belonging to the form. Create it as follows:
    <UL><LI>Open the form in design view.
    <LI>Click on the combo box.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the After Update event.
    <LI>From the dropdown list in this event, select [Event Procedure].
    <LI>Click the builder button, i.e. the three dots ... to the right of the dropdown error.
    <LI>The Visual Basic Editor will be activated, with the form module open, and the first and last line of the code already created for you:

    Private Sub cboLastName_AfterUpdate()

    End Sub

    (with the name of your combo box instead of cboLastName, if different.)
    <LI>You can type or copy/paste the code into the empty line between Sub ... and End Sub. Make sure you don't get duplicate Sub ... or End Sub lines - they have already been created for you.
    <LI>When you're done, switch back to Access. You can now close and save the form, and try it.[/list]Notes:
    - You don't need Module1 at the moment; you can delete it if it's still present.
    - I recommend selecting Tools | Options... in the Visual Basic Editor, and ticking the check box "Require Variable Declaration. You only need to do this once. It will save you a lot of grief if you go on to write more code.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button for Filter on Form (2002, sp2)

    Thank you! I'm getting closer, I think.

    I now have my regular form with "Gary" and his last name" with a combo box above labelled Last Name. The row source is set to:SELECT [Unique Last Names].Last FROM [Unique Last Names] ORDER BY [Last]; The name to cboLastName. In the AfterUpdate I have [Event Procedure]. When I click the ellipses I have:

    Private Sub cboLastName_AfterUpdate()
    If IsNull(Me.cboLastName) Then
    ' Remove filter
    Me.Filter = ""
    Me.FilterOn = False
    Else
    ' Filter form
    Me.Filter = "LastName = " & Chr(34) & Me.cboLastName & Chr(34)
    Me.FilterOn = True
    End If
    End Sub

    When I choose a last name from my combo box the status bar says "filtered" but all 117 records are still showing with Gary, of course, still showing.

    Any ideas?

    Thanks! I changed the VB option, too, now. What does that change?
    egghead

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button for Filter on Form (2002, sp2)

    1. Change the row source of the combo box to Unique Last Names (you can select it from the dropdown list in the Row Source property). This is more efficient than having an SQL statement in this property.

    2. Since the name of the "last name" field in your table is Last, not LastName, you must change

    Me.Filter = "LastName = " & Chr(34) & Me.cboLastName & Chr(34)

    in the code to

    Me.Filter = "Last = " & Chr(34) & Me.cboLastName & Chr(34)

    <img src=/w3timages/blueline.gif width=33% height=2>

    The "Require Variable Declaration" option forces you to declare variables in your code explicitly. This reduces the chance of errors by small typing mistakes. Since you haven't gone deeply into VBA yet, it is a bit early to go into technical details.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button for Filter on Form (2002, sp2)

    I just realized that the VB referred to Last Name, and mine is just called "last". I change that and now it works!! Sorry about that! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    egghead

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •