asked on

Pass listbox value (as fieldname) to query

Experts:

I need some help (in Access) where selecting a list box value will open a query with the selected list box value being the ** field name ** (not the query criteria).

Please find attached sample database which contains the following objects:
1. tblFactors (with fields "Age", "Gender", and "Race")
2, Query2 -- executing the query will result in six (6) records with fieldname = [Listbox Field]. Thus, the query should only be executed via the "frmLogin"
3. frmLogin -- includes a listbox with "Age", "Gender", and "Race" being hardcoded

Current Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it now shows 6 records and all of them show "Age"... this doesn't work for me!

Envisioned Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it **should** now should the field [Age] and six records (i.e., 25, 41, ..., ..., 24).

My questions:
1. How should the VBA (in frmLogin) be modified to pass the listbox value as a field name into the query? How should be VBA be modified to show, e.g., both [Age] and [Race] in the query assuming both were selected in frmLogin?

2. Question #1 is the most important one at this time. However, ideally, I also would like to have a solution that accounts for having, potentially, up to 150 field in tblFactors. If so, I would like the listbox to dynamically include all available fields in the listbox. Is that possible? If so, how?

Thank you in advance.
EEH
SampleDatabase.mdb

Dale Fye

cannot open your db at the moment (iPad), but you would use the click event of the Listbox (assuming it is not multi-select), like:

Private Sub lstFields_Click

    dim strSQL as string

    strSQL = "SELECT ID, [" & me.lstFields.Value & "] FROM yourTable"
    docmd.runsql strsql

End sub

Open in new window

Or if you have a query that is already saved:

Private Sub lstFields_Click

    dim strSQL as string

    strSQL = "SELECT ID, [" & me.lstFields.Value & "] FROM yourTable"
    currentdb.querydefs("savedQueryName").SQL = strSQL
    docmd.openquery "SavedQueryName"

End sub

Open in new window

ExpExchHelp

ASKER

Dale:

Thank you for chiming in so promptly... I very much appreciate it.

Yes, I have a query (Query2) saved and, thus, your 2nd option works well. Please see below the modified SQL which dynamically calls the query for each of the three fields. Good so far...

... however, here's the catch though. In the actual database, I will have hundreds of fields. Therefore, per current code, I would have to replicate the same four lines hundreds of times (i.e., for each "If Me.ListBoxActions = "Age" Then"; If Me.ListBoxActions = "Gender" Then; If Me.ListBoxActions = "Race" Then, etc., etc.

Private Sub ListBoxActions_AfterUpdate()
  
    Dim strSQL As String
    
    
    'Option #1
    If Me.ListBoxActions = "Age" Then
            strSQL = "SELECT [" & Me.ListBoxActions.Value & "] FROM tblFactors"
            CurrentDb.QueryDefs("Query2").SQL = strSQL
            DoCmd.OpenQuery "Query2"
    End If
    
    
  
    'Option #2
    If Me.ListBoxActions = "Gender" Then
            strSQL = "SELECT [" & Me.ListBoxActions.Value & "] FROM tblFactors"
            CurrentDb.QueryDefs("Query2").SQL = strSQL
            DoCmd.OpenQuery "Query2"
    End If
     
     
  
    'Option #3
    If Me.ListBoxActions = "Race" Then
            strSQL = "SELECT [" & Me.ListBoxActions.Value & "] FROM tblFactors"
            CurrentDb.QueryDefs("Query2").SQL = strSQL
            DoCmd.OpenQuery "Query2"
    End If


End Sub

Open in new window

My follow-up questions:
1. How can the VBA be modified so that I don't have to have those multiple IF statement (for each value of the listbox)?
2. How can I modify the VBA so that multiple listbox values can be selected. That is, if I were to select "Age" and "Race", the query should output the respective data?

Thank you,
EEH

Dale Fye

No, you misunderstand, you don't need the IF End if construct do do this, you would simply have the 4 lines of code I've shown in the example, to handle all of the variations of Fieldnames, which is part of the reason I wrapped the name of the field in brackets, to account for the possibility that you have fields with a space.

ExpExchHelp

ASKER

Copy... sorry for the misunderstanding. I changed the code to the following:

