If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Several combo boxes (with "All" option included) in a form/subform
I built first a form with 3 combo boxes (all of them with the "All" option
created using a Union query) and a subform. Form and subform are linked (Master/Child fields) using the combo boxes. I also tweaked the SQL code for the subform query, following advice from this forum, so that any combination of choices (including the “All” option in all of them) in the 3 combos generates the right set of records in the subform. Perfect so far, with 3 combos, because the number of combinations is not too much. But if I try to expand to 7 combo boxes (i.e. 7 different fields to filter the data, and taking into account the "All" option in all of them as well), I cannot any more just adjust the SQL code of the subform query, because the number of possible combinations becomes staggering. Would a viable solution be via form code, instead of changing the SQL code? Can you make any suggestions? Thanks in advance, |
#2
|
|||
|
|||
Several combo boxes (with "All" option included) in a form/subform
With 7 combos, I would be tempted to remove the 'all' option from the
combos. You can put a reset button on the form to clear all the combos in one go. You can also use a union query to put an empty option at the top of the list for each combo. Then you can simplify the filtering code like this Dim strWhere As String Dim lngLen As Long 'Look at each search box, and build up the criteria string from the non-blank ones. 'text field example. Use Like to find anywhere in the field. If Len(Me.cboFilterContact & vbNullString) 0 Then strWhere = "[ClientID] = " & Me.cboFilterContact & " AND " End If If Len(Me.txtFilterMainName & vbNullString) 0 Then strWhere = strWhere & "([MainName] Like """ & Me.txtFilterMainName & "*"") AND " End If If Len(Me.cboFilterStatus & vbNullString) 0 Then strWhere = strWhere & "[StatusID] = """ & Me.cboFilterStatus & """ AND " End If 'Date field example. Use the format string to add the # delimiters and get the right international format. If Len(Me.txtFilterStartDate & vbNullString) 0 Then If IsDate(Me.txtFilterStartDate) Then strWhere = strWhere & "([EnteredOn] = " & SQLDate(Me.txtFilterStartDate) & ") AND " End If End If 'Another date field example. Use "less than the next day" since this field has times as well as dates. If Len(Me.txtFilterEndDate & vbNullString) 0 Then 'Less than the next day. If IsDate(Me.txtFilterEndDate) Then strWhere = strWhere & "([EnteredOn] " & SQLDate(DateAdd("d", 1, Me.txtFilterEndDate)) & ") AND " End If End If 'Chop off the trailing " AND ", and use the string as the form's Filter. 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strWhere) - 5 'Debug.Print lngLen, Now() If lngLen = 0 Then Me.Filter = "(False)" Else strWhere = Left$(strWhere, lngLen) 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.Filter = strWhere End If Me.FilterOn = True End If The user gets the same effect as the 'all' option by choosing the empty row at the top of any combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Austrlia "Paulo" wrote in message ... I built first a form with 3 combo boxes (all of them with the "All" option created using a Union query) and a subform. Form and subform are linked (Master/Child fields) using the combo boxes. I also tweaked the SQL code for the subform query, following advice from this forum, so that any combination of choices (including the "All" option in all of them) in the 3 combos generates the right set of records in the subform. Perfect so far, with 3 combos, because the number of combinations is not too much. But if I try to expand to 7 combo boxes (i.e. 7 different fields to filter the data, and taking into account the "All" option in all of them as well), I cannot any more just adjust the SQL code of the subform query, because the number of possible combinations becomes staggering. Would a viable solution be via form code, instead of changing the SQL code? Can you make any suggestions? Thanks in advance, |
#3
|
|||
|
|||
Several combo boxes (with "All" option included) in a form/sub
Hi Jeanette, thanks for your posting.
I am trying to implement your solution. A first problem encountered was the message "Object doesn't support this property or method" on the line: Forms![Bids Tracking]![Sub Bids Tracking].Filter = strWhere (the combos are in the form, but the data selected is shown in the subform). I tried to put the line Forms![Bids Tracking]![Sub Bids Tracking].FilterOn = True before, but there was the same error message. Any suggestions? Thanks, Paulo "Jeanette Cunningham" wrote: With 7 combos, I would be tempted to remove the 'all' option from the combos. You can put a reset button on the form to clear all the combos in one go. You can also use a union query to put an empty option at the top of the list for each combo. Then you can simplify the filtering code like this Dim strWhere As String Dim lngLen As Long 'Look at each search box, and build up the criteria string from the non-blank ones. 'text field example. Use Like to find anywhere in the field. If Len(Me.cboFilterContact & vbNullString) 0 Then strWhere = "[ClientID] = " & Me.cboFilterContact & " AND " End If If Len(Me.txtFilterMainName & vbNullString) 0 Then strWhere = strWhere & "([MainName] Like """ & Me.txtFilterMainName & "*"") AND " End If If Len(Me.cboFilterStatus & vbNullString) 0 Then strWhere = strWhere & "[StatusID] = """ & Me.cboFilterStatus & """ AND " End If 'Date field example. Use the format string to add the # delimiters and get the right international format. If Len(Me.txtFilterStartDate & vbNullString) 0 Then If IsDate(Me.txtFilterStartDate) Then strWhere = strWhere & "([EnteredOn] = " & SQLDate(Me.txtFilterStartDate) & ") AND " End If End If 'Another date field example. Use "less than the next day" since this field has times as well as dates. If Len(Me.txtFilterEndDate & vbNullString) 0 Then 'Less than the next day. If IsDate(Me.txtFilterEndDate) Then strWhere = strWhere & "([EnteredOn] " & SQLDate(DateAdd("d", 1, Me.txtFilterEndDate)) & ") AND " End If End If 'Chop off the trailing " AND ", and use the string as the form's Filter. 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strWhere) - 5 'Debug.Print lngLen, Now() If lngLen = 0 Then Me.Filter = "(False)" Else strWhere = Left$(strWhere, lngLen) 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.Filter = strWhere End If Me.FilterOn = True End If The user gets the same effect as the 'all' option by choosing the empty row at the top of any combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Austrlia "Paulo" wrote in message ... I built first a form with 3 combo boxes (all of them with the "All" option created using a Union query) and a subform. Form and subform are linked (Master/Child fields) using the combo boxes. I also tweaked the SQL code for the subform query, following advice from this forum, so that any combination of choices (including the "All" option in all of them) in the 3 combos generates the right set of records in the subform. Perfect so far, with 3 combos, because the number of combinations is not too much. But if I try to expand to 7 combo boxes (i.e. 7 different fields to filter the data, and taking into account the "All" option in all of them as well), I cannot any more just adjust the SQL code of the subform query, because the number of possible combinations becomes staggering. Would a viable solution be via form code, instead of changing the SQL code? Can you make any suggestions? Thanks in advance, . |
#4
|
|||
|
|||
Several combo boxes (with "All" option included) in a form/sub
Please ignore the previous post. I have fixed the problem.
"Paulo" wrote: Hi Jeanette, thanks for your posting. I am trying to implement your solution. A first problem encountered was the message "Object doesn't support this property or method" on the line: Forms![Bids Tracking]![Sub Bids Tracking].Filter = strWhere (the combos are in the form, but the data selected is shown in the subform). I tried to put the line Forms![Bids Tracking]![Sub Bids Tracking].FilterOn = True before, but there was the same error message. Any suggestions? Thanks, Paulo "Jeanette Cunningham" wrote: With 7 combos, I would be tempted to remove the 'all' option from the combos. You can put a reset button on the form to clear all the combos in one go. You can also use a union query to put an empty option at the top of the list for each combo. Then you can simplify the filtering code like this Dim strWhere As String Dim lngLen As Long 'Look at each search box, and build up the criteria string from the non-blank ones. 'text field example. Use Like to find anywhere in the field. If Len(Me.cboFilterContact & vbNullString) 0 Then strWhere = "[ClientID] = " & Me.cboFilterContact & " AND " End If If Len(Me.txtFilterMainName & vbNullString) 0 Then strWhere = strWhere & "([MainName] Like """ & Me.txtFilterMainName & "*"") AND " End If If Len(Me.cboFilterStatus & vbNullString) 0 Then strWhere = strWhere & "[StatusID] = """ & Me.cboFilterStatus & """ AND " End If 'Date field example. Use the format string to add the # delimiters and get the right international format. If Len(Me.txtFilterStartDate & vbNullString) 0 Then If IsDate(Me.txtFilterStartDate) Then strWhere = strWhere & "([EnteredOn] = " & SQLDate(Me.txtFilterStartDate) & ") AND " End If End If 'Another date field example. Use "less than the next day" since this field has times as well as dates. If Len(Me.txtFilterEndDate & vbNullString) 0 Then 'Less than the next day. If IsDate(Me.txtFilterEndDate) Then strWhere = strWhere & "([EnteredOn] " & SQLDate(DateAdd("d", 1, Me.txtFilterEndDate)) & ") AND " End If End If 'Chop off the trailing " AND ", and use the string as the form's Filter. 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strWhere) - 5 'Debug.Print lngLen, Now() If lngLen = 0 Then Me.Filter = "(False)" Else strWhere = Left$(strWhere, lngLen) 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.Filter = strWhere End If Me.FilterOn = True End If The user gets the same effect as the 'all' option by choosing the empty row at the top of any combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Austrlia "Paulo" wrote in message ... I built first a form with 3 combo boxes (all of them with the "All" option created using a Union query) and a subform. Form and subform are linked (Master/Child fields) using the combo boxes. I also tweaked the SQL code for the subform query, following advice from this forum, so that any combination of choices (including the "All" option in all of them) in the 3 combos generates the right set of records in the subform. Perfect so far, with 3 combos, because the number of combinations is not too much. But if I try to expand to 7 combo boxes (i.e. 7 different fields to filter the data, and taking into account the "All" option in all of them as well), I cannot any more just adjust the SQL code of the subform query, because the number of possible combinations becomes staggering. Would a viable solution be via form code, instead of changing the SQL code? Can you make any suggestions? Thanks in advance, . |
#5
|
|||
|
|||
Several combo boxes (with "All" option included) in a form/sub
Jeanette,
Your solution works like a charm. Thank you very much. Paulo "Paulo" wrote: Please ignore the previous post. I have fixed the problem. "Paulo" wrote: Hi Jeanette, thanks for your posting. I am trying to implement your solution. A first problem encountered was the message "Object doesn't support this property or method" on the line: Forms![Bids Tracking]![Sub Bids Tracking].Filter = strWhere (the combos are in the form, but the data selected is shown in the subform). I tried to put the line Forms![Bids Tracking]![Sub Bids Tracking].FilterOn = True before, but there was the same error message. Any suggestions? Thanks, Paulo "Jeanette Cunningham" wrote: With 7 combos, I would be tempted to remove the 'all' option from the combos. You can put a reset button on the form to clear all the combos in one go. You can also use a union query to put an empty option at the top of the list for each combo. Then you can simplify the filtering code like this Dim strWhere As String Dim lngLen As Long 'Look at each search box, and build up the criteria string from the non-blank ones. 'text field example. Use Like to find anywhere in the field. If Len(Me.cboFilterContact & vbNullString) 0 Then strWhere = "[ClientID] = " & Me.cboFilterContact & " AND " End If If Len(Me.txtFilterMainName & vbNullString) 0 Then strWhere = strWhere & "([MainName] Like """ & Me.txtFilterMainName & "*"") AND " End If If Len(Me.cboFilterStatus & vbNullString) 0 Then strWhere = strWhere & "[StatusID] = """ & Me.cboFilterStatus & """ AND " End If 'Date field example. Use the format string to add the # delimiters and get the right international format. If Len(Me.txtFilterStartDate & vbNullString) 0 Then If IsDate(Me.txtFilterStartDate) Then strWhere = strWhere & "([EnteredOn] = " & SQLDate(Me.txtFilterStartDate) & ") AND " End If End If 'Another date field example. Use "less than the next day" since this field has times as well as dates. If Len(Me.txtFilterEndDate & vbNullString) 0 Then 'Less than the next day. If IsDate(Me.txtFilterEndDate) Then strWhere = strWhere & "([EnteredOn] " & SQLDate(DateAdd("d", 1, Me.txtFilterEndDate)) & ") AND " End If End If 'Chop off the trailing " AND ", and use the string as the form's Filter. 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strWhere) - 5 'Debug.Print lngLen, Now() If lngLen = 0 Then Me.Filter = "(False)" Else strWhere = Left$(strWhere, lngLen) 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.Filter = strWhere End If Me.FilterOn = True End If The user gets the same effect as the 'all' option by choosing the empty row at the top of any combo. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Austrlia "Paulo" wrote in message ... I built first a form with 3 combo boxes (all of them with the "All" option created using a Union query) and a subform. Form and subform are linked (Master/Child fields) using the combo boxes. I also tweaked the SQL code for the subform query, following advice from this forum, so that any combination of choices (including the "All" option in all of them) in the 3 combos generates the right set of records in the subform. Perfect so far, with 3 combos, because the number of combinations is not too much. But if I try to expand to 7 combo boxes (i.e. 7 different fields to filter the data, and taking into account the "All" option in all of them as well), I cannot any more just adjust the SQL code of the subform query, because the number of possible combinations becomes staggering. Would a viable solution be via form code, instead of changing the SQL code? Can you make any suggestions? Thanks in advance, . |
Thread Tools | |
Display Modes | |
|
|