Filtering subform combo from main form combo

[mukudu99]

Hi Everyone
There is something i am missing here.
I have a sub form on a main form.
I have set the combo on the main to filter the combo on the sub form and its
working all right.

code:
Private Sub cboProgram_AfterUpdate()
Dim strSql As String
strSql = "SELECT lk_Indicator.IndicatorID, lk_Indicator.Indicator,
lk_Indicator.ProgramAreaID" & _
" FROM lk_Indicator WHERE lk_Indicator.ProgramAreaID = " & Me.cboProgram.
Column(0) & _
" ORDER BY lk_Indicator.IndicatorID ASC"
Me.subfrmZNANDATA.Form.cboIndicator.RowSource = strSql
End Sub
The problem comes in when i change the entry on the main form combo.
The other records do not show on the main form.

Note:
main form has cboIndicator--unbound
subform has cboIndicator---bound to lk_Indicator table

the main form and subform are linked

what i want is all the records entered on the subform to show

Thanks for your time....

 

[Allen Browne]

So you are changing the RowSource of cboIndicator in the subform, so it
contains only the programs relevant to cboProgram in the main form. Your
RowSource query statement indicates it contains 2 fields: IndicatorID and
Indicator. I'm guessing that it is bound to the IndicatorID, but that column
is zero-width so it displays the Indicator?

The combo does not have a different RowSource for every row in the subform.
Therefore when you restrict its RowSource, it does not have the value it
needs to display the other rows. Consequently, there is nothing that Access
can display, and it looks blank on the other rows (even though it does have
an IndicatorID value you can't see.)

Here's some workarounds. Choose which suits best:

a) Don't filter the RowSource. It will then have the values it needs to
display all the rows.

b) Don't hide the bound column. If lk_Indicator is a little lookup table,
you may be able to use the Indicator text field as the primary key (since it
will be required and unique.) Then in your main table, you will use a text
field (same number of characters) as the foreign key field. Add a numeric
sorting key if you wish. The combo's RowSource will just be like this:
SELECT lk_Indicator.Indicator
FROM lk_Indicator
WHERE lkIndicator.ProgramAreaID = 99
ORDER BY lk_Indicator.SortOrder;
Now the display column is the bound column, so Access has the value it needs
to display, even when the value is not in its RowSource.

c) If neither of the above suit, base your subform on a query that includes
your main form and also lk_Indicator. Include lk_Indicator.Indicator in the
source query. You can now place a text box on your form to show the value.
Place this over the top of the combo. In its Enter event, SetFocus to the
combo. The trick is that the combo jumps in front of the text box only on
the current row, so the text box still shows the values on the other rows.

 

[mukudu99 via AccessMonster.com]

Thanks Allen
I went for option 2 and it worked like a CHARM as they say.
I am somehow allergic to making text fields as primary keys but in this case
the primary key is unique and fairly small because it represents codes which
will not exceed 10 characters so i guess it meets the criteria.

Thanks for the solution!!!

So you are changing the RowSource of cboIndicator in the subform, so it
contains only the programs relevant to cboProgram in the main form. Your
RowSource query statement indicates it contains 2 fields: IndicatorID and
Indicator. I'm guessing that it is bound to the IndicatorID, but that column
is zero-width so it displays the Indicator?

The combo does not have a different RowSource for every row in the subform.
Therefore when you restrict its RowSource, it does not have the value it
needs to display the other rows. Consequently, there is nothing that Access
can display, and it looks blank on the other rows (even though it does have
an IndicatorID value you can't see.)

Here's some workarounds. Choose which suits best:

a) Don't filter the RowSource. It will then have the values it needs to
display all the rows.

b) Don't hide the bound column. If lk_Indicator is a little lookup table,
you may be able to use the Indicator text field as the primary key (since it
will be required and unique.) Then in your main table, you will use a text
field (same number of characters) as the foreign key field. Add a numeric
sorting key if you wish. The combo's RowSource will just be like this:
SELECT lk_Indicator.Indicator
FROM lk_Indicator
WHERE lkIndicator.ProgramAreaID = 99
ORDER BY lk_Indicator.SortOrder;
Now the display column is the bound column, so Access has the value it needs
to display, even when the value is not in its RowSource.

c) If neither of the above suit, base your subform on a query that includes
your main form and also lk_Indicator. Include lk_Indicator.Indicator in the
source query. You can now place a text box on your form to show the value.
Place this over the top of the combo. In its Enter event, SetFocus to the
combo. The trick is that the combo jumps in front of the text box only on
the current row, so the text box still shows the values on the other rows.

Hi Everyone
There is something i am missing here.

[quoted text clipped - 23 lines]

what i want is all the records entered on the subform to show