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.
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.
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.
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.
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
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.
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
Oh, looks like your reply changed.
Thanks, i'll try that.
hi June7,
So I took what you provided last to this.
Had to modify .lstCitiesColumn to lstCities.Column
and some missing C in lstities
Not seeing though how it handles the Cities selected like it does for the Member-GroupsCode: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
1. It is resulting all of the Member Groups in the Member-Groups temp query when one is selectedCode:myDB.QueryDefs.Delete "Member-Groups" Set qdf = myDB.CreateQueryDef("Member-Groups", strSQL)
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
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
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.
ok will try the edited code.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.
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;
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:
Yes, and it is not doing that.'Don't you want to pull records from Members table filtered by the Group and City criteria?
"CitySelection" temp queryCode: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
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.Code:SELECT * FROM Cities;
which would insert the selection like this:
Now it's ignoring any selection and doing ALL of them and not using this at all.Code:SELECT MemberGroups.[Member Group], * FROM MemberGroups WHERE (((MemberGroups.[Member Group]) In ('Regular Member')));
here is the latest and greatest file: CascadingStates.zip