Update combo box in main form from last record entered in subform

[Guest]

I have a form [frmAgreement] with couple of combo boxes called
[cboPartySelect] and [cboContactSelect]. The form includes a Command Button
that opens a subform [subfrmParty] where the user can enter address
information for the party associated with the agreement listed in the main
form.

The subform [subfrmParty] also includes an embedded sub-subform
[subfrmContact] where the user can enter the names of specific contacts
associated with that party.

A given agreement can only have ONE party selected from [cboPartySelect] and
ONE contact selected from [cboContactSelect]. However, a given party can have
multiple contacts associated with it.

I have already set up an After_Update event so that when a user enters a new
party name in the subform [subfrmParty] and then closes the subform, the
party name it is automatically filled in in the control [cboPartySelect] in
the main form.

What I would like to do in addition -- and what I can't seem to figure out a
way to do -- is to automatically fill in the control [cboContactSelect] with
the most recent contact entered in the sub-subform [subfrmContact], if in
fact it gets updated. If it doesn't get updated, the box can remain empty.

I thought this would be easy to do but I just can't figure it out.

 

[John Vinson]

I have a form [frmAgreement] with couple of combo boxes called
[cboPartySelect] and [cboContactSelect]. The form includes a Command Button
that opens a subform [subfrmParty]

If it's being opened from a command button, then IT IS NOT A SUBFORM.
A Subform is a form within a Subform control on the main form; it's
opened when the main form opens with no buttons and no code. You're
describing what I'd call a "pop-up" form.

where the user can enter address
information for the party associated with the agreement listed in the main
form.

What are the Recordsources of frmAgreement and sbfrmParty?

The subform [subfrmParty] also includes an embedded sub-subform
[subfrmContact] where the user can enter the names of specific contacts
associated with that party.

A given agreement can only have ONE party selected from [cboPartySelect] and
ONE contact selected from [cboContactSelect]. However, a given party can have
multiple contacts associated with it.

I have already set up an After_Update event so that when a user enters a new
party name in the subform [subfrmParty] and then closes the subform, the
party name it is automatically filled in in the control [cboPartySelect] in
the main form.

What I would like to do in addition -- and what I can't seem to figure out a
way to do -- is to automatically fill in the control [cboContactSelect] with
the most recent contact entered in the sub-subform [subfrmContact], if in
fact it gets updated. If it doesn't get updated, the box can remain empty.

The AfterUpdate event of sbfrmContact should be the appropriate place.
What have you tried?

John W. Vinson[MVP]

 

[Guest]

Hi, John --

I have a form [frmAgreement] with couple of combo boxes called
[cboPartySelect] and [cboContactSelect]. The form includes a Command Button
that opens a subform [subfrmParty]

If it's being opened from a command button, then IT IS NOT A SUBFORM.
A Subform is a form within a Subform control on the main form; it's
opened when the main form opens with no buttons and no code. You're
describing what I'd call a "pop-up" form.

Okay, I see what you're saying.

What are the Recordsources of frmAgreement and sbfrmParty?

I'm not totally sure of what you mean by Recordsources -- do you mean the
underlying tables?
I have tables called tblAgreement (for [frmAgreement]), tblParty (for pop-up
form [subfrmParty]), and tblPartyContact (for subfrmContact, embedded in
pop-up form [subfrmParty]).
The main table, tblAgreement, is linked to the other two tables via
ContactID and PartyID.
Like I said, a given record in tblAgreement can only have ONE PartyID and
ONE ContactID associated with it.
The parties in [tblParty] are all unique.
However, a given party may have multiple contacts associated with it in
[tblPartyContact].

The subform [subfrmParty] also includes an embedded sub-subform
[subfrmContact] where the user can enter the names of specific contacts
associated with that party.

A given agreement can only have ONE party selected from [cboPartySelect] and
ONE contact selected from [cboContactSelect]. However, a given party can have
multiple contacts associated with it.

I have already set up an After_Update event so that when a user enters a new
party name in the subform [subfrmParty] and then closes the subform, the
party name it is automatically filled in in the control [cboPartySelect] in
the main form.

What I would like to do in addition -- and what I can't seem to figure out a
way to do -- is to automatically fill in the control [cboContactSelect] with
the most recent contact entered in the sub-subform [subfrmContact], if in
fact it gets updated. If it doesn't get updated, the box can remain empty.

The AfterUpdate event of sbfrmContact should be the appropriate place.
What have you tried?

What I tried was writing an AfterUpdate event, but not for subformContact as
a whole; since I wanted the combo box on the main form to update according to
the LAST contact name entered in subfrmContact, I wrote the AfterUpdate event
for the control under subformContact called [FirstName], so that

Private Sub FirstName_AfterUpdate ()

Forms![frmAgreement]![cboContactSelect] = Me![ContactID]
Forms![frmAgreement]![cboContactSelect].Requery

This is probably a mess, I know, but it was the best idea I could come up
with.