Private Sub ListBoxActions_AfterUpdate()
  
    Dim strSQL As String
        
    strSQL = "SELECT [" & Me.ListBoxActions.Value & "] FROM tblFactors"
    CurrentDb.QueryDefs("Query2").SQL = strSQL
    DoCmd.OpenQuery "Query2"

End Sub

Open in new window

Your original response included:... ".... (assuming it is not multi-select)....:"

How can the code be modified to allow for multi-select values?

Dale Fye

Do you want to run the query N times for each of the selected items, or add multiple fields to a single query, based upon multiple selections?

ExpExchHelp

ASKER

The latter... multiple fields into a single query. Listbox value selections may not be in consecutive order.

I envision to add a command button to the form once multiple selections in listbox have been made and user is ready to execute query.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpExchHelp

ASKER

Dale:

Thank you for your continued help. Very much appreciated.

I added the following code (plus cmd button). Upon clicking on the cmd button, line "If Me.ListboxActions.ItemsSelected = 0 Then" is hightlighted. The dialog box shows a compile error indicating "Argument not optional".

What am I missing?

Private Sub cmd_RunQuery_Click()

    Dim varItem As Variant
    Dim strFieldList As String
    Dim strSQL As String

    If Me.ListBoxActions.ItemsSelected = 0 Then
        MsgBox "Select one or more items from the list!"
        Exit Sub
    End If

    For Each varItem In Me.ListBoxActions.ItemsSelected
        strFieldList = IIf(Len(strFieldList) = 0, "", ", ") & "[" & Me.ListBoxActions.ItemData(varItem) & "]"
    Next

    strSQL = "SELECT " & strFieldList & " FROM tblFactors"
    CurrentDb.QueryDefs("Query2").SQL = strSQL
    DoCmd.OpenQuery "Query2"

End Sub

Open in new window

Dale Fye

try:

IF me.ListboxActions.ItemsSelected.Count

ExpExchHelp

ASKER

One step further... that worked. Now, however, line "CurrentDb.QueryDefs("Query2").SQL = strSQL" is highlighted:

Error indicates: "The Select statement includes a reserved word or an argument name that is misspelled..."

Thoughts?

Dale Fye

add a line before that line:

debug.print strsql

Then take a look at the SQL string to see if something appears wrong. You can also copy it and paste it into the SQL window of a new query if it is not obvious what the problem is.

One possibility is that the field names are spelled wrong, or that you have captions associated with the fields in table design.

How are you getting the list of field names into your listbox?

ExpExchHelp

ASKER

Dale:

Attached are the error message (JPG) and the latest version of the database.

Not sure how to address the runt-time error.

Thank you for your help.

EEH
Error.JPG
SampleDatabase.mdb

ExpExchHelp

ASKER

Dale:

Based on the debug statement, the first selected value from the listbox is not inserted into the SQL statement.

That is, if I were to select, e.g., Age and Race from the listbox and the press the cmdButton, the strSQL reads:

"SELECT , [Race] FROM tblFactors"

How should the VBA be modified to account for multiple listbox selections?

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpExchHelp

ASKER

Great... last question before I close this item.

For the **single value** list box, I used the following line for grouping and sorting:

FROM tblFactors GROUP BY [" & Me.ListBoxActions.Value & "] ORDER BY [" & Me.ListBoxActions.Value & "]"

Open in new window

Applying the same idea and changing it to the line below, however, does not work:

strSQL = "SELECT " & strFieldList & " FROM tblFactors GROUP BY [" & Me.ListBoxActions.ItemsSelected & "] ORDER BY [" & Me.ListBoxActions.ItemsSelected & "]"

Open in new window

My question: How can I use "grouping" and "sorting" for the multi-value select approach?

EEH

ExpExchHelp

ASKER

Got it...

strSQL = "SELECT " & strFieldList & " FROM tblFactors GROUP BY " & strFieldList & "ORDER BY " & strFieldList

Open in new window

ExpExchHelp

ASKER

Dale:

As always, your solutions are SUPERB!!!

Thank you for your prompt assistance and helping me out w/ some VBA coding. Very much appreciated.

EEH

ExpExchHelp

ASKER

Did something change on EE? I awarded you 1000 points but now I see a "Closed Question Request"? In the past, once I awarded points, the question was immediately closed.

Dale Fye

Not sure, but don't sweat it, the points will eventually come through.

I did hear that there was some form of change, but have not witnessed it myself.