Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter Button (Access 2003)

    I have a form with 2 combo boxes to select records on a main form showing the company, then I have 2 subforms - one showing the Offices and the other showing the contacts.
    I created a command button to filter and show the current selections. My problem is: the focus stays on the 2nd combo box and I would like it to move to the Company Name. Then if they click the Filter button I would like it to filter by the Company name - without having to click on the filter by selection button - it then opens a Report that they can print the info from that 1 company. Now when I close the report I would like the filter to be turned off instead of having to click on the Remove Filter button.
    Thanks in advance.

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

    Re: Filter Button (Access 2003)

    Can you try to explain this again, step by step? I am completely confused by this, there may be two or three different questions here, but I'm not sure...

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Button (Access 2003)

    I have one form with 2 combo boxes that I can select the Company Type then select a company that belongs to that type. It displays the information for the Company on the main part of the form and in 1 subform, the offices that belong to that company and in another subform the contacts that belong to the office that you click on. I then created a filter button that will display a report . But first you have to click into the company name control on the form, then click the Filter by Selection button on the toolbar, then click my command button that previews the filtered information (Company, Offices and Contacts) of the filter by selection. When you close the report it returns to the form but then you have to click the remove filter button on the toolbar. I would like the command button I created to: Focus on the Company Name that is selected (move the forcus to it), do a Filter by Selection automatically and open the report; when you close the report to automatically remove the filter from the main Company form.

    Hopefully I have explained it a little better. Thanks Hans

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

    Re: Filter Button (Access 2003)

    The filter by selection shouldn't be necessary at all, but it is not clear to me how the report picks up the filter from the form. Can you explain that?

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Button (Access 2003)

    Hi Hans - Sorry I was out for the evening. Hope you still are following this.
    I set up a command button with an On Click procedure as follows:

    Private Sub Command38_Click()
    On Error GoTo errhandler

    Dim fil As Form
    Dim stDocName As String

    stDocName = "Alphabetical Contact List" '**The report you wish to preview**
    Set fil = Forms![Frm_Company] '**The form you have added the button to**


    If iFilterType = acApplyFilter Then
    DoCmd.OpenReport stDocName, acPreview, , fil.Filter '**If a filter has been set on the form then apply it to the report**

    Else
    DoCmd.OpenReport stDocName, acPreview '**If there is no filter then just generate the normal report**
    End If
    errhandler:
    End Sub

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

    Re: Filter Button (Access 2003)

    Try this. You must replace Company Name by the actual name of the company name field:
    <pre>Private Sub Command38_Click()
    Dim stDocName As String, stWhereCondition As String

    On Error GoTo ErrHandler

    stDocName = "Alphabetical Contact List" '**The report you wish to preview**
    stWhereCondition = "[Company Name] = " & Chr(34) & Me.[Company Name] & Chr(34)
    DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

    ErrHandler:
    End Sub
    </pre>

    If you have an ID field that uniquely identifies a company, it would be better to use that instead of the company name. Assuming it is named CompanyID and that it is a number field, the line strWhereCondition = ... in the code above would become
    <pre> stWhereCondition = "[CompanyID] = " & Me.[CompanyID]
    </pre>

    The report will display data for the current company only, no need to set and then remove a filter on the form.

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Button (Access 2003)

    Thanks so much Hans - worked great.

Posting Permissions

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