Filtering only the current record's Combo Box on a continuous form

[CES]

All,

I have a subform embedded as a Continuous Form. Within the subform I have a combo box that I would like to set the RowSource based on a checkbox true\false value. The default value of the checkbox is true. If the checkbox value is true I want to filter the available items in the combo box based on a value that I have in my main form. The following works correctly for the current record.

If Me.FilterCategory1.Value = True Then
x = Me.Parent.Category.Value
strSQL = "SELECT ...;"
Else
strSQL = "SELECT ...;"
End If

Me.SubCategory1.RowSource = strSQL


The problem I'm running into is that when I apply the RowSource filter it effects every record in a continuous form. So that if I apply the filter on Record 1 and one of the combo box's values is outside of the filter on any of the other records in the continuous form, it will show as an empty combo box even though it does have a value.

Is there a way of only effecting the current record that I am on. Any help on this would be greatly appreciated. Thanks in advance. - CES

 

[Allen Browne]

The combo only has one RowSource, so it is not possible to make it different
in different rows.

The problem only occurs if the Bound Column is not visible (e.g.
zero-width). If the bound column is the visible column, Access can show the
right value, even if it is not in the RowSource. Therefore one solution is
to make the category text field the primary key instead of using a hidden
autonumber.

Another solution is to change the RecordSource for the form to a query so
that it includes the text field from the combo's lookup table. You can then
show it in a text box on the form. Place the combo behind the text box
(Format | Send to Back, in design view), and set its GotFocus event to
SetFocus to the combo. This way the text box in front of the combo masks the
fact that the combo is blank on many rows, but as soon as the text box gets
focus, the combo jumps in front (on the current record only) so the user can
still use the combo.

 

[CES]

The combo only has one RowSource, so it is not possible to make it
different in different rows.

The problem only occurs if the Bound Column is not visible (e.g.
zero-width). If the bound column is the visible column, Access can show
the right value, even if it is not in the RowSource. Therefore one
solution is to make the category text field the primary key instead of
using a hidden autonumber.

Another solution is to change the RecordSource for the form to a query
so that it includes the text field from the combo's lookup table. You
can then show it in a text box on the form. Place the combo behind the
text box (Format | Send to Back, in design view), and set its GotFocus
event to SetFocus to the combo. This way the text box in front of the
combo masks the fact that the combo is blank on many rows, but as soon
as the text box gets focus, the combo jumps in front (on the current
record only) so the user can still use the combo.

Allen,
Thank you for your response I just wanted to explore one of the suggestions that you provided a little further.

Your suggestion is to put two controls on the form of identical height and width, one control is a text box (UnBound) and the other is a combo box bound to the Data field.
The text box equals a function that returns the value equivalent to the text boxes column 2 (column 1 is a stored and hidden value).
You then stack the text box on top of the combo box and set the tab order for the text box before the combo box.
While you don't mention it, I'm assuming that the text box visibility property would be set to true and the combo box visibility property would be set to false (to compensate for the fact that the combo box might actually be displayed properly).
An event property (on enter or focus) for the text box is then set to:
comboBox.visibility = True
comboBox.SetFocus
txtBox.visibility = False
and then after the comboBox focus has been lost on (Exit or LostFocus) you then do the reverse.
txtBox.visibility = True
comboBox.visibility = False

The reason I'm being so explicit is because I've tried this before to compensate for the fact that you cannot dynamically render a control object in VBScript and while it's quite possible I screwed up the coding I wasn't able to get it to work properly.
I appreciate your thoughtful advice, thanks in again. - CES

 

[Allen Browne]

There's no need to set the Visible property of either the combo or text box.
The combo sits behind the text box (so you can't actually see if even though
its Visible property is Yes.) When the text box gets focus, it transfers
focus to the combo (i.e. the combo jumps in front of the text box.)

The trick is that it's only the current record that gets the combo jumping
in front. The other rows still display the text box, and hence the data is
visible there.

And btw, the Control Source of the text box is not a function, nor an
expression like [combo1].Column(2). It is a field name. The field comes from
the lookup table, which you included in the form's RecordSource. That is,
you changed the RecordSource property of the form to a query that includes
both its usual table and the lookup table (the table used as the combo's
RowSource) so you can get that field available for the text box.

 

[CES]

There's no need to set the Visible property of either the combo or text
box. The combo sits behind the text box (so you can't actually see if
even though its Visible property is Yes.) When the text box gets focus,
it transfers focus to the combo (i.e. the combo jumps in front of the
text box.)

The trick is that it's only the current record that gets the combo
jumping in front. The other rows still display the text box, and hence
the data is visible there.

And btw, the Control Source of the text box is not a function, nor an
expression like [combo1].Column(2). It is a field name. The field comes
from the lookup table, which you included in the form's RecordSource.
That is, you changed the RecordSource property of the form to a query
that includes both its usual table and the lookup table (the table used
as the combo's RowSource) so you can get that field available for the
text box.

Allen,
THANK YOU... that's an amazing workaround. I was a little confused by why the bottom ComboBox doesn't stay visible once it has received and lost Focus but it works properly so... Once again, Thank you - CES