Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 44
  1. #16
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by June7 View Post
    You want to use more than one multi-select listbox?
    Yes, currently it works with the list box for Member Groups (multiple selections)
    Need to also include multiple selection from the Cities list box or ALL Cities from the selection of a State or ALL States

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did some edits on my previous post. Might review again.
    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.

  3. #18
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by June7 View Post
    Did some edits on my previous post. Might review again.
    What post and edits?

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That would be Post #15 - expanded comment about multiple listboxes and some questions at beginning.
    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.

  5. #20
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, you must have missed that I need multiple multi-select.
    Starting with the Member Group.

    then a State or ALL States (selected)
    or if a State is selected then the cities selected or the ALL Cities (either way it's a selection)

    According to Post #15, all it has is the Member Group code with the Cities NOT together.
    And last statement which doesn't help because I do not know how to include it to do all three selections running through a loop.

    That's the question HOW and just saying include an "AND" doesn't help me. It might someone else but I don't know how to add these two additional selection options in that code.

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build on what you have learned. Basically, modify the procedure to have a looping construct for each listbox and conditional statements for concatenation, like:

    Build looping construct for groups as strGroups.
    Then trim strGroups to strip last comma (don't include the WHERE keyword)
    If strGroups <> "" Then strGroups = "[MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"

    Build looping construct for cities as strCities.
    Then trim strCities to strip last comma (don't include the WHERE keyword)
    If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"

    Conditional code that concatenates criteria strings:
    strWHERE = strGroups & IIf(strGroups <> "" AND strCities <> "", " AND ", "") & strCities

    More conditional code for concatentation of SQL statement
    strSQL = "SELECT * FROM Members " & IIf(strWHERE <> "", " WHERE ", "") & strWHERE
    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.

  7. #22
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, I certainly do not know this enough to make it actually work.
    I tried this and it's not correct
    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" and strSQL "SELECT * FROM Cities"
         
            'create the IN string by looping thru the listbox
        For i = 0 To MemberGroupSelector.ListCount - 1 And lstities.ListCount - 1
            If MemberGroupSelector.Selected(i) Then
                If MemberGroupSelector.Column(0, i) = " All" Then
                    flgAll = True
            If lstCities.Selected(i) Then
                If lstCities.Column(0, i) = " All" Then
                    flgAll = True
                End If
                strIN = strIN & "'" & MemberGroupSelector.Column(0, i) & "'," And strIN = strIN & "'" & lstities.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" And myDB.QueryDefs.Delete "City"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL) And qdf = myDB.CreateQueryDef("City", 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

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Has to be two complete looping structures for building two IN clauses.

    Code:
         For i = 0 To MemberGroupSelector.ListCount - 1
            If MemberGroupSelector.Selected(i) Then
                If MemberGroupSelector.Column(0, i) LIKE " All*" Then
                    Exit For
                End If
                strGroups = strGroups & "'" & MemberGroupSelector.Column(0, i) & "',"
            End If
         Next i
         If strGroups <> "" Then strGroups = "[MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
    
         For i = 0 To lstCities.ListCount - 1
            If lstCities.Selected(i) Then
                If lstCities.Column(0, i) LIKE " All*" Then
                    Exit For
                End If
                strCities = strCities & "'" & lstCities.Column(0, i) & "',"
            End If
         Next i
         If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
    
        'Don't you want to pull records from Members table filtered by the Group and City criteria?
    
         strWHERE = strGroups & IIf(strGroups <> "" AND strCities <> "", " AND ", "") & strCities
    
         strSQL = "SELECT * FROM Members " & IIf(strWHERE <> "", " WHERE ", "") & strWHERE
    Last edited by June7; 12-11-2015 at 11:11 AM.
    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.

  9. #24
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    June7,
    I think you missed it again, it's listed a few times now.

    GOAL:
    to allow the user to select multiple selections from multiple list boxes including the combobox.

    1st - select one or more Member Group, which the procedure allows
    2nd - then to select a State or ALL States AFTER Member Group(s) selected
    3rd - then to select a City or multiple cities or ALL Cities

    As mentioned, I can't figure out how to incorporate the State selection as described AND City selection(s) as described WITH the Member Group(s) selection in the current MembersEmailList procedure. As that only handles the multiple Member Group selection

  10. #25
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Oh, looks like your reply changed.
    Thanks, i'll try that.

  11. #26
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    hi June7,
    So I took what you provided last to this.
    Had to modify .lstCitiesColumn to lstCities.Column
    and some missing C in lstities

    Code:
    Private Sub MemberEmailList_Click()
    On Error GoTo Err_MemberEmailList_Click
        Dim myDB As Database
        Dim qdf As QueryDef
        Dim i As Integer, strSQL As String
        Dim strWhere As String, strIN 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 And lstCities.ListCount - 1
            If MemberGroupSelector.Selected(i) Then
                If MemberGroupSelector.Column(0, i) = " All" Then
            Exit For
                End If
        strIN = strGroups & "'" & MemberGroupSelecter.Column(0, i) & "',"
            End If
        Next i
            If strGroups <> "" Then strGroups = "[Member Group] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
                For i = 0 To lstCities.ListCount - 1 And lstCities.ListCount - 1
                    If lstCities.Selected(i) Then
                        If lstCities.Column(0, i) = " All" Then
                        flgAll = True
                        End If
                    strIN = strCities & "'" & lstCities.Column(0, i) & "',"
                    End If
        Next i
            If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
            strWhere = strGroups & IIf(strGroups <> "" And strCities <> "", " AND ", "") & strCities
            strSQL = "SELECT * FROM MemberGroups " & IIf(strWhere <> "", " WHERE ", "") & strWhere
            
        
        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
    Not seeing though how it handles the Cities selected like it does for the Member-Groups
    Code:
        myDB.QueryDefs.Delete "Member-Groups"
        Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
    1. It is resulting all of the Member Groups in the Member-Groups temp query when one is selected
    2. I don't see or know how it's handling the temp CitySelected temp query like the Member-Group for the City selection(s)?
    3. Then how it handles the State selection?
    4. And for All States or State and All Cities

  12. #27
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I'm not certain what each line here is trying to do but from inserting this it is not doing what I'm looking for.
    as mentioned on previous post, it's putting ALL of the Member Groups even when one is selected and doing nothing from the Cities selected.
    Code:
        For i = 0 To MemberGroupSelector.ListCount - 1 And lstCities.ListCount - 1
            If MemberGroupSelector.Selected(i) Then
                If MemberGroupSelector.Column(0, i) = " All" Then
            Exit For
                End If
        strIN = strGroups & "'" & MemberGroupSelecter.Column(0, i) & "',"
            End If
            Next i
            If strGroups <> "" Then strGroups = "[Member Group] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
                For i = 0 To lstCities.ListCount - 1 And lstCities.ListCount - 1
                    If lstCities.Selected(i) Then
                        If lstCities.Column(0, i) = " All" Then
                        flgAll = True
                        End If
                    strIN = strCities & "'" & lstCities.Column(0, i) & "',"
                    End If
            Next i
                If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
                    strWhere = strGroups & IIf(strGroups <> "" And strCities <> "", " AND ", "") & strCities
                    strSQL = "SELECT * FROM MemberGroups " & IIf(strWhere <> "", " WHERE ", "") & strWhere

  13. #28
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry for the typos (now fixed) - did not test. However, you are not using the latest edits I made. Note that strIN variable is no longer used. And I see no need for the flgAll variable - it is not in my code. I found a couple of other errors due to copy/paste of your original code.

    Why would you want to modify the QueryDefs for Member-Groups and qryCitiesAndStates? What purpose does that serve? You don't do anything else with those queries in the code - they are not exported.

    If the intent of this code is to pull member emails, then just modify the MemberEmailList query.

    I have now tested the revised code in post 23 and it works. However, I can now see that will need to include criteria for the state as well because same city name can be in multiple states - such as Helena which is in 8 states.

    Why don't you save the state abbreviation in Members table instead of the full state name?
    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.

  14. #29
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by June7 View Post
    Sorry for the typos (now fixed) - did not test. However, you are not using the latest edits I made. Note that strIN variable is no longer used. And I see no need for the flgAll variable - it is not in my code. I found a couple of other errors due to copy/paste of your original code.

    Why would you want to modify the QueryDefs for Member-Groups and qryCitiesAndStates? What purpose does that serve? You don't do anything else with those queries in the code - they are not exported.

    If the intent of this code is to pull member emails, then just modify the MemberEmailList query.

    I have now tested the revised code in post 23 and it works.
    ok will try the edited code.

    this is the MemberEmailList query, which is dependent on Member Groups and CitySelection as well as the StateCode
    Code:
    SELECT dbo_v030ALLMembershipsAllDatesByZip.Email, dbo_v030ALLMembershipsAllDatesByZip.LastName, dbo_v030ALLMembershipsAllDatesByZip.FirstName, dbo_v030ALLMembershipsAllDatesByZip.Club, dbo_v030ALLMembershipsAllDatesByZip.City, dbo_v030ALLMembershipsAllDatesByZip.State, dbo_v030ALLMembershipsAllDatesByZip.ZipCode, IIf([MemberGroup]="Supportive","Friends Of Figure Skating",[MemberGroup]) AS [Member Group], dbo_v030ALLMembershipsAllDatesByZip.OptOut, dbo_v030ALLMembershipsAllDatesByZip.StateCode
    FROM (dbo_v030ALLMembershipsAllDatesByZip INNER JOIN [Member-Groups] ON dbo_v030ALLMembershipsAllDatesByZip.MemberGroup = [Member-Groups].[Member Group]) INNER JOIN CitySelection ON dbo_v030ALLMembershipsAllDatesByZip.City = CitySelection.City
    WHERE (((dbo_v030ALLMembershipsAllDatesByZip.EndDate)>=[Forms]![ZipCodeMemberships]![StartDate]) AND ((dbo_v030ALLMembershipsAllDatesByZip.Age)>=[Forms]![ZipCodeMemberships]![Age]))
    GROUP BY dbo_v030ALLMembershipsAllDatesByZip.Email, dbo_v030ALLMembershipsAllDatesByZip.LastName, dbo_v030ALLMembershipsAllDatesByZip.FirstName, dbo_v030ALLMembershipsAllDatesByZip.Club, dbo_v030ALLMembershipsAllDatesByZip.City, dbo_v030ALLMembershipsAllDatesByZip.State, dbo_v030ALLMembershipsAllDatesByZip.ZipCode, IIf([MemberGroup]="Supportive","Friends Of Figure Skating",[MemberGroup]), dbo_v030ALLMembershipsAllDatesByZip.OptOut, dbo_v030ALLMembershipsAllDatesByZip.StateCode
    HAVING (((dbo_v030ALLMembershipsAllDatesByZip.Email) Is Not Null) AND ((dbo_v030ALLMembershipsAllDatesByZip.OptOut)=[Forms]![ZipCodeMemberships]![OptOutEmail]) AND ((dbo_v030ALLMembershipsAllDatesByZip.StateCode)=[Forms]![ZipCodeMemberships]![cboStateCode]))
    ORDER BY dbo_v030ALLMembershipsAllDatesByZip.City, dbo_v030ALLMembershipsAllDatesByZip.State, dbo_v030ALLMembershipsAllDatesByZip.ZipCode;

  15. #30
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    ok, same thing, it's using ALL of the Member Groups and ALL of the Cities from the State selection
    can't do that. it has to be based on what was selected from the Member Groups, from the State selected (which it IS doing) or ALL States, AND if a specific State is selected then what's selected in Cities OR All Cities NOT just All Cities from the selected State and ALL Member Groups.

    It also not handling the error, it needs to tell the user if they didn't select any one of those to do so to run the report. it just runs and null report exports.

    here's the updated incorporated code:

    'Don't you want to pull records from Members table filtered by the Group and City criteria?
    Yes, and it is not doing that.

    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) Like " All*" Then
                    Exit For
                End If
                strGroups = strGroups & "'" & MemberGroupSelector.Column(0, i) & "',"
            End If
         Next i
         If strGroups <> "" Then strGroups = "[MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
    
         For i = 0 To lstCities.ListCount - 1
            If lstCities.Selected(i) Then
                If lstCities.Column(0, i) Like " All*" Then
                    Exit For
                End If
                strCities = strCities & "'" & lstCities.Column(0, i) & "',"
            End If
         Next i
         If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
    
        'Don't you want to pull records from Members table filtered by the Group and City criteria?
    
         strWhere = strGroups & IIf(strGroups <> "" And strCities <> "", " AND ", "") & strCities
    
         strSQL = "SELECT * FROM Members " & IIf(strWhere <> "", " WHERE ", "") & strWhere
        
        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
    "CitySelection" temp query
    Code:
    SELECT *
    FROM Cities;
    where the selected Cities, including selecting All Cities should be in the Critiera in this query as In ('xxxxxx', 'xxxxx') like it is in the "Member-Groups" temp query.
    which would insert the selection like this:
    Code:
    SELECT MemberGroups.[Member Group], *
    FROM MemberGroups
    WHERE (((MemberGroups.[Member Group]) In ('Regular Member')));
    Now it's ignoring any selection and doing ALL of them and not using this at all.

    here is the latest and greatest file: CascadingStates.zip

Page 2 of 3 FirstFirst 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