How to update one form when closing another ?

[Mel]

I have two forms open.

frmPeople has the Focus
frmAccount does not.

frmAccount has subFormPeopleList

I add a new person to frmPeople (which is linked to frmAccount)

Question 1:

When I close frmPeople I want to update frmAccount so the new person
shows in subFormPeopleList

Quetion 2:

But I'm also wondering if I add code to the "close event" for
frmPeople to update frmAccount

What will this do if frmAccount is not open? Will it add the person
twice? I'm guessing not as updating a form doesn't really add data to
the table... right?

Thanks for any help.

Mel

 

[Guest]

Mel,
You could check to see if the form is loaded as follows...

If CurrentProject.AllForms("Suppliers").IsLoaded = True Then
your code here...
End If

And your data won't be added twice because it's written to the table directly.

Maurice

 

[Mel]

Thanks Maurice.

That still leaves the original question...

How to update one form when closing another?

thanks for any help.

Mel

 

[Larry Linson]

That still leaves the original question...
How to update one form when closing another?

You don't "add records _to_ a Form", which is the implication here. In
Access, you add data to Tables -- you may use a Form to accomplish this.
Your question might lead one to believe that this was not clear to you. If
so, you might consider looking at the Training links you'll find at
http://office.microsoft.com or investing in a self-study book such as
"Access <version number> Step by Step" from Microsoft Press.

(1) Is your frmPeople bound to a "People" table (that is, the Record Source
property of the Form refers directly to the People table, or to a Query
against that Table)? If so, the new record entered on the Form will be
written to the People table when the Form is closed, when you take specific
action to update, or when you move the focus into a Subform Control on that
Form, but not before.

(2) Are _all_ the records from the People table displayed in the Subform
subFormPeopleList? If, as is often the case, only those Records related to
the main Form in which the Subform Control exists are displayed, based on
the Subform Control's LinkMasterFields and LinkChildFields, the newly-added
Record may or may not be one which will be displayed.

(3) If both the above questions are "Yes." then you can

(a) You should not need to do anything to cause the Record to be written to
the People table, because it will automatically be written if <1> you move
to another Record, <2> close the Form, or <3> move the focus to a Subform
Control on the Form, but you haven't indicated a Subform on frmPeople. But,
unless you do one of these things, or click a Control (probably a Command
Button), there is no Form event in which you can put Me.Dirty = False to
force the update.

So, if the only condition under which you will want this to happen is when
you Close the Form, you need do nothing for a bound Form because that is
when the Record will be written, anyway.

(b) Then, if the frmAccount is open, you can cause it to display the current
data from the "People" table with:

On Error Resume Next
Forms![frmAccount].subFormPeopleList.Requery
On Error <add your normal error handling code here>

You only want to Requery the Subform Control if frmAccount is open -- the
"OnError Resume Next" simply ignores the error that will be raised if it is
not. An appropriate Event in which to include _this_ code would be the
Form's AfterUpdate event.

Larry Linson
Microsoft Access MVP