Subform requery when combo box changes

[sweyer via AccessMonster.com]

I have a form named Employees. Each employee is tied to the company they work
for - I call them Groups. We are an insurance broker. So each employee has a
list of policies, each policy is tied to an Employee and a Group. So inside
the form Employees, I have a subform (datasheet) named EmployeePolicies. When
a Group is chosen in a combo box, the subform is supposed to query to find
the Policies that are only tied to that Group. (I have a field called
GroupPolicy - a combo box in the subform, and for each employee you would
choose the GroupPolicy that the employee has elected, and then type in their
ContractNo, EffectiveDate, CoverageType, etc.). So basically what I would do
is choose a group, and when I enter the subform, I would click the arrow next
to GroupPolicy and that Group's Policies would show up.

Anyway, the problem I am having is this: When you choose the FIRST Group it
works fine. But say you accidently choose the wrong group, and need to select
a different group, it doesn't requery for the second group, it still show's
the information for the first Group. How can I get the subform to requery
EVERY time the Group field is updated.

Also, I don't know if this makes a difference, but this problem didn't come
about until I changed the BeforeUpdate property of both the parent and child
forms. It asks the user if they want to save, if yes, save, if no, undo.

Any ideas?

 

[RuralGuy]

I have a form named Employees. Each employee is tied to the company they work
for - I call them Groups. We are an insurance broker. So each employee has a
list of policies, each policy is tied to an Employee and a Group. So inside
the form Employees, I have a subform (datasheet) named EmployeePolicies. When
a Group is chosen in a combo box, the subform is supposed to query to find
the Policies that are only tied to that Group. (I have a field called
GroupPolicy - a combo box in the subform, and for each employee you would
choose the GroupPolicy that the employee has elected, and then type in their
ContractNo, EffectiveDate, CoverageType, etc.). So basically what I would do
is choose a group, and when I enter the subform, I would click the arrow next
to GroupPolicy and that Group's Policies would show up.

Anyway, the problem I am having is this: When you choose the FIRST Group it
works fine. But say you accidently choose the wrong group, and need to select
a different group, it doesn't requery for the second group, it still show's
the information for the first Group. How can I get the subform to requery
EVERY time the Group field is updated.

Also, I don't know if this makes a difference, but this problem didn't come
about until I changed the BeforeUpdate property of both the parent and child
forms. It asks the user if they want to save, if yes, save, if no, undo.

Any ideas?

Do you have the LinkChild/MasterFields properties of the SubFormControl set up
correctly?
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[sweyer via AccessMonster.com]

Yes. There are two fields tying the forms together. GroupID and EmployeeNo.
They have the same name on both the form and the subform. Both the Link
Child Fields property and the Link Master Fields property are set to:
GroupID;EmployeeNo.

I have a form named Employees. Each employee is tied to the company they work
for - I call them Groups. We are an insurance broker. So each employee has a

[quoted text clipped - 19 lines]

Any ideas?

Do you have the LinkChild/MasterFields properties of the SubFormControl set up
correctly?
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[sweyer via AccessMonster.com]

The thing is that it works fine the first time you choose a group. It just
doesn't requery whenever you choose a different group. Even if you are on a
different record on the parent form.

Yes. There are two fields tying the forms together. GroupID and EmployeeNo.
They have the same name on both the form and the subform. Both the Link
Child Fields property and the Link Master Fields property are set to:
GroupID;EmployeeNo.
[quoted text clipped - 7 lines]

hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

The thing is that it works fine the first time you choose a group. It just
doesn't requery whenever you choose a different group. Even if you are on a
different record on the parent form.

Is it the ComboBox on the MainForm of the SubForm that isn't working as you
expect? Does the MainForm have an EmployeeNo control/field?
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[sweyer via AccessMonster.com]

Yes the main form has an EmployeeNo field...that's working perfectly.

The problem is that you choose a group on the main form, and it doesn't
requery the subform to match the new group you chose. It works the very first
time after you open the main form, but after that, it keeps showing the
information for the first group you chose, and not necessarily the actual one
that is chosen. So basically, the subform querys once for that first group,
and no matter what record you are on, no matter what group is chosen, it
doesn't requery again - just shows the first group's information the whole
time.

 

[sweyer via AccessMonster.com]

Never mind....got it.

I had to force a requery on a field in the subform instead of on the subform
itself. The field GroupPolicies just needed to be updated every time you
updated a Group.

Thanks anyway!

Yes the main form has an EmployeeNo field...that's working perfectly.

The problem is that you choose a group on the main form, and it doesn't
requery the subform to match the new group you chose. It works the very first
time after you open the main form, but after that, it keeps showing the
information for the first group you chose, and not necessarily the actual one
that is chosen. So basically, the subform querys once for that first group,
and no matter what record you are on, no matter what group is chosen, it
doesn't requery again - just shows the first group's information the whole
time.
[quoted text clipped - 5 lines]

hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

Never mind....got it.

I had to force a requery on a field in the subform instead of on the subform
itself. The field GroupPolicies just needed to be updated every time you
updated a Group.

Glad to hear you got it sorted. Thanks for posting back.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.