Access 2007: Add Filter to VBA Script controlling export of data to MS Word?

Posted on 2011-04-28
Last Modified: 2012-05-11
Hi everyone, I have a VBA script that experts here have crafted over time which exports selected fields (as captions) to a Microsoft Word document.  I know how to create a 'filter' in a SQL query with where clauses, but I would like to implement a method to do this on the form.  I wanted something similar to: (the highlighting is optional but I liked the search box with combo box populated by relevant fields).  I've tried to modify this code with my form 'MakeWordTable' but it doesn't seem to be working.  I've attached the database here if anyone can take a look at what I'm doing and try to help I'd very much appreciate it.  As of now I keep getting errors saying that it can't find the field I've inputted into the combo box.   If users think I am barking up the wrong tree with this approach let me know that as well and please offer another suggestion.  Even a simple solution would be excellent.  My ultimate goal is to perhaps have two or three methods for the user to filter the data on the MakeWordTable form.  

I'm attaching the file here if anyone is interested in looking.

Thank you so much,
Question by:Bevos
    LVL 22

    Accepted Solution

    In the Command2_Click after the line:
    strSql = "select " & fieldlist & " from qryAll"
    I've added these lines:
    If Not IsNull(Me.cboField) Then
        strSql = strSql & " WHERE " & Me.cboField & " like '*" & Me.txtSearchText & "*' "
    End If

    Then I selected
    show only where field Infection contains c
    on the form, and this seemed to do the trick. It probably needs some tweaking, but I hope you get the general ID. If this is (more or less) what you need you can remove all the code from the cboField and txtSearchText controls.


    Author Comment

    This works, jawesome nicobo.

    So you are saying i should remove all code ascribed to items and it will still work?

    LVL 22

    Expert Comment

    by:Nico Bontenbal
    Yes, delete the
    Private Sub cboField_AfterUpdate()
    Private Sub txtSearchText_AfterUpdate()
    procedures, and it will still work.

    Author Closing Comment

    Thanks so much Nicobo!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now