Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    AfterUpdate Select ALL option?

    Hello,
    I am trying to provide a Select All option AFTER a State has been selected.

    On load it lists all the cities and an option to Select ALL from there BUT not sure how to AFTER a State has been selected.



    Please see attached.
    CascadingStates.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    2 Option btns. All or 1. The 1 would enable a combo to pick 1 city.
    a query, q1 would use this combo.

    for ALL option, Q2, would ignore the city combo and just pull all cities using the State combo.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Hi ranman256,
    I think I have the ALL option figured out.

    However, I'm not quite sure how to add / incorporate the selected cities to this code from the lstCities?

    Code:
    Private Sub MemberEmailList_Click()
    On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim strWhere As String, strIN As String
        Dim flgAll As Boolean
        
        Set myDB = CurrentDb()
        
        strSQL = "SELECT * FROM MemberGroups"
          
        'create the WHERE string, stripping off the last comma of the IN string
        strWhere = " WHERE [Member Group]in (" & Left(strIN, Len(strIN) - 1) & ")"
        
        'if "All" was selected, don't add the WHERE condition
        If Not flgAll Then
            strSQL = strSQL & strWhere
        End If
        
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
        
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
        
    Exit_MemberEmailList_Click:
        Exit Sub
        
    Err_MemberEmailList_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        ElseIf Err.Number = 5 Then
            MsgBox "You must select at least one Member Group"
            Resume Exit_MemberEmailList_Click
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_MemberEmailList_Click
        End If
    
    End Sub
    Please see attached updated db
    CascadingStates.zip

  4. #4
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok, the ALL option AFTER the State is selected still needs work.

    And anyone know how to have multiple multi-selections (see code or attached above)

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First off, I would advise using only letters and numbers and/or the underscore in object names. NO spaces, punctuation or special characters.



    Change the code for cboStateCode after update event to:
    Code:
    Private Sub cboStateCode_AfterUpdate()
        ' Filter the list of Cities based on the selected State.
        Dim strRS As String
    
        strRS = "SELECT qryCitiesAndStates.City, qryCitiesAndStates.State, qryCitiesAndStates.StateCode FROM qryCitiesAndStates"
        strRS = strRS & " WHERE StateCode = '" & Me.cboStateCode & "'"
        strRS = strRS & " UNION  SELECT 'ALL CITIES' as City, '" & Me.cboStateCode.Column(1) & "' as State, ' ' as StateCode  FROM qryCitiesAndStates"
        strRS = strRS & " ORDER BY qryCitiesAndStates.StateCode, qryCitiesAndStates.City"
        '    Debug.Print strRS
        Me.lstCities.RowSource = strRS
    
        Me.lstCities.Requery
    
    End Sub
    Then see:
    Use a multi-select list box to filter a report
    http://www.allenbrowne.com/ser-50.html



    For the button "MemberEmailList_Click", you will want to first check the list box "lstCities" for "All Cities".
    If "lstCities" is not "All Cities", then you can loop through the list box "lstCities" to create the WHERE clause.

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Thank you! I'll try the updated code changes.

    NOTE: on the link, ok, so not sure if you saw, but there's already code for the multi-select.
    I'm trying to figure out how to do multiple multi-select using the existing multi-select code.
    Suggestions?

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok thank you on the showing ALL Cities as an option in the lstCities after a State has been selected!

    Now how to add another multi-select to the current multi-select code.

    The user selects:
    1. Member Group(s) (see attached), one or two or all of them THEN
    2. State or ALL States, if a State is selected THEN
    3. Cities selected or the ALL Cities

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by aellistechsupport View Post
    <snip>

    NOTE: on the link, ok, so not sure if you saw, but there's already code for the multi-select. <snip>
    In the dB attached to Post#3, there is no code for the multi-select.


    Quote Originally Posted by aellistechsupport View Post
    1. Member Group(s) (see attached), one or two or all of them THEN
    Nothing attached.... (Post #7)

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Please see post #3. At the end of the post is the attachment.

    Thank you

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yep, downloaded it again and still no code to handle creating criteria from multi-select list box.

  11. #11
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    ?
    How strange

    you're not seeing the last module?
    here it is updated with the ALL correction: CascadingStates.zip

    This is the original I think
    Code:
    Private Sub MemberEmailList_Click()
    On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim strWhere As String, strIN As String
        Dim flgAll As Boolean
        
        Set myDB = CurrentDb()
        
        strSQL = "SELECT * FROM MemberGroups"
        strSQL = "SELECT * FROM qryCitiesAndStates"
          
        'create the WHERE string, stripping off the last comma of the IN string
        strWhere = " WHERE [Member Group]in (" & Left(strIN, Len(strIN) - 1) & ")"
        strWhere = " WHERE [City] in (" & Left(strIN, Len(strIN) - 1) & ")"
        
        'if "All" is selected, don't add the WHERE condition
        If Not flgAll Then
            strSQL = strSQL & strWhere
        End If
        
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
        myDB.QueryDefs.Delete "Cities"
        Set qdf = myDB.CreateQueryDef("Cities", strSQL)
        
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
        
    Exit_MemberEmailList_Click:
        Exit Sub
        
    Err_MemberEmailList_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        ElseIf Err.Number = 5 Then
            MsgBox "You must select at least one Member Group and/or City"
            Resume Exit_MemberEmailList_Click
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_MemberEmailList_Click
        End If
    
    End Sub
    I edited in the City part BUT that's not right.

    Code:
    Private Sub MemberEmailList_Click()
    On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim i As Integer
        Dim strWhere As String, strIN As String, strSQL As String
        Dim flgAll As Boolean
        
        Set myDB = CurrentDb()
        
        strSQL = "SELECT * FROM MemberGroups"
         
            'create the IN string by looping thru the listbox
        For i = 0 To MemberGroupSelector.ListCount - 1
            If MemberGroupSelector.Selected(i) Then
                If MemberGroupSelector.Column(0, i) = " All" Then
                    flgAll = True
                End If
                strIN = strIN & "'" & MemberGroupSelector.Column(0, i) & "',"
            End If
         Next i
            
        'create the WHERE string, stripping off the last comma of the IN string
        strWhere = " WHERE [Member Group]in (" & Left(strIN, Len(strIN) - 1) & ")"
        
        'if "All" is selected, don't add the WHERE condition
        If Not flgAll Then
            strSQL = strSQL & strWhere
        End If
        
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
        
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
        
    Exit_MemberEmailList_Click:
        Exit Sub
        
    Err_MemberEmailList_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        ElseIf Err.Number = 5 Then
            MsgBox "You must select at least one Member Group and/or City"
            Resume Exit_MemberEmailList_Click
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_MemberEmailList_Click
        End If
    
    End Sub

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There is only one form module. and no standard modules.
    There is a procedure named "MemberEmailList_Click", but there are errors and won't execute.

    Comments inline:
    Code:
    Private Sub MemberEmailList_Click()
        On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim strWhere As String, strIN As String
        Dim flgAll As Boolean
    
        Set myDB = CurrentDb()
    
        strSQL = "SELECT * FROM MemberGroups"      '<-- "strSQL" has not been declared
        strSQL = "SELECT * FROM qryCitiesAndStates"
    
    'The two strSQL statements above would ALWAYS result in...   strSQL = "SELECT * FROM qryCitiesAndStates"
    
        'create the WHERE string, stripping off the last comma of the IN string
        strWhere = " WHERE [Member Group]in (" & Left(strIN, Len(strIN) - 1) & ")"
        strWhere = " WHERE [City] in (" & Left(strIN, Len(strIN) - 1) & ")"
    ' "strIN" has been declared, but is ALWAYS a NULL/Empty string. Nowhere is the value set
    
    'The two strWhere statements above would ALWAYS result in...  strWhere = " WHERE [City] in (" & Left(strIN, Len(strIN) - 1) & ")"
    
        'if "All" is selected, don't add the WHERE condition
        If Not flgAll Then         ' <-- "flgAll" is ALWAYS False. Nowhere is the value set
            strSQL = strSQL & strWhere
        End If
    
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
        myDB.QueryDefs.Delete "Cities"
        Set qdf = myDB.CreateQueryDef("Cities", strSQL)
    
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    
    Exit_MemberEmailList_Click:
        Exit Sub
    
    Err_MemberEmailList_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        ElseIf Err.Number = 5 Then
            MsgBox "You must select at least one Member Group and/or City"
            Resume Exit_MemberEmailList_Click
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_MemberEmailList_Click
        End If
    
    End Sub
    Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.





    --------------------------------------------------------
    Also, you should ensure the top two lines of every module are:
    Option Compare Database
    Option Explicit

  13. #13
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Hi,
    I attached an updated one with your ALL Cities correction added in post #11

    Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.
    That's where I was trying to add the WHERE clause under the Member Group WHERE clause and it doesn't work.

    Anyway, in the current attachment, it works with just the Member Group (NOTE: the export file location you'll have to change to your local directory).

    I don't know how to add the selected or the ALL from the lstCities that procedure.
    So how and what needs to be changed to look at and use both list box selections?

    Thank you

  14. #14
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by ssanfu View Post
    There is only one form module. and no standard modules.
    There is a procedure named "MemberEmailList_Click", but there are errors and won't execute.

    Comments inline:
    Code:
    Private Sub MemberEmailList_Click()
        On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim strWhere As String, strIN As String
        Dim flgAll As Boolean
    
        Set myDB = CurrentDb()
    
        strSQL = "SELECT * FROM MemberGroups"      '<-- "strSQL" has not been declared
        strSQL = "SELECT * FROM qryCitiesAndStates"
    
    'The two strSQL statements above would ALWAYS result in...   strSQL = "SELECT * FROM qryCitiesAndStates"
    
        'create the WHERE string, stripping off the last comma of the IN string
        strWhere = " WHERE [Member Group]in (" & Left(strIN, Len(strIN) - 1) & ")"
        strWhere = " WHERE [City] in (" & Left(strIN, Len(strIN) - 1) & ")"
    ' "strIN" has been declared, but is ALWAYS a NULL/Empty string. Nowhere is the value set
    
    'The two strWhere statements above would ALWAYS result in...  strWhere = " WHERE [City] in (" & Left(strIN, Len(strIN) - 1) & ")"
    
        'if "All" is selected, don't add the WHERE condition
        If Not flgAll Then         ' <-- "flgAll" is ALWAYS False. Nowhere is the value set
            strSQL = strSQL & strWhere
        End If
    
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
        myDB.QueryDefs.Delete "Cities"
        Set qdf = myDB.CreateQueryDef("Cities", strSQL)
    
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    
    Exit_MemberEmailList_Click:
        Exit Sub
    
    Err_MemberEmailList_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        ElseIf Err.Number = 5 Then
            MsgBox "You must select at least one Member Group and/or City"
            Resume Exit_MemberEmailList_Click
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_MemberEmailList_Click
        End If
    
    End Sub
    Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.





    --------------------------------------------------------
    Also, you should ensure the top two lines of every module are:
    Option Compare Database
    Option Explicit

    How do you incorporate multiple list box selections then?

  15. #15
    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 are selecting cities, why does the code refer to [Member Group]? And if you want to export members, shouldn't this pull records from Members table?

    The following works for me:
    Code:
        Set myDB = CurrentDb()
         
        'create the IN string by looping thru the listbox
        For i = 0 To lstCities.ListCount - 1
            If lstCities.Selected(i) Then
                If lstCities.Column(0, i) Like " All*" Then
                    Exit For
                End If
                strIN = strIN & "'" & lstCities.Column(0, i) & "',"
            End If
         Next i
            
        'create the WHERE string, stripping off the last comma of the IN string
        If strIN <> "" Then strWhere = " WHERE [City] IN (" & Left(strIN, Len(strIN) - 1) & ")"
        
        strSQL = "SELECT * FROM Members " & strWhere
        
        myDB.QueryDefs.Delete "MemberEmailList"
        Set qdf = myDB.CreateQueryDef("MemberEmailList", strSQL)
        
        DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Member Services\MembershipLists\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    You want to use more than one multi-select listbox to build filter criteria? That would be two looping structures and concatenate with an " AND " operator.
    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.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to add select all option in combo box
    By Vaibhav2015 in forum Forms
    Replies: 6
    Last Post: 09-30-2015, 10:09 AM
  2. Using Option Box to Select Criteria
    By HelpDesk in forum Queries
    Replies: 1
    Last Post: 07-17-2015, 10:41 AM
  3. Replies: 11
    Last Post: 01-25-2012, 09:46 AM
  4. Replies: 6
    Last Post: 12-30-2011, 08:09 PM
  5. Replies: 1
    Last Post: 05-05-2011, 09:21 AM

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