Requerying combo box in sub sub form

[TRose]

I have a form with two layers of subforms, the 2nd of which includes a combo
box whose data is based upon a selection in the main form. I'm having
trouble getting the data in the combo box to update when the record on the
main form changes, having tried various combinations of macros, controls and
properties without success. Currently, I have a message box macro attached
to the On Change property of the main form that asks the user to go to the
combo box in the sub sub form and hit "F9" which requeries the data. This
works, but I can't figure out how to get this to occur automatically.

For example, using Northwind terminology, let's say that a customer can only
purchase certain products, but your products are grouped by category. You
select the customer's name on the main form and then the category on the sub
form, but the combo box to select the individual product isn't filtered to
those that the customer can purchase.

This may not be the best description of what my problem is, but I'd
appreciate any suggestions that you may have.

 

[Graham Mandeno]

Hi TRose

You refer to controls on a subform using this syntax:

<container form object>![subform control name].Form![control name]

The form object contained in the subform is:

<container form object>![subform control name].Form

So you can use that as the <container form object> to get down to a lower
level.

So what you need is:

Me![Subform1].Form![Subform2].Form![Combobox].Requery

Note that here, Subform1 and Subform2 are the names of the subform container
controls, which are not necessarily the same as the names of the form
objects that they contain.

Also, you should attach this code to the AfterUpdate event of the control
that is changing, not the Change event.

 

[Marshall Barton]

I have a form with two layers of subforms, the 2nd of which includes a combo
box whose data is based upon a selection in the main form. I'm having
trouble getting the data in the combo box to update when the record on the
main form changes, having tried various combinations of macros, controls and
properties without success. Currently, I have a message box macro attached
to the On Change property of the main form that asks the user to go to the
combo box in the sub sub form and hit "F9" which requeries the data. This
works, but I can't figure out how to get this to occur automatically.

Use the main form's Current event:

Me.subform1control.Form.subform2control.combo.Requery

 

[TRose]

Graham and Marshall:

Thank you for your replies, but I'm not having any success with your
suggestions. Let me try to be more specific to see if that would help.

My main form is named Account and the information on this form is brought
forward from existing data so no data is entered here. This is why I have
the macro on the Change event and not the AfterUpdate event. One account is
shown on each record of the form and has been previously identified with
certain assertions.

The subform to the Account form is named Risk (data is entered here). The
subform to the Risk subform is named Assertion and this includes the
AssertionSelect combo box that isn't filtering to the specific assertions
based upon the account selected in the Account form. (Yes, these are the
names of the controls and not the names of the underlying objects.)

