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
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 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.
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?
Please see attached updated dbCode: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
CascadingStates.zip
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)
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:
Then see: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
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.
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?
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
In the dB attached to Post#3, there is no code for the multi-select.
Nothing attached.... (Post #7)
Please see post #3. At the end of the post is the attachment.
Thank you
Yep, downloaded it again and still no code to handle creating criteria from multi-select list box.
?
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
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 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
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
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:
Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.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
--------------------------------------------------------
Also, you should ensure the top two lines of every module are:
Option Compare Database
Option Explicit
Hi,
I attached an updated one with your ALL Cities correction added in post #11
That's where I was trying to add the WHERE clause under the Member Group WHERE clause and it doesn't work.Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.
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
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:
Don't see any code to get the selected cities from "lstCities" to create a valid WHERE clause.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
--------------------------------------------------------
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?
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:
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.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
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.