Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Multi select list box to generate query results

    Hello All! In need of your assistance Am attempting to build a list box which allows for multiple selections and to generate a query result based on those selections. Below is the starter code which I am attempting to modify but no luck thus far.

    Code:
    Private Sub cmd_HMA_RUN_Click()
    
    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
    
        Dim varItem As Variant
        Dim strWhere As String
        Dim strDescrip As String
        Dim lngLen As Long
        Dim strDelim As String
        Dim strDoc As String
        
        strDelim = """"
        strDoc = "HMA_CC"
    
    
        With Me.lst_HMA_PDC
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
    
                    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                End If
            Next
        End With
        
        lngLen = Len(strWhere) - 1
        If lngLen > 0 Then
            strWhere = "[PDCC7] IN (" & Left$(strWhere, lngLen) & ")"
            lngLen = Len(strDescrip) - 2
            If lngLen > 0 Then
                strDescrip = "PSPDAT_PSPCCHD.PDCCC7: " & Left$(strDescrip, lngLen)
            End If
        End If
        
        If CurrentProject.AllReports(strDoc).IsLoaded Then
            DoCmd.Close acReport, strDoc
        End If
        
        DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
        End If
        Resume Exit_Handler
    
    End Sub
    Here is the SQL which retrieves the necessary data from the query HMA_CC

    Code:
    SELECT PSPDAT_PSPCCHD.PDCCC7 AS PDC, PSPDAT_PSPCCHD.PARTC7 AS [Part Number], PSPDAT_PSPMAST.DESCPM AS [Part Name], PSPDAT_PSPCCHD.INVTC7 AS [System Qty], PSPDAT_PSPCCHD.CNT1C7 AS [Qty Count], PSPDAT_PSPCCHD.PLANC7 AS [Unit Price], PSPDAT_PSPCCHD.RCDTC7 AS [Date]
    FROM PSPDAT_PSPMAST INNER JOIN (PSPDAT_PSPCCHD INNER JOIN PSPDAT_PSPCCBS ON (PSPDAT_PSPCCHD.BSEQC7 = PSPDAT_PSPCCBS.BSEQC4) AND (PSPDAT_PSPCCHD.BCTHC7 = PSPDAT_PSPCCBS.BCTHC4) AND (PSPDAT_PSPCCHD.PDCCC7 = PSPDAT_PSPCCBS.PDCCC4)) ON PSPDAT_PSPMAST.PARTPM = PSPDAT_PSPCCHD.PARTC7
    WHERE (((PSPDAT_PSPCCBS.CTYPC4) In ('1','2','3','4')) AND ((PSPDAT_PSPCCHD.LTYPC7)="P") AND ((PSPDAT_PSPCCHD.VVALC7)<0))
    GROUP BY PSPDAT_PSPCCHD.PDCCC7, PSPDAT_PSPCCHD.PARTC7, PSPDAT_PSPMAST.DESCPM, PSPDAT_PSPCCHD.INVTC7, PSPDAT_PSPCCHD.CNT1C7, PSPDAT_PSPCCHD.PLANC7, PSPDAT_PSPCCHD.RCDTC7, PSPDAT_PSPCCHD.VVALC7
    HAVING (((PSPDAT_PSPCCHD.PDCCC7)=[Forms]![CC_SV]![HMA_PDC_CC]) AND ((PSPDAT_PSPCCHD.RCDTC7) Between [Forms]![CC_SV]![Start Date_CC] And [Forms]![CC_SV]![End Date_CC]))
    ORDER BY PSPDAT_PSPCCHD.RCDTC7;
    Am unsure how to modify the procedure code, state the Row Source in the list box properties, and where to indicate the number of columns in the query results (7) . Any assistance is greatly appreciated. Thanks!

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Not enough information to really help. Since we don't know or understand your db. If you already have the information selected, why do you then need to generate a qry? Don't you need to just simply view and or print? Explain more, Please!

  3. #3
    Join Date
    Jan 2013
    Posts
    53
    I have a simple form [Forms]![CC_SV] where the end user can generate data based on the criteria selected [Forms]![CC_SV]![HMA_PDC_CC]. There are up to 5 different selections to chose from. Currently the qry results are limited to 1 selection. I would like to give the end user the option of selecting multiple criteria from the multi list box [HMA_PDC_CC]. Hope this helps.

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    Under your Other tab for your listbox, does it show Simple under MultiSelect? If so, you should be able to select multiple selections and then run whatever function you want with that data selected. Here is an example of a multiselect.
    Private Sub cmdAddRecords_Click()

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant

    On Error GoTo ErrorHandler

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)

    'make sure a selection has been made
    If Me.ListProducts.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Item"
    Exit Sub
    End If

    If Not IsNumeric(Me.txtOtherValue) Then
    MsgBox "Please enter a quantity amount"
    Exit Sub
    End If

    'add selected value(s) to table
    Set ctl = Me.ListProducts
    For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!ListProducts = ctl.ItemData(varItem)
    rs!OtherValue = Me.txtOtherValue
    rs.Update
    Next varItem

    ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case Else
    MsgBox Err.Description
    DoCmd.Hourglass False
    Resume ExitHandler
    End Select

  5. #5
    Join Date
    Jan 2013
    Posts
    53
    Ok. What should be input in the Row Source and Row Source Type for the List Box?

  6. #6
    Join Date
    Nov 2011
    Posts
    413

    Row Source and Type

    Your Row Source will be your qry and criteria.The Row Source Type will be Table/Query
    Attached Thumbnails Attached Thumbnails RowSource.png  
    Last edited by Burrina; 07-23-13 at 15:32. Reason: Pic

  7. #7
    Join Date
    Jan 2013
    Posts
    53
    Private Sub cmdHMARUN_Click()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryHMA_CC_test")

    For Each varItem In Me!lstHMAPDC.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lstHMAPDC.ItemData(varItem) & "'"
    Next varItem

    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    strSQL = "SELECT PSPDAT_PSPCCHD.PDCCC7 AS PDC, PSPDAT_PSPCCHD.PARTC7 AS [Part Number], PSPDAT_PSPMAST.DESCPM AS [Part Name], PSPDAT_PSPCCHD.INVTC7 AS [System Qty], PSPDAT_PSPCCHD.CNT1C7 AS [Count], [CNT1C7]-[INVTC7] AS [Variance Qty], PSPDAT_PSPCCHD.PLANC7 AS [Cost_$], PSPDAT_PSPCCHD.VVALC7 AS [Variance_$], PSPDAT_PSPCCHD.RCDTC7 AS [Date] " & vbCrLf & _
    "FROM PSPDAT_PSPMAST INNER JOIN (PSPDAT_PSPCCHD INNER JOIN PSPDAT_PSPCCBS ON (PSPDAT_PSPCCHD.PDCCC7 = PSPDAT_PSPCCBS.PDCCC4) AND (PSPDAT_PSPCCHD.BCTHC7 = PSPDAT_PSPCCBS.BCTHC4) AND (PSPDAT_PSPCCHD.BSEQC7 = PSPDAT_PSPCCBS.BSEQC4)) ON PSPDAT_PSPMAST.PARTPM = PSPDAT_PSPCCHD.PARTC7 " & vbCrLf & _
    "WHERE ((((PSPDAT_PSPCCHD.PDCCC7) IN (" & strCriteria & "))) AND (((PSPDAT_PSPCCHD.LTYPC7)=""P""))) AND (((PSPDAT_PSPCCBS.CTYPC4) In ('1','2','3','4')))" & _
    "GROUP BY PSPDAT_PSPCCHD.PDCCC7, PSPDAT_PSPCCHD.PARTC7, PSPDAT_PSPMAST.DESCPM, PSPDAT_PSPCCHD.INVTC7, PSPDAT_PSPCCHD.CNT1C7, [CNT1C7]-[INVTC7], PSPDAT_PSPCCHD.PLANC7, PSPDAT_PSPCCHD.VVALC7, PSPDAT_PSPCCHD.RCDTC7 " & _
    "HAVING (((PSPDAT_PSPCCHD.RCDTC7) Between [Forms]![CC_test]![Start Date] And [Forms]![CC_test]![End Date]))" & _
    "ORDER BY PSPDAT_PSPCCHD.PDCCC7,PSPDAT_PSPCCHD.RCDTC7;"

    qdf.SQL = strSQL

    DoCmd.OpenQuery "qryHMA_CC_test"

    Set db = Nothing
    Set qdf = Nothing

    End Sub
    Final result, works like a charm. Thanks for your help.

  8. #8
    Join Date
    Nov 2011
    Posts
    413
    Your Welcome. Glad I could help. Good Luck With Your Project!

Posting Permissions

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