Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Cool Unanswered: Multi-Column List Box – Select Multiple Items

    Access 2003 – Multi-Column List Box – Select Multiple Items

    I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended).

    I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the items in the list box and selects those fields to populate the list box.

    My table has about 200 fields and the three fields I need are like number 32, 4, and 132 in the table. Can someone help me modify the code to get the fields I need? Here’s my code:

    Private Sub SelectedContract_Click()
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_OptimizeIt3")
    ' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!List10.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
    Next varItem
    ' Check that user selected something
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    ' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _
    "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the query
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    End Sub


    Krazy (Bill) Kasper

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If I understand you correctly, you needs fields 32, 4, and 132 of the table. If that's so, change your SQL from

    strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _

    to

    strSQL = "SELECT NamesOfTheThreeFieldsSeperatedByCommasGoHere FROM dbo_OptimizeIt1 " & _

    Sam

  3. #3
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Access 2003 - List Box - Multiple Select

    Thanks, that gets me closer. I am now selecting the right fields but I have two problems:
    1. The List Box only populates with one set of records (instead of several hundred) and it is repeated three times.
    2. All of the fields in my report now show as "#Error".

    Following is the revised code:
    Code:
    Private Sub SelectedContract_Click()
    ' Declare variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
    ' Get the database and stored query
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("qry_OptimizeIt3")
    ' Loop through the selected items in the list box and build a text string
        For Each varItem In Me!List10.ItemsSelected
            strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
        Next varItem
    ' Check that user selected something
        If Len(strCriteria) = 0 Then
            MsgBox "You did not select anything from the list" _
                , vbExclamation, "Nothing to find!"
            Exit Sub
        End If
    ' Remove the leading comma from the string
        strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
        strSQL = "SELECT LeaseMasterContractId, SoldToCustomerName, OrderRepName FROM dbo_OptimizeIt1 " & _
                 "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
    ' Apply the new SQL statement to the query
        qdf.SQL = strSQL
    ' Open the report
        DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
        ' Empty the memory
        Set db = Nothing
        Set qdf = Nothing
        ' strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
    End Sub
    Krazy (Bill) Kasper
    Last edited by gvee; 08-11-08 at 17:34. Reason: [CODE[ tags added for clarity

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What is the RecordSource property for rpt_OptimizeItReport1?

    Sam

  5. #5
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Access 2003 - Select Multiple Items from Multi-Column List Box

    It's a query named qry_OptimizeIt1
    The query links to two tables housed in a Data Warehouse via ODBC.
    The tables are named dbo_OptimizeIt1 and dbo_OptimizeIt_Audit.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    ' Open the query
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
    How does the DoCmd statement open the query? The report's RecordSource is a different query, and the filter doesn't mention it either.

    I also understand that strFilter is a filter built into the report's RecordSource somehow. But if that's so, the whole clause needs quotes, as in

    "strFilter = [LeaseMasterContractId] = '" & Me.List10 & "'"

    Also, the above code will select only one item from List10. I think that what you really mean to say is

    Code:
    "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('" & strCriteria & ")'"
    (although my single quotes may need to be played with.)

    However, in that case, you don't need qry_OptimizeIt3 at all, as you're building the criteria right into the report.

    The truth is, though, that while theoretically this approach works, it often takes a long time to run it. What you should think of doing, perhaps, is opening the criteria string in an an action query, building a temporary table. Then open the report using the temp table as a "table" in the report's RecordSource. It takes much less time that way.

    Hope all this helps,
    Sam

  7. #7
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Smile Thanks Sam - Almost there

    Sam,
    Thanks for your input. I modified my code and now get all records. However, I'm getting the first three fields in the database (there are several hundred). Can you tell me how I can select the specific fields I want in the List Box?
    Thanks,
    Krazy (Bill) Kasper

    p.s. Here's my latest code:

    Private Sub SelectedContract_Click()
    ' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database
    Set db = CurrentDb()
    ' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!List10.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
    Next varItem
    ' Check that user selected something
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    ' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _
    "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the report
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('" & Me.List10 & ")'"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    End Sub

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I think it's because you want back to

    strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _

    instead of keeping

    strSQL = "SELECT LeaseMasterContractId, SoldToCustomerName, OrderRepName FROM dbo_OptimizeIt1 " & _

    How did that change get reversed?

    Sam

  9. #9
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Sam - I'm getting a compile error

    Sam,

    Not sure how it happened. I changed it back to strSQL = "SELECT LeaseMasterContractId, SoldToCustomerName, OrderRepName FROM dbo_OptimizeIt1 " & _, however now I get a "run-time error 424 - Object Required". It doesn't seem to like the statement qdf.SQL = strSQL Following is the most current rendition of the code:

    Private Sub SelectedContract_Click()
    ' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database
    Set db = CurrentDb()
    ' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!List10.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
    Next varItem
    ' Check that user selected something
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    ' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT LeaseMasterContractId, SoldToCustomerName, OrderRepName FROM dbo_OptimizeIt1 " & _
    "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the report
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('" & Me.List10 & ")'"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    End Sub

    Krazy (Bill) Kasper
    Last edited by KrazyKasper; 08-22-08 at 11:10.

  10. #10
    Join Date
    Dec 2002
    Location
    Prιverenges, Switzerland
    Posts
    3,740
    maybe i'm missing something, but where are the following lines:
    dim qdf as DAO.querydef
    set qdf = currentdb.querydefs("whateverItIsCalled")

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I think the reason for the compile error is that you've somehow removed the single quotes in the WHERE clause:

    "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"

    I think if you reinstate them you have a good chance.

    Sam

  12. #12
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Multi-Column List Box - Select Multiple Items

    I "copied" your line which included the quotes. Didn't help.
    Here's the latest error message followed by the code:

    Syntax error (Missing Operator) in query expression 'strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('200-5011461-000)')'.

    The "number 200-5011461-000 is the first field in the list box.
    Clicking on Debug I get the following string highlighted:

    Report "rpt_OptimizeItReport1", acViewPreview, , "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('" & Me.List10 & ")'"

    You mentioned earlier I may have to "play" with the quotes. I tried several variations but no luck. Appreciate your latest input.

    THE CODE
    Private Sub SelectedContract_Click()
    ' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    ' Get the database
    Set db = CurrentDb()
    Set qdf = CurrentDb.QueryDefs("qry_OptimizeIt3")
    ' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!List10.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
    Next varItem
    ' Check that user selected something
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    ' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT LeaseMasterContractId, SoldToCustomerName, OrderRepName FROM dbo_OptimizeIt1 " & _
    "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the report
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN('" & Me.List10 & ")'"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    End Sub


    Krazy (Bill) Kasper

  13. #13
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quoting myself from thread #6,

    "strFilter = [LeaseMasterContractId] = '" & Me.List10 & "'"

    Also, the above code will select only one item from List10. I think that what you really mean to say is
    "strFilter = 'dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ")'"
    (although my single quotes may need to be played with.) (I played with them just now. They might be correct now.)

    As I see from your code, you never did make that change. Try it. You might like it.

    Sam

    ps. You did change the strSQL; what you have to change is the same clause in the OpenReport method.
    Last edited by Sam Landy; 08-26-08 at 13:40.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •