Access 2007 List Box Field Selection to Query input

Posted on 2012-09-15
Last Modified: 2016-01-28
I have a table as follows ....

Report Name    Loss Ref Num       Claim Num      Accident Date

Report1                   x                           na                     na  
Report2                  na                           x                      na  
Report3                  na                           x                       x

If the report output column applies, it is populated with an 'x', otherwise 'na'. I want to build a list box of the column names (Loss Ref Num, Claim Num, etc.) and if I select 'Loss Ref Num' for example from the listbox, pass the selections to a query as follows:

SELECT [Report Name]
FROM tbl1
WHERE ((tbl1.[Loss Ref Num])="x");

Struggled with this for a couple of days .... time to ask for help.

Many thanks, mec412
Question by:mec412
    1 Comment
    LVL 29

    Accepted Solution

    In the RowSourceType property for your listbox, change it to VALUE LIST
    Then in the RowSource property, add Loss Ref Num; Claim Num; Accident Date
    In the AfterUpdate event for the listbox, add some code similar to this:
        Dim strSQL   As String
        strSQL = "SELECT [Report Name] FROM tbl1 WHERE [" & Me.ListBoxName & "] = 'x'"
        CurrentDb.QueryDefs("qry_NameOfQuery").SQL = strSQL

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now