Results 1 to 13 of 13
  1. #1
    msk7777 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    14

    Multi Select Listbox Issue

    Hello everyone!



    I have been trying for two days now researching how to complete this but after trying to make adjustments to multiple examples found I can not get this to work.

    I have a form with a start date, end date and a multi select listbox that feeds a query (with no criteria). After reading up multi select listboxes I realized that I would have to have code to insert a WHERE statement to pass along the multiple listbox parameters to the query. Previously it was just a single-selection combo box but then the powers that be asked to be able to select multiple values and I had to change it to the multi select listbox.

    I found and adjusted this code:

    Private Sub DateOKButton_Click()
    On Error GoTo Err_DateOKButton_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant

    Set MyDB = CurrentDb()

    strSQL = "SELECT * FROM Claims"

    'Build the IN string by looping through the listbox
    For i = 0 To DentalPracticeNameListBx.ListCount - 1
    If DentalPracticeNameListBx.Selected(i) Then
    If DentalPracticeNameListBx.Column(0, i) = "All" Then
    flgSelectAll = True
    End If
    strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
    End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [DentalPracticeName] in " & _
    "(" & Left(strIN, Len(strIN) - 1) & ")"

    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
    strSQL = strSQL & strWhere
    End If

    MyDB.QueryDefs.Delete "ClaimsBatchSummaryQuery"
    Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)

    'Open the query, built using the IN clause to set the criteria
    DoCmd.OutputTo acOutputQuery, "ClaimsBatchSummaryQuery", acFormatXLS, _
    "\\cifs04\NetworkDevelopment\MS_Database\Reports\C laims Batch Summary\Claim Batch Summary Report_" & "'" & Forms!CBSParameterDateForm!DentalPracticeNameListB x & "_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
    'Clear listbox selection after running query
    For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
    Me.DentalPracticeNameListBx.Selected(varItem) = False
    Next varItem

    Exit_DateOKButton_Click:
    Exit Sub

    Err_DateOKButton_Click:

    If Err.Number = 5 Then
    MsgBox "You must make a selection(s) from the list" _
    , , "Selection Required !"
    Resume Exit_DateOKButton_Click
    Else
    'Write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_DateOKButton_Click
    End If

    End Sub



    This works fine for passing the list box selections as parameters but does not put the start and end date parameters in there. My VBA is not strong at all and could use some help adjusting this code to do the following:

    1. Pass both the date parameters (>=[Forms]![CBSParameterDateForm]![StateDateTxtBx] And <=[Forms]![CBSParameterDateForm]![EndDateTxtBx]) and the list box selections
    2. In the section of the code where I am exporting the query to excel, I need to save the list box values into the file name where the current code shows "Forms!CBSParameterDateForm!DentalPracticeNameList Bx".I am guessing I would need to separate each Listbox value (which is a Dental Practice name) with a "_". I wish I could get around this part but the powers that be asked for it.

    Any help with this would be greatly appreciated!

    msk7777

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Check each control and add to the where
    You cant use form boxes in a query if there's nothing in them..so..
    Test all controls for a possible filter then build the where clause.

    Code:
    dim qdf as querydef
    
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
        'remove 1st And
    sWhere= mid(sWhere,4)
    
        'save the sql as a qry or open the sql
    
    sSql = "SELECT * FROM tblCompany WHERE " & sWhere
    set qdf = currentdb.querdefs("qsResults")
    qdf.sql = ssql
    qdf.close
    docmd.openquery qdf.name

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not seeing any code that concatenates the date range parameters into the WHERE clause. If the date range parameters are optional, try:

    strWhere = "WHERE [DateFieldName] BETWEEN #" & Nz(Me.StartDateTxtBox, "1/1/1900") & "# AND #" & Nz(Me.EndDateTxtBox, "12/31/2900") & "# "

    Then follow with the listbox code to build the IN criteria and concatenate to the previous. Be sure not to repeat the WHERE keyword.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    msk7777 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    14
    Thank you so much for your help on this and the quick replies. I hadn't placed anything for the date ranges in the code due to not having a clue how to incorporate it into the code I borrowed from another posting. Also, the date range parameters are required fields. I wouldn't want to give them an option of leaving them empty.

    I have tried adding your code to the current strWhere line but I am not doing so well. Would you mind showing me how you would combine the date range parameters with the listbox parameter? Sorry to be a pain, but again, I am no VBA pro and mainly copy and make small changes to other people's codes. A copycat really, ha-ha. Oh and the table's DateFieldName is [InvoiceDate].

    Thanks again for your help!

    Quote Originally Posted by June7 View Post
    Not seeing any code that concatenates the date range parameters into the WHERE clause. If the date range parameters are optional, try:

    strWhere = "WHERE [DateFieldName] BETWEEN #" & Nz(Me.StartDateTxtBox, "1/1/1900") & "# AND #" & Nz(Me.EndDateTxtBox, "12/31/2900") & "# "

    Then follow with the listbox code to build the IN criteria and concatenate to the previous. Be sure not to repeat the WHERE keyword.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you don't want the date parameters to be optional (BOTH are required?) then don't use my suggestion with the Nz() function.

    Follow ranman's approach. Review Allen Browne's code http://allenbrowne.com/ser-50.html and http://www.allenbrowne.com/ser-62.html

    Have code verify both date parameters are provided and only run if true. Something like:

    Code:
    If Not IsNull(Me.StartDateTxtBox) And Not IsNull(Me.EndDateTxtBox) = True Then
        strWhere = "WHERE [DateFieldName] BETWEEN #" & Me.StartDateTxtBox & "# AND #" & Me.EndDateTxtBox & "# "
        With Me.DentalPracticeNameListBx
            If .Value <> "All" Then
                For Each varItem In .ItemsSelected
                    If Not IsNull(varItem) Then
                        'Build up the filter from the bound column (hidden).
                        strIN = strIN & strDelim & .ItemData(varItem) & strDelim & ","
                        'Build up the description from the text in the visible column. See note 2.
                        strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                    End If
                Next
                strIN = "AND [DentalPracticeName] IN(" & Left(strIN, Len(strIN) - 1) & ")"
            End If
        End With
        strWhere = strWhere & strIN
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    msk7777 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    14
    Thanks a ton! I got the code in and think I am almost there but for some reason I keep getting a "Syntax error in FROM Clause" message.

    Here is the query:

    SELECT Claims.CIInvoiceNumber AS InvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal
    FROM Claims
    GROUP BY Claims.CIInvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal;


    And the full code:

    Private Sub DateOKButton_Click()
    On Error GoTo Err_DateOKButton_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    Set MyDB = CurrentDb()
    strSQL = "SELECT * FROM Claims"
    'Build the IN string by looping through the listbox
    For i = 0 To DentalPracticeNameListBx.ListCount - 1
    If DentalPracticeNameListBx.Selected(i) Then
    If DentalPracticeNameListBx.Column(0, i) = "All" Then
    flgSelectAll = True
    End If
    strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
    End If
    Next i
    'Create the WHERE string, and strip off the last comma of the IN string
    If Not IsNull(Me.StartDateTxtBx) And Not IsNull(Me.EndDateTxtBx) = True Then
    strWhere = "WHERE [InvoiceDate] BETWEEN #" & Me.StartDateTxtBx & "# AND #" & Me.EndDateTxtBx & "# "
    With Me.DentalPracticeNameListBx
    If .Value <> "All" Then
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    'Build up the filter from the bound column (hidden).
    strIN = strIN & strDelim & .ItemData(varItem) & strDelim & ","
    'Build up the description from the text in the visible column. See note 2.
    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
    End If
    Next
    strIN = Left(strIN, Len(strIN) - 1) & ")"
    End If
    End With
    strWhere = strWhere & strIN
    End If
    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
    strSQL = strSQL & strWhere
    End If
    MyDB.QueryDefs.Delete "ClaimsBatchSummaryQuery"
    Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OutputTo acOutputQuery, "ClaimsBatchSummaryQuery", acFormatXLS, _
    "\\cifs04\NetworkDevelopment\MS_Database\Reports\Cl aims Batch Summary\Claim Batch Summary Report_" & "'" & Forms!CBSParameterDateForm!DentalPracticeNameListB ox & "_" & Format(Date, "dd-mmm-yyyy") & ".xls", True

    'Clear listbox selection after running query
    For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
    Me.DentalPracticeNameListBx.Selected(varItem) = False
    Next varItem
    Exit_DateOKButton_Click:
    Exit Sub
    Err_DateOKButton_Click:
    If Err.Number = 5 Then
    MsgBox "You must make a selection(s) from the list" _
    , , "Selection Required !"
    Resume Exit_DateOKButton_Click
    Else
    'Write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_DateOKButton_Click
    End If
    End Sub



    Do you see anything out of place to cause this error that I am missing?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That query is for what? There is no filter criteria.

    Please post code between CODE tags to retain indentation so will be more readable.

    Learn debugging techniques. Review link at bottom of my post for debugging guidelines.

    Add a Debug.Print line in code to send the constructed string to the VBA immediate window.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You have this for your sql

    strSQL = "SELECT * FROM Claims"

    And then you add your where criteria. This is the reason for the error you are getting. I think there are other issues too.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Need a space between Claims and WHERE so words don't run together.

    "SELECT * FROM Claims "
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    msk7777 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    14
    Thank you so much! And terribly sorry about not placing tags on the code. I fixed what you both were talking about and I am reposting the code with tags. It's still not quite working but I feel its getting closer.

    Also. Please remember I took this code from a posting I found. If you have a better way to pass the parameters from the form to the query I am open to it. My problem right now is this sort of VBA is beyond my understanding and why I need help from you pros.

    Code:
    Private Sub DateOKButton_Click()
     On Error GoTo Err_DateOKButton_Click
     Dim MyDB As DAO.Database
     Dim qdef As DAO.QueryDef
     Dim i As Integer
     Dim strSQL As String
     Dim strWhere As String
     Dim strIN As String
     Dim flgSelectAll As Boolean
     Dim varItem As Variant
     Set MyDB = CurrentDb()
     strSQL = "SELECT * FROM Claims "
     'Build the IN string by looping through the listbox
     For i = 0 To DentalPracticeNameListBx.ListCount - 1
     If DentalPracticeNameListBx.Selected(i) Then
     If DentalPracticeNameListBx.Column(0, i) = "All" Then
     flgSelectAll = True
     End If
     strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
     End If
     Next i
     'Create the WHERE string, and strip off the last comma of the IN string
     If Not IsNull(Me.StartDateTxtBx) And Not IsNull(Me.EndDateTxtBx) = True Then
        strWhere = "WHERE [InvoiceDate] BETWEEN #" & Me.StartDateTxtBx & "# AND #" & Me.EndDateTxtBx & "# "
        With Me.DentalPracticeNameListBx
            If .Value <> "All" Then
                For Each varItem In .ItemsSelected
                    If Not IsNull(varItem) Then
                        'Build up the filter from the bound column (hidden).
                        strIN = strIN & strDelim & .ItemData(varItem) & strDelim & ","
                        'Build up the description from the text in the visible column. See note 2.
                        strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                    End If
                Next
                strIN = Left(strIN, Len(strIN) - 1) & ")"
            End If
        End With
        strWhere = strWhere & strIN
    End If
     'If "All" was selected in the listbox, don't add the WHERE condition
     If Not flgSelectAll Then
     strSQL = strSQL & strWhere
     End If
     MyDB.QueryDefs.Delete "ClaimsBatchSummaryQuery"
     Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)
     'Open the query, built using the IN clause to set the criteria
     DoCmd.OpenQuery "ClaimsBatchSummaryQuery"
     'Clear listbox selection after running query
     For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
     Me.DentalPracticeNameListBx.Selected(varItem) = False
     Next varItem
    Exit_DateOKButton_Click:
     Exit Sub
    Err_DateOKButton_Click:
     If Err.Number = 5 Then
     MsgBox "You must make a selection(s) from the list" _
     , , "Selection Required !"
     Resume Exit_DateOKButton_Click
     Else
     'Write out the error and exit the sub
     MsgBox Err.Description
     Resume Exit_DateOKButton_Click
     End If
     End Sub
    And this is supposed to feed this query. And to answer your previous statement, I read I needed to remove the criteria from the query and let the VBA code place the criteria in the query. If that is incorrect then please let me know.
    Code:
    SELECT Claims.CIInvoiceNumber AS InvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal
    FROM Claims
    GROUP BY Claims.CIInvoiceNumber, Claims.InvoiceDate, Claims.PracticeReferenceNumber, Claims.PractitionerID, Claims.DOB, Claims.PractitionerName, Claims.DentalPracticeName, Claims.PolicyNumber, Claims.MemberFirstName, Claims.MemberLastName, Claims.PaymentType, Claims.NonDiscountedAmountTotal, Claims.DiscountedAmountTotal, Claims.MemberCollectedAmountTotal, Claims.ReimbursedAmountTotal, Claims.MSAdminFeeTotal;

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The VBA code isn't just placing criteria in the query - it is CREATING the query object. Why are you using code that creates then deletes query object? I have only done this once because of requirement to allow users to export custom query.

    Why are you using a GROUP BY query? There are no aggregate calcs. Have you considered instead building a report that uses Grouping & Sorting features then apply filter criteria to the report as demonstrated in the links I referenced?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    msk7777 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Posts
    14
    I must embarrassingly say that I didn't know it was doing that. Which would explain why when the query didn't work it would be deleted and I would have to replace it from a backup. As I stated, this sort of VBA is a bit beyond my skills.

    The point of this is just to pull the data the user needs into a query then export it to an excel document that gets sent to a client.

    I reviewed those links you sent on Friday but didn't get too far into them because I didn't want to create a report. However, I think I get now where you are going with it so I will give it a try.

    Quote Originally Posted by June7 View Post
    The VBA code isn't just placing criteria in the query - it is CREATING the query object. Why are you using code that creates then deletes query object? I have only done this once because of requirement to allow users to export custom query.

    Why are you using a GROUP BY query? There are no aggregate calcs. Have you considered instead building a report that uses Grouping & Sorting features then apply filter criteria to the report as demonstrated in the links I referenced?

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am going to be pretty busy today. But I would add... it is OK to create and then delete the query object. However, for you, I would have a query object with a special name. Maybe something like vba_ . So your name might be, vba_qryName. With this object, I would get the query working the way you need it, without the where criteria.

    Test the query. When you are happy with that, don't use VBA to delete it. Use VBA to grab the SQL, add the WHERE, and change the Query Object's SQL. Afterwards, change the Query Object's SQL back to how it was. The reason this approach may be better than deleting is... you do not have to translate the SQL within the object into a VBA string (deleting a query object will require the entire SQL be in a VBA string). If it is a complex Query, leave it in the Query Object and retrieve it when needed. Store it in a string variable when you need it. Use the same string variable to rebuild it when done.

    There are other approaches but I have used this for complex queries that only need a couple changes.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi-Select Listbox
    By RayMilhon in forum Forms
    Replies: 5
    Last Post: 03-04-2014, 11:54 AM
  2. pbaldy - Multi-Select Listbox
    By jasbrown in forum Forms
    Replies: 9
    Last Post: 01-31-2013, 11:22 AM
  3. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  4. Looping through a multi select listbox - how do I do it?
    By shabbaranks in forum Programming
    Replies: 4
    Last Post: 04-02-2012, 11:56 AM
  5. Need Multi-Select listbox code example
    By Buakaw in forum Forms
    Replies: 4
    Last Post: 08-21-2011, 08:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums