none
Extended Listbox VBA help RRS feed

  • Question

  • Hello,
    Thanks in advance for your help. I have an Access 2010 database on a Win7 PC. I have never used list boxes on my previous forms before and I need some help. My form name is frmLocationRpt. I added a list box called listLocation. 
    Row Source :
    SELECT [3PLvsGSO listing].Loc
    FROM [3PLvsGSO listing]
    GROUP BY [3PLvsGSO listing].Loc, [3PLvsGSO listing].Loc
    ORDER BY [3PLvsGSO listing].Loc;
    Multi Select: Extended

    I also added a command button called cmdSearch. From what I have researched, I’ll need to add some VBA code to produce my results. 

    I have a query called selLocationResults. Here’s the SQL: 
    SELECT [WIP-Download].[Cust Dept], [WIP-Download].[Print Wave], [WIP-Download].[Wave Descrip], [WIP-Download].[Status Flag], [WIP-Download].[PT Status Flag], [WIP-Download].[Order #], [WIP-Download].[PT #], [WIP-Download].ShipTo, [WIP-Download].[Ship_ VIA], [WIP-Download].Customer, [WIP-Download].SoldTo, [WIP-Download].CustSoldToName, [WIP-Download].[Cust PO#], [WIP-Download].[Total Qty], [WIP-Download].Area, [WIP-Download].Zone, [WIP-Download].Aisle, [WIP-Download].Bay, [WIP-Download].Lvl, [WIP-Download].Pos, [WIP-Download].[Pkt Gen Date], [WIP-Download].[Start Ship], [WIP-Download].[Stop Ship], [WIP-Download].[Carton Number], [WIP-Download].[Shipment Number], [WIP-Download].PS, [WIP-Download].[Last Modified Date], [WIP-Download].[Carton Pallet ID], [WIP-Download].Whse, [WIP-Download].DecoratedStatus, [WIP-Download].PHSC2, [WIP-Download].PHLBTR, [WIP-Download].PHSVIA, [WIP-Download].InProcess, [WIP-Download].InPicking
    FROM [WIP-Download];

    I want the end user to select the location(s) (field name Loc) in the listLocation and have the selLocationResults open with the results where listLocation.Loc = selLocationResults.Aisle. I want to display all the fields listed in the selLocationResults query.

    What VBA do I need to add to the cmdSearch On Click property?

    Thank you,
    Kevin



    Tuesday, April 16, 2019 2:16 PM

Answers

  •  I tried enclosing Aisle with quotes.


    It's not the column name which should be wrapped in literal quotes characters if the column is of text data type, but the value.  So, is it of text data type or not?  If it is:

        strIn = strIn & """" & Me.listAisle.ItemData(VAR) & ""","

    BTW you don't seem to be requiring explicit declaration of variables.  Bad Idea!


    Ken Sheridan, Stafford, England

    • Marked as answer by KevinATF Monday, April 22, 2019 3:01 PM
    Friday, April 19, 2019 9:13 PM

All replies

  • Hi Kevin. First of all, I would suggest changing the row source of the listbox to simply have:

    SELECT DISTINCT [3PLvsGSO listing].Loc
    FROM [3PLvsGSO listing]
    ORDER BY [3PLvsGSO listing].Loc;

    Are you saying you want to "open" the query or "modify" the query? I mean, where are you using the query setLocationResult? Is it used on a form or a report?

    Tuesday, April 16, 2019 4:30 PM
  • Hi DBguy.

    It's always a pleasure to hear from you. I just want to open the select query for now. Eventually, I will build a report for this data. I'll make the necessary changes to the listbox row source.

    I'm hoping that this lesson in "Listboxes 101" will allow me to learn how listboxes are setup. And I hope to build on that knowledge.

    Cheers,

    Kevin

    Tuesday, April 16, 2019 5:09 PM
  • Hi DBguy.

    It's always a pleasure to hear from you. I just want to open the select query for now. Eventually, I will build a report for this data. I'll make the necessary changes to the listbox row source.

    I'm hoping that this lesson in "Listboxes 101" will allow me to learn how listboxes are setup. And I hope to build on that knowledge.

    Cheers,

    Kevin


    Hi Kevin. The thing is, if you open the query, we would need to modify it to include the WHERE clause. However, if you use it for a Form or Report, then we don't need to modify it at all because we can pass the query criteria using the WhereCondition argument of the OpenForm or OpenReport method. So, if you intend to use the query as a Record Source for a Form or a Report, I would suggest we just use code to build the criteria and then open the Form or Report passing the criteria to it without touching the query.
    Tuesday, April 16, 2019 7:08 PM
  • Sounds good. What would the VBA for the cmdSearch button On Click property look like to pass this criteria to a report? Let's call the report rptLocationResults.
    Tuesday, April 16, 2019 7:27 PM
  • Hi DBguy,

    I like your suggestion about passing my listbox criteria to a report. Can you gibe me an example of the VBA I need for the On Click property of my cmdSearch button?

    Cheers, Kevin

    Wednesday, April 17, 2019 12:08 PM
  • Hi DBguy,

    I like your suggestion about passing my listbox criteria to a report. Can you gibe me an example of the VBA I need for the On Click property of my cmdSearch button?

    Cheers, Kevin


    Hi Kevin. So, working backwards, let's say you have a query that returns all the records from a table and you use it for your report. Meaning, if you open the report, you'll see all the data from the table. Now, to open the report using code and filtering the data using a criteria, we can do something like this:

    DoCmd.OpenReport "ReportName", acViewPreview, , "FieldName=CriteriaValue"
    So, the "FieldName=CriteriaValue" part will be the one we'll need to create using your Listbox. Does it make sense?
    Wednesday, April 17, 2019 4:37 PM
  • Actually, I'm looking for some help with VBA code for my command button that will return records specified in my listbox. My command button is named cmdSearch. In the On Click property, I added the follow code below. This appends my listbox selections to a table named ListBox01. I then can use this table with my query to report the records I selected in my listbox. Please let me know if there is a simpler option. Cheers, Kevin

    Private Sub cmdSearch_Click()
        Dim x As Long
        DoCmd.SetWarnings False
        'Deletes data from table ListBox01.
        DoCmd.RunSQL ("Delete * from ListBox01")
        'Appends selected listbox items to table ListBox1.
        For x = 0 To Me.listAisle.ListCount - 1
            If Me.listAisle.Selected(x) = True Then
                DoCmd.RunSQL ("Insert INTO ListBox01 (ValueText) values ('" & Me.listAisle.ItemData(x) & "')")
            End If
        Next x
        'Verifies selection was made in listbox.
        If DCount("ValueText", "ListBox01") < 1 Then
            MsgBox "Please select Location(s)", vbOKOnly, "ALERT"
        End If
        'Clears listbox selected items.
        Me.listAisle.Value = Null
        Me.listAisle.Requery
    End Sub

    Thursday, April 18, 2019 12:27 PM
  • It sounds like you are trying to correlate two list boxes.  If so you might like to take a look at CorrelatedLists.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    This little demo file illustrates how to correlate a second list box on the basis of one or more selections made in the first, using Northwind data as its example.  The results can then be restricted by selecting one or more items in the second list box.

    In the demo the results are returned in the form, but could equally well be returned in a report with the same RecordSource property as the form.  The query calls the InParam function to restrict the results.  This function, along with the GetToken function which it calls, is contained in the basInParam module in the database, so you'd need to copy this module to you database to make use of the functions.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, April 18, 2019 1:28 PM Typo corrected.
    Thursday, April 18, 2019 1:26 PM
  • Hi Ken,

    Thanks for your suggestion. I downloaded your Db and that is impressive. I'm looking at using one listbox. Below is a screenshot of my listbox. Using the VBA code stated in my last reply, I placed it in the Search button's On Click property. If there's a simpler approach, please let me know. This is the first time I have needed to use a listbox. So please let me know if there is any information I can provide. Cheers, Kevin

    Thursday, April 18, 2019 1:43 PM
  • Actually, I'm looking for some help with VBA code for my command button that will return records specified in my listbox. My command button is named cmdSearch. In the On Click property, I added the follow code below. This appends my listbox selections to a table named ListBox01. I then can use this table with my query to report the records I selected in my listbox. Please let me know if there is a simpler option. Cheers, Kevin

    Private Sub cmdSearch_Click()
        Dim x As Long
        DoCmd.SetWarnings False
        'Deletes data from table ListBox01.
        DoCmd.RunSQL ("Delete * from ListBox01")
        'Appends selected listbox items to table ListBox1.
        For x = 0 To Me.listAisle.ListCount - 1
            If Me.listAisle.Selected(x) = True Then
                DoCmd.RunSQL ("Insert INTO ListBox01 (ValueText) values ('" & Me.listAisle.ItemData(x) & "')")
            End If
        Next x
        'Verifies selection was made in listbox.
        If DCount("ValueText", "ListBox01") < 1 Then
            MsgBox "Please select Location(s)", vbOKOnly, "ALERT"
        End If
        'Clears listbox selected items.
        Me.listAisle.Value = Null
        Me.listAisle.Requery
    End Sub

    Well, I was going to get into that but just making sure we're on the same page at every step of the way. So, instead of creating a table, I was thinking you could simply construct the "FieldName=CriteriaValue" part of the code I posted earlier using your listbox. So, normally, a criteria using multiple values would be using an In() clause. For example: "ID In(1,2,3,4,5)" where ID is the field in the table and 1,2,3,4,5 are the values selected from the Listbox. If you were able to construct this criteria using code, then our code to open the report would then look something like this:

    DoCmd.OpenReport "ReportName", acViewPreview, , "ID In(1,2,3,4,5)"

    So, if you are following me so far, here's how you could create the 1,2,3,4,5 part from your Listbox:

    For Each var in Me.ListboxName.ItemsSelected
        strIn = strIn & Me.ListboxName.ItemData(var) & ","
    Next

    Then, your button's code to open the report might look something like this:

    DoCmd.OpenReport "ReportName", acViewPreview, , "ID In(" & strIn & ")"

    Hope it helps...

    Thursday, April 18, 2019 2:58 PM
  • I'm looking at using one listbox.
    To restrict a report on the basis of selections in a multi-select list box you have two options:

    1.  Restrict the report's RecordSource query by means of a parameter by calling the InParam function in the query.   The parameter in this case is a hidden text box control which is populated with code which loops through the list box's ItemsSelected collection.  You'll find an example in DatabaseBasics.zip in the same OneDrive folder to which I gave you a link earlier.  In this, the section on 'retrieving data from the database' includes a form in which one or more contacts can be selected in a list box.  The code behind the button to open a report is:

        Dim varItem As Variant
        Dim strContactIDList As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstContacts
        
        If ctrl.ItemsSelected.Count > 0 Then
            ' if at least one item has been selected in the list box
            ' loop through the control's ItemsSelected collection
            ' and build a value list of ContactID values
            For Each varItem In ctrl.ItemsSelected
                strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strContactIDList = Mid(strContactIDList, 2)
            ' assign value list to hidden text box control
            Me.txtContactIDList = strContactIDList
            
            DoCmd.OpenReport "rptContacts_1", View:=acViewPreview
        Else
            MsgBox "No contacts selected", vbInformation, "Warning"
        End If

    2.. Alternatively, you can drop the report's query's WHERE clause and filter the report by means of the WhereCondition argument of the OpenReport method.  The equivalent code for this would be:

        Dim strContactIDList As String
        Dim strFilter As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstContacts
        
        If ctrl.ItemsSelected.Count > 0 Then
            ' if at least one item has been selected in the list box
            ' loop through the control's ItemsSelected collection
            ' and build a value list of ContactID values
            For Each varItem In ctrl.ItemsSelected
                strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strContactIDList = Mid(strContactIDList, 2)
            ' build string expression to filter the report
            strFilter = "ContactID IN(" & strContactIDList & ")"
            
            DoCmd.OpenReport "rptContacts_1", View:=acViewPreview, WhereCondition:=strFilter
        Else
            MsgBox "No contacts selected", vbInformation, "Warning"
        End If



    Ken Sheridan, Stafford, England

    Thursday, April 18, 2019 3:57 PM
  • Hi DBguy,

    I jumped a few steps ahead. My apologies. I'm looking at your suggestion. I'm assuming that I would place the following code in the button's On Click property:

    DoCmd.OpenReport "ReportName", acViewPreview, , "ID In(" & strIn & ")"

    Where would I need to place this code?

    For Each var in Me.ListboxName.ItemsSelected
        strIn = strIn & Me.ListboxName.ItemData(var) & ","
    Next

    Cheers, Kevin

    Thursday, April 18, 2019 4:59 PM
  • Hi DBguy,

    I jumped a few steps ahead. My apologies. I'm looking at your suggestion. I'm assuming that I would place the following code in the button's On Click property:

    DoCmd.OpenReport "ReportName", acViewPreview, , "ID In(" & strIn & ")"

    Where would I need to place this code?

    For Each var in Me.ListboxName.ItemsSelected
        strIn = strIn & Me.ListboxName.ItemData(var) & ","
    Next

    Cheers, Kevin

    Hi Kevin. You can put it in the same place, but the code I posted is just a snippet. However, based on your previous code posts, I think you should be able to put the pieces together. Essentially, in the button event, you are constructing the In() clause from the choices selected from the listbox and then opening the report using the criteria you got from looping through the listbox. So, in essence, it sort of goes like this:

    1. Button clicked
    2. Loop through the listbox selections to construct the In() clause
    3. Open the report using the result of the In() clause as criteria in the WhereCondition argument

    Cheers!

    Thursday, April 18, 2019 5:19 PM
  • Sorry to be a pain. I added the report name and replace "ID" with the name of the field in the report utilizing my listbox selections.  When I click the button, I get the Enter Parameter Value dialog box for "txtAisle". How do I correct this? Cheers, Kevin

    Private Sub cmdSearch_Click()
        For Each VAR In Me.listAisle.ItemsSelected
            strIn = strIn & Me.listAisle.ItemData(VAR) & ","
        Next
        DoCmd.OpenReport "zzzTEST2rpt", acViewPreview, , "txtAisle In(" & strIn & ")"
    End Sub
    

    Thursday, April 18, 2019 5:44 PM
  • Hi DBguy,

    Any thoughts on my last reply?

    Cheers, Kevin

    Friday, April 19, 2019 12:44 PM
  • Sorry to be a pain. I added the report name and replace "ID" with the name of the field in the report utilizing my listbox selections.  When I click the button, I get the Enter Parameter Value dialog box for "txtAisle". How do I correct this? Cheers, Kevin

    Private Sub cmdSearch_Click()
        For Each VAR In Me.listAisle.ItemsSelected
            strIn = strIn & Me.listAisle.ItemData(VAR) & ","
        Next
        DoCmd.OpenReport "zzzTEST2rpt", acViewPreview, , "txtAisle In(" & strIn & ")"
    End Sub


    Is "txtAisle" the name of the field or the box on a form? You'll want to use the name of the field in the report's record source.
    Friday, April 19, 2019 4:46 PM
  • The fact that the column name is tagged 'txt' suggests that it is of text data type.  If so, each value in the value list will need to be wrapped in literal quotes characters when building the string expression in the For....Next loop.  In your code at present they are not.  BTW in addition to its primary purpose of accepting a parameter as the value list, one incidental advantage of the InParam function over the IN operator is that the data type is immaterial.  The values need not be wrapped in quotes, whether they are of text or number data type.


    Ken Sheridan, Stafford, England

    Friday, April 19, 2019 5:02 PM
  • My fault. That was a typo. The correct name of the field in the report's record source is Aisle. I made the necessary change (see below). Now when I click the button, the Enter Parameter Value dialog box appears with the selection I made in the listbox. I tried enclosing Aisle with quotes. No luck. What else could I be doing wrong?

    Private Sub cmdSearch_Click()
        For Each VAR In Me.listAisle.ItemsSelected
            strIn = strIn & Me.listAisle.ItemData(VAR) & ","
        Next
        DoCmd.OpenReport "zzzTEST2rpt", acViewPreview, , "Aisle In(" & strIn & ")"
    End Sub

    Friday, April 19, 2019 6:07 PM
  •  I tried enclosing Aisle with quotes.


    It's not the column name which should be wrapped in literal quotes characters if the column is of text data type, but the value.  So, is it of text data type or not?  If it is:

        strIn = strIn & """" & Me.listAisle.ItemData(VAR) & ""","

    BTW you don't seem to be requiring explicit declaration of variables.  Bad Idea!


    Ken Sheridan, Stafford, England

    • Marked as answer by KevinATF Monday, April 22, 2019 3:01 PM
    Friday, April 19, 2019 9:13 PM
  • BTW you don't seem to be requiring explicit declaration of variables.  Bad Idea! 

    Hi Ken. I'v had no formal training in VBA. I'm teaching myself as I go along. Please forgive me if I do not understand what you are talking about.

    Would you please explain your comment? What is a explicit declaration of variables?

    Saturday, April 20, 2019 1:24 AM
  • Ensuring that all variables are declared with the Dim statement (see my earlier post for examples) means that a compilation error will be generated if a variable name is misspelt in the code anywhere.  To ensure that this is done in all future modules select Tool | Options on the VBA menu bar, and in the Editor tab of the dialogue select the option to require declaration of all variables.

    In all existing modules, including forms' or reports' class modules, add the following line to the declarations area of each module:

    Option Explicit

    I's astonishing that Microsoft did not make this the default./

    Ken Sheridan, Stafford, England

    Saturday, April 20, 2019 9:26 AM
  • Hi Kevin. Did you try using the code Ken suggested above?

    strIn = strIn & """" & Me.listAisle.ItemData(VAR) & ""","

    Saturday, April 20, 2019 3:07 PM
  • My apologies for my late response. I was out of town for the holiday weekend. Ken, your suggestion worked beautifully. Thank you both for your help and patience. You both are excellent teachers.

    Cheers, Kevin

    Monday, April 22, 2019 3:01 PM
  • My apologies for my late response. I was out of town for the holiday weekend. Ken, your suggestion worked beautifully. Thank you both for your help and patience. You both are excellent teachers.

    Cheers, Kevin

    Hi Kevin. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
    Monday, April 22, 2019 3:48 PM