For example, out of 13 possible assertions, account #1 may have been
identified with 5 assertions and account #2 with 7 assertions. When data is
entered in the Risk subform for account #1, I want the user to select from
the list of 5 assertions that account. When the user goes to the next
account (#2), the list of assertions should change to the 7 for that account.
And so on ...

So, based upon your suggestions, I created a macro and added the Requery
action for the following controls:

Graham: Account![Risk].Form![Assertion].Form![AssertionSelect]
Marshall: Account.Risk.Form.Assertion.AssertionSelect

This macro was attached to the Change event on the Account form, but both
options generated errors that the control didn't exist.

I suspect that I don't have the syntax quite right or need to take a
different approach to this and would appreciate any additional help that you
can provide. I don't consider myself a programmer, so it might help if you
could be specific for me. Thanks.

Hi TRose

You refer to controls on a subform using this syntax:

<container form object>![subform control name].Form![control name]

The form object contained in the subform is:

<container form object>![subform control name].Form

So you can use that as the <container form object> to get down to a lower
level.

So what you need is:

Me![Subform1].Form![Subform2].Form![Combobox].Requery

Note that here, Subform1 and Subform2 are the names of the subform container
controls, which are not necessarily the same as the names of the form
objects that they contain.

Also, you should attach this code to the AfterUpdate event of the control
that is changing, not the Change event.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with two layers of subforms, the 2nd of which includes a
combo
box whose data is based upon a selection in the main form. I'm having
trouble getting the data in the combo box to update when the record on the
main form changes, having tried various combinations of macros, controls
and
properties without success. Currently, I have a message box macro
attached
to the On Change property of the main form that asks the user to go to the
combo box in the sub sub form and hit "F9" which requeries the data. This
works, but I can't figure out how to get this to occur automatically.

For example, using Northwind terminology, let's say that a customer can
only
purchase certain products, but your products are grouped by category. You
select the customer's name on the main form and then the category on the
sub
form, but the combo box to select the individual product isn't filtered to
those that the customer can purchase.

This may not be the best description of what my problem is, but I'd
appreciate any suggestions that you may have.

 

[Marshall Barton]

Graham and Marshall:

Thank you for your replies, but I'm not having any success with your
suggestions. Let me try to be more specific to see if that would help.

My main form is named Account and the information on this form is brought
forward from existing data so no data is entered here. This is why I have
the macro on the Change event and not the AfterUpdate event. One account is
shown on each record of the form and has been previously identified with
certain assertions.

The change event is triggered for each character that is
typed into the control (which control?) This seems to be
totally inappropriate. If you want the subform's combo box
to sync with the main form as you navigate through the main
form records, then use the main form's Current event.

The subform to the Account form is named Risk (data is entered here). The
subform to the Risk subform is named Assertion and this includes the
AssertionSelect combo box that isn't filtering to the specific assertions
based upon the account selected in the Account form. (Yes, these are the
names of the controls and not the names of the underlying objects.)

For example, out of 13 possible assertions, account #1 may have been
identified with 5 assertions and account #2 with 7 assertions. When data is
entered in the Risk subform for account #1, I want the user to select from
the list of 5 assertions that account. When the user goes to the next
account (#2), the list of assertions should change to the 7 for that account.
And so on ...

So, based upon your suggestions, I created a macro and added the Requery
action for the following controls:

Graham: Account![Risk].Form![Assertion].Form![AssertionSelect]
Marshall: Account.Risk.Form.Assertion.AssertionSelect

This macro was attached to the Change event on the Account form, but both
options generated errors that the control didn't exist.

Something about your words does not ring true. AFAIK, forms
do not have a change event. Only text and combo boxes have
a change event. (Well, tab control do too, but you haven't
mentioned a tab control so I am ignoring that possibility.)

I never use macros, but I think you need something more
like:
Risk.Form.Assertion.Form.AssertionSelect

 

[TRose]

You are correct. I have that macro on the Current event, not the Change event.

I tried your syntax (and a couple of variations thereon) and I still get an
error that the control doesn't exist. Can you suggest some code that might
accomplish this? I'm not against using code instead of a macro, but I'm just
not familiar with the VB programming in Access.

Thank you for your help.

Graham and Marshall:

Thank you for your replies, but I'm not having any success with your
suggestions. Let me try to be more specific to see if that would help.

My main form is named Account and the information on this form is brought
forward from existing data so no data is entered here. This is why I have
the macro on the Change event and not the AfterUpdate event. One account is
shown on each record of the form and has been previously identified with
certain assertions.

The change event is triggered for each character that is
typed into the control (which control?) This seems to be
totally inappropriate. If you want the subform's combo box
to sync with the main form as you navigate through the main
form records, then use the main form's Current event.

The subform to the Account form is named Risk (data is entered here). The
subform to the Risk subform is named Assertion and this includes the
AssertionSelect combo box that isn't filtering to the specific assertions
based upon the account selected in the Account form. (Yes, these are the
names of the controls and not the names of the underlying objects.)

For example, out of 13 possible assertions, account #1 may have been
identified with 5 assertions and account #2 with 7 assertions. When data is
entered in the Risk subform for account #1, I want the user to select from
the list of 5 assertions that account. When the user goes to the next
account (#2), the list of assertions should change to the 7 for that account.
And so on ...

So, based upon your suggestions, I created a macro and added the Requery
action for the following controls:

Graham: Account![Risk].Form![Assertion].Form![AssertionSelect]
Marshall: Account.Risk.Form.Assertion.AssertionSelect

This macro was attached to the Change event on the Account form, but both
options generated errors that the control didn't exist.

Something about your words does not ring true. AFAIK, forms
do not have a change event. Only text and combo boxes have
a change event. (Well, tab control do too, but you haven't
mentioned a tab control so I am ignoring that possibility.)

I never use macros, but I think you need something more
like:
Risk.Form.Assertion.Form.AssertionSelect

 

[Marshall Barton]

You are correct. I have that macro on the Current event, not the Change event.

I tried your syntax (and a couple of variations thereon) and I still get an
error that the control doesn't exist. Can you suggest some code that might
accomplish this? I'm not against using code instead of a macro, but I'm just
not familiar with the VB programming in Access.

Like I said, I am not familiar with the idiosyncrasies of
macros, but I would take the error message for what it says
and triple check the names of all the controls in the
reference.