Page 3 of 3 FirstFirst 123
Results 31 to 44 of 44
  1. #31
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Edited previous post.



    This revised code works for me:
    Code:
    Dim myDB As Database
    Dim qdf As QueryDef
    Dim i As Integer
    Dim strWhere As String, strGroups As String, strCities As String, strState As String, strSQL As String
    
    Set myDB = CurrentDb()
    
    With Me
    
        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) & ")"
        
        If Not IsNull(.cboStateCode) Then
            strState = "[State] ='" & .cboStateCode.Column(1) & "'"
        End If
        
        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) & ")"
    
    End With
    
    'pull records from Members table filtered by the Group and State and City criteria
    strWhere = strGroups
    strWhere = strWhere & IIf(strWhere <> "" And strState <> "", " AND ", "") & strState
    strWhere = strWhere & IIf(strWhere <> "" And strCities <> "", " AND ", "") & strCities
    strSQL = "SELECT * FROM Members " & IIf(strWhere <> "", " WHERE ", "") & strWhere
    myDB.QueryDefs.Delete "MemberEmailList"
    Set qdf = myDB.CreateQueryDef("MemberEmailList", strSQL)
    DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "C:\Temp\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    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.

  2. #32
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok, will try this.
    I uploaded the latest and greatest to my last post BUT before your current revised code.

    Ah, it's doing the selections in the "MemberEmailList" query itself.

    Issue, I have more than one query list reports that's dependent on the Member-Groups, CitySelection, State.
    it should not be inclusive as it is now

    and the selection City In ('All Cities') is not working, nor the Message Box letting the user know they didn't make selection(s)
    plus only certain fields are needed in this query as well as other queries utilizing the selections AND some having to Group By due to the details in the originating table of the data set.

    Code:
    SELECT *
    FROM dbo_v030ALLMembershipsAllDatesByZip
    WHERE (((dbo_v030ALLMembershipsAllDatesByZip.[MemberGroup]) In ('Basic Skills Member')) AND ((dbo_v030ALLMembershipsAllDatesByZip.[State])='COLORADO') AND ((dbo_v030ALLMembershipsAllDatesByZip.[City]) In ('ALL CITIES')));

  3. #33
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    This is the actual and full query.
    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
    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;

  4. #34
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, did not test the "ALL*" choice. Will do that now.

    Since you don't presently have an "All*" item for the Groups, this condition is not really needed for that listbox.


    I did not incorporate message box into my procedure because the code will still run and export all records if no criteria selected. Okay, now I see need for the flgAll code.

    If strWHERE = "" And Not flgAll Then
    MsgBox "No criteria selected"
    Exit Sub
    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.

  5. #35
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Hope you didn't already read my previous post before I edited it.
    Another code revision:
    Code:
    Dim myDB As Database
    Dim qdf As QueryDef
    Dim i As Integer
    Dim strWhere As String, strGroups As String, strCities As String, strState As String, strSQL As String, flgAll As Boolean
    
    Set myDB = CurrentDb()
    
    With Me
    
        For i = 0 To .MemberGroupSelector.ListCount - 1
            If .MemberGroupSelector.Selected(i) Then
                strGroups = strGroups & "'" & .MemberGroupSelector.Column(0, i) & "',"
            End If
        Next i
        If strGroups <> "" Then strGroups = "[MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
        
        If Not IsNull(.cboStateCode) Then
            strState = "[State] ='" & .cboStateCode.Column(1) & "'"
        End If
        
        For i = 0 To .lstCities.ListCount - 1
            If .lstCities.Selected(i) Then
                If .lstCities.Column(0, i) Like " All*" Then
                    flgAll = True
                    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) & ")"
    
    End With
    
    strWhere = strGroups
    strWhere = strWhere & IIf(strWhere <> "" And strState <> "", " AND ", "") & strState
    strWhere = strWhere & IIf(strWhere <> "" And strCities <> "", " AND ", "") & strCities
    If strWhere = "" And Not flgAll Then
        MsgBox "No criteria selected"
        Exit Sub
    End If
    
    'pull records from Members table filtered by the Group and State and City criteria
    strSQL = "SELECT * FROM Members " & IIf(strWhere <> "", " WHERE ", "") & strWhere
    myDB.QueryDefs.Delete "MemberEmailList"
    Set qdf = myDB.CreateQueryDef("MemberEmailList", strSQL)
    DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "C:\Temp\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    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. #36
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Hi June7,
    Thanks!
    So I'm getting the message to select when there isn't a selection (though how do you also do the error check on the other selections?, looks likes it just when a Member Group isn't selected)

    here's the MemberEmailList query result when ALL is selected. It doesn't display anything
    Code:
    SELECT *
    FROM Members
    WHERE (((Members.[MemberGroup]) In ('Basic Skills Member')) AND ((Members.[State])='ALL States') AND ((Members.[City]) In ('ALL CITIES')));
    Don't know if you saw my comment #33 but this can't work this way due to other parameters the query uses and needing the selections in the temp queries do be used in other queries.

    Please see updated db: CascadingStates.zip

    And take a look at query "Copy Of MemberEmailList"
    This is the minimum on the critiera linking to the temp queries based on selection of Member-Groups and CitySelection

    There is a lot more involved, there's a form date range criteria, age criteria, etc ...
    since I have to have these PLUS the Group By and since there are a lot more fields than you are seeing the Members table (it's derived from the linked dbo View, since you can't view linked file I just dumped some sample records to the Members table.

    Anyway MemberEmailList is just one query of many with other selections from the main form that'll be passed.
    those are easy to do AS LONG as the query doesn't change by the way you currently have it set. it can't be that way.

    I am grateful you're trying to help with this but having changed that whole process, I feel like we're back to the beginning.

    I'm looking for help in how to incorporate the additional selections, including an All option in the procedure that existed.
    not changing the whole structure of how it was working which breaks everything else that was working.

  7. #37
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, maybe up to speed now. Modify Groups and CitiesState queries and export the Members query.

    More like this:
    Code:
    Dim myDB As Database
    Dim qdf As QueryDef
    Dim i As Integer
    Dim strWhere As String, strGroups As String, strCities As String, strState As String, strSQL As String, strStateCities As String, flgAll As Boolean
    
    Set myDB = CurrentDb()
    
    With Me
    
        For i = 0 To .MemberGroupSelector.ListCount - 1
            If .MemberGroupSelector.Selected(i) Then
                strGroups = strGroups & "'" & .MemberGroupSelector.Column(0, i) & "',"
            End If
        Next I
        If strGroups <> "" Then strGroups = "WHERE [MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
        
        If Not IsNull(.cboStateCode) Then
            strState = "[State] ='" & .cboStateCode.Column(1) & "'"
        End If
        
        For i = 0 To lstCities.ListCount - 1
            If .lstCities.Selected(i) Then
                If .lstCities.Column(0, i) Like " All*" Then
                    flgAll = True
                    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) & ")"
        strStateCities = strState & IIf(strState <> "", " AND ", "") & strCities
    
    End With
    
    If strGroups = "" And strStateCities = "" And Not flgAll Then
        MsgBox "No criteria selected"
        Exit Sub
    End If
    
    myDB.QueryDefs.Delete "Member-Groups"
    Set qdf = myDB.CreateQueryDef("Member-Groups", "SELECT * FROM MemberGroups " & strGroups)
    
    myDB.QueryDefs.Delete "qryCitiesAndStates"
    Set qdf = myDB.CreateQueryDef("qryCitiesAndStates", "SELECT States.State, Cities.City, States.StateCode " & _
                "FROM States INNER JOIN Cities ON States.StateCode = Cities.StateCode " & _
                IIf(strStateCities <> "", " WHERE ", "") & strStateCities & " ORDER BY States.State, Cities.City;")
    
    DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "C:\Temp\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen
    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. #38
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    It's still not working with post #37 modification

    It still gives NULL Values when ALL is selected
    AND it does NOT refresh the lstCities anymore when you select a State as it was before
    AND the error on no selection from State / lstCities is still no prompting

    Code:
    SELECT States.State, Cities.City, States.StateCode
    FROM States INNER JOIN Cities ON States.StateCode = Cities.StateCode
    WHERE (((States.[State])='ALL States') AND ((Cities.[City]) In ('ALL CITIES')))
    ORDER BY States.State, Cities.City;

  9. #39
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It works in the db I download from original post. Let me download your latest db and do some more testing.
    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. #40
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Strange

    Ok, and THANK YOU for sticking through this!

  11. #41
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    June7,
    I found a MultiSelect db.

    See attached. CascadingStates.zip It has the latest of what we're working on and another db named MultiSelect.accdb.

    Are you able to utilize the code iin the Event Procedure on the button named "Filter Report on Multiple values from Fields, combined with AND" for what I'm trying to do?
    NOTE: Don't want another button to Select All, it should be within the State list and City list

    Which is, to be sure we're on the same page:
    User must select:
    1. A Member Group or multiple Member Groups from the listbox
    2. A State or All States from the combobox
    3. A City or multiple cities or All Cities from the listbox

    The user needs to get a prompt if any one of those are blank.

  12. #42
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The Copy query needs a compound link on City and State.

    SELECT Members.Email, Members.LastName, Members.FirstName, Members.City, Members.State, Members.ZipCode, IIf([MemberGroup]="Supportive","Friends Of Figure Skating",[MemberGroup]) AS [Member Group]
    FROM (Members INNER JOIN CitySelection ON (CitySelection.State = Members.State) AND (Members.City = CitySelection.City)) INNER JOIN [Member-Groups] ON Members.MemberGroup = [Member-Groups].[Member Group]
    GROUP BY Members.Email, Members.LastName, Members.FirstName, Members.City, Members.State, Members.ZipCode, IIf([MemberGroup]="Supportive","Friends Of Figure Skating",[MemberGroup])
    HAVING (((Members.Email) Is Not Null))
    ORDER BY Members.City, Members.State, Members.ZipCode;

    Code:
    Dim myDB As Database
    Dim qdf As QueryDef
    Dim i As Integer
    Dim strWhere As String, strGroups As String, strCities As String, strState As String, strSQL As String, strStateCities As String
    Dim flgAllStates As Boolean, flgAllCities As Boolean
    
    Set myDB = CurrentDb()
    
    With Me
    
        For i = 0 To .MemberGroupSelector.ListCount - 1
            If .MemberGroupSelector.Selected(i) Then
                strGroups = strGroups & "'" & .MemberGroupSelector.Column(0, i) & "',"
            End If
        Next i
        If strGroups <> "" Then strGroups = "WHERE [Member Group] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
        
        If Not IsNull(.cboStateCode) Then
            If .cboStateCode Like "*ALL*" Then
                flgAllStates = True
            Else
                strState = "[State] ='" & .cboStateCode.Column(1) & "'"
            End If
        End If
        
        If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
        For i = 0 To lstCities.ListCount - 1
            If .lstCities.Selected(i) Then
                If .lstCities.Column(0, i) Like "*All Cities*" Then
                    flgAllCities = True
                    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) & ")"
        strStateCities = strState & IIf(strState <> "" And strCities <> "", " AND ", "") & strCities
    
    End With
    
    If strGroups = "" And strStateCities = "" And Not flgAllStates And Not flgAllCities Then
        MsgBox "No criteria selected"
        Exit Sub
    End If
    
    myDB.QueryDefs.Delete "Member-Groups"
    Set qdf = myDB.CreateQueryDef("Member-Groups", "SELECT * FROM MemberGroups " & strGroups)
    myDB.QueryDefs.Delete "CitySelection"
    Set qdf = myDB.CreateQueryDef("CitySelection", "SELECT * FROM qryCitiesAndStates " & _
                IIf(strStateCities <> "", " WHERE ", "") & strStateCities)
    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.

  13. #43
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    OMG! Thank you! It works.

    Thank you so much!

    Greatly appreciated!
    Last edited by aellistechsupport; 12-11-2015 at 11:11 PM.

  14. #44
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code will permit user to not make any selection in the Cities listbox as long as Group or State selection made and only one of them must be selected.

    Basically, the validation code only insists on selection of one of the criteria, others are treated as "ALL". Really, could do this by default for all 3 and not have to nag users to make selection.

    Just depends how much you want to dictate users.
    Last edited by June7; 12-11-2015 at 11:40 PM.
    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 3 of 3 FirstFirst 123
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