Access 2007 List Box Field Selection to Query input

Posted on 2012-09-15
Medium Priority
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

IrogSinta earned 2000 total points
ID: 38402307
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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