Refresh data in sub forms when one sub form is edited

[Michael]

I have a main form that has a number of sub-forms. Some of the
subforms are different views of the same table. For example, my main
form is a form for tracking service calls we call "Tickets" which
pulls from the table "Tickets". There are 2 particular subforms,
"Accounts Receivable" and "Accounts Payable" that both pull their data
from the table "Accounting" via respective queries "Accounts Payable"
and "Accounts Receivable". These forms are linked by "TicketNumber".
We use a separate subform for Accounts Receivable and Accounts Payable
to enter all cost data on one form and all billable data on another
sub form without getting the 2 confused and to avoid having an
excessively long horizontal scroll in the subforms.

Currently, when I edit the data on one subform and then go to the
other subform, we have to somehow refresh things to be able to have
the edits from the first sub form show up on the secondary subform. I
usually just move to another record and then back or un-filter and
then re-filter.

Is there a way that, when we edit one subform or the other that they
will update/refresh/requery so that the other sub form views are
updated as well and don't give us that "another user has edited this
record" error?

 

[Marshall Barton]

I have a main form that has a number of sub-forms. Some of the
subforms are different views of the same table. For example, my main
form is a form for tracking service calls we call "Tickets" which
pulls from the table "Tickets". There are 2 particular subforms,
"Accounts Receivable" and "Accounts Payable" that both pull their data
from the table "Accounting" via respective queries "Accounts Payable"
and "Accounts Receivable". These forms are linked by "TicketNumber".
We use a separate subform for Accounts Receivable and Accounts Payable
to enter all cost data on one form and all billable data on another
sub form without getting the 2 confused and to avoid having an
excessively long horizontal scroll in the subforms.

Currently, when I edit the data on one subform and then go to the
other subform, we have to somehow refresh things to be able to have
the edits from the first sub form show up on the secondary subform. I
usually just move to another record and then back or un-filter and
then re-filter.

Is there a way that, when we edit one subform or the other that they
will update/refresh/requery so that the other sub form views are
updated as well and don't give us that "another user has edited this
record" error?

If you are adding or deleting records in the first subform,
try using a line of code in the first subform's Form
AfterUpdate event procedure:
Parent.othersubform.Form.Requery

If you are only editing existing records in the first
subform, then you can get away with:
Parent.othersubform.Form.Refresh

 

[Michael]

There are times that we edit existing and delete/add records from
either of the 2 subforms. I'm assuming in that case we should just
use and after update event to requery to cover both scenarios? I
would put that after update on the property of the subform itself
right?

 

[Marshall Barton]

There are times that we edit existing and delete/add records from
either of the 2 subforms. I'm assuming in that case we should just
use and after update event to requery to cover both scenarios? I
would put that after update on the property of the subform itself
right?

If you want the other subform to pick up the changes
immediately, then use the subform's AfterUpdate event.

If you don't care about about the other subform being
updated until you are done making changes (e.g. other
subform is in a different tab page), then you can wait until
the subform control's Exit event.

Be aware that the requeried subform will have its first
record as the current record. If that's a problem, you can
use more code to remember the other subform's current
record's PK field, do the Requery, and then use FindFirst to
position back to the record that was current before the
Requery.

 

[Michael]

Ok, so I got all that to work but now I have another followup. We use
different forms to auto-populate information in the background so we
can avoid repetitive entry. I used the refresh idea you gave me to
refresh using: Forms!NewTicket867.Refresh but to make this work I
would have to customize this code for every Client New Ticket form we
have (NewTicket866, NewTicket867, NewTicket868, etc.).

I would like to find a way for the refresh statement to be universal
so we don't have to customize it for each client form. For example,
we would like the refresh statement to read Forms!
NewTicket(clientID#).Refresh so that it will automatically pull the
ClientID# out of the clientID field and refresh the form we are
currently working which would be NewTicket867.

 

[Marshall Barton]

Ok, so I got all that to work but now I have another followup. We use
different forms to auto-populate information in the background so we
can avoid repetitive entry. I used the refresh idea you gave me to
refresh using: Forms!NewTicket867.Refresh but to make this work I
would have to customize this code for every Client New Ticket form we
have (NewTicket866, NewTicket867, NewTicket868, etc.).

I would like to find a way for the refresh statement to be universal
so we don't have to customize it for each client form. For example,
we would like the refresh statement to read Forms!
NewTicket(clientID#).Refresh so that it will automatically pull the
ClientID# out of the clientID field and refresh the form we are
currently working which would be NewTicket867.

Do you really have a separate form for each client?? What
kind of a magical genie do you have that can keep up with
changes to all those forms??? Maybe you should read up on
using multiple instances of a single form at
http://allenbrowne.com/ser-35.html

Anyway, to answer your question:
Forms("NewTicket" & Me.clientID).Refresh

 

[Michael]

These forms are for clients who give us multiple tickets, not for our
entire customer base so they don't change very often therefore the
magical genie was laid off LOL.

Thanks for your help. Your suggestion worked well. I know just
enough to get myself into situations where I know something is
possible I just have trouble figuring out how.

 

[Michael]

Ok, so how do I incorporate the find first function? I've noticed
that when we edit the subform, the parent form goes to a completely
different record and of course our users are not always noticing so
they are continuing edits in a completely different record.

Here is what I'm currently using:

Private Sub Form_AfterUpdate()
Me.Parent.Requery
End Sub

 

[Marshall Barton]

Ok, so how do I incorporate the find first function? I've noticed
that when we edit the subform, the parent form goes to a completely
different record and of course our users are not always noticing so
they are continuing edits in a completely different record.

Here is what I'm currently using:

Private Sub Form_AfterUpdate()
Me.Parent.Requery
End Sub

Well, that will definitely make the parent form's first
record the current record. Why does adding/editing a record
in the subform require the main form to be requeried?

If you need to pick up changes to the records in the main
form's recordset, are you sure you can not get by with
Refresh? The difference is that Refresh only picks up
changes to existing records made by other users where
Requery completely reloads the main form's recordsset,
picking up records added and/or deleted as well. It would
be very unusual for a subform to be adding/deleting records
used in the main form.