Several combo boxes (with "All" option included) in a form/subform

  • Thread starter Thread starter Paulo
  • Start date Start date
P

Paulo

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,
 
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
 
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 said:
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 said:
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,
Click to expand...


.
Click to expand...
 
Please ignore the previous post. I have fixed the problem.




Paulo said:
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 said:
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 said:
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,
Click to expand...


.
Click to expand...
Click to expand...
 
Jeanette,

Your solution works like a charm. Thank you very much.

Paulo




Paulo said:
Please ignore the previous post. I have fixed the problem.




Paulo said:
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 said:
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

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,



.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top