Subform issue

[Guest]

I have a main form "Customer" and a subform "Address". They are linked using
"CompanyName" field.

The record source of the main form is a table (Customers). The record source
of the subform is a query based on another table (Addresses).

"CompanyName" is the primary key of the Customers table. The Customers table
has a one-to-many relationship with the Addresses table on "CompanyName" with
RI and cascade updates.

Everything displays and updates properly on the main form EXCEPT when a
change is made to "CompanyName." Then the fields in the subform go blank.
I've tried Requery and Refresh everywhere I can think of. The Address subform
remains blank until I move to another record on the main form and then back.

Please help.

Access 2003.

 

[Allen Browne]

Hmm. Timing problem. Access realizes the names in the subform don't match
and clears the subform, but this happens before the cascading update after
which they do match again.

Did you try to requery the subform in the Afterupdate event procedure of the
main form? This kind of thing:

Private Sub Form_AfterUpdate()
Me.[Sub1].Form.Requery
End Sub

If that still fails, you could add a DoEvents ahead of the Requery.

If you get it solved, it would be good to limit the Requery to only those
cases where the name actually changed, to avoid unnecessary fetches. That
would mean creating a form-level boolean variable (General Declarations
section of the form's module), setting it in Form_BeforeUpdate (where you
can read the OldValue), and testing it in Form_AfterUpdate.

 

[Guest]

Thanks. It never fails, though, that as soon as I post an issue I discover a
solution.

I added a Save command to the AfterUpdate event before the requery of the
subform. Then I requeried the entire main form as there are several other
subforms that were affected as well.

That may not be the best solution in a large database with many users, but
it works fine in my situation.

Hmm. Timing problem. Access realizes the names in the subform don't match
and clears the subform, but this happens before the cascading update after
which they do match again.

Did you try to requery the subform in the Afterupdate event procedure of the
main form? This kind of thing:

Private Sub Form_AfterUpdate()
Me.[Sub1].Form.Requery
End Sub

If that still fails, you could add a DoEvents ahead of the Requery.

If you get it solved, it would be good to limit the Requery to only those
cases where the name actually changed, to avoid unnecessary fetches. That
would mean creating a form-level boolean variable (General Declarations
section of the form's module), setting it in Form_BeforeUpdate (where you
can read the OldValue), and testing it in Form_AfterUpdate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a main form "Customer" and a subform "Address". They are linked
using
"CompanyName" field.

The record source of the main form is a table (Customers). The record
source
of the subform is a query based on another table (Addresses).

"CompanyName" is the primary key of the Customers table. The Customers
table
has a one-to-many relationship with the Addresses table on "CompanyName"
with
RI and cascade updates.

Everything displays and updates properly on the main form EXCEPT when a
change is made to "CompanyName." Then the fields in the subform go blank.
I've tried Requery and Refresh everywhere I can think of. The Address
subform
remains blank until I move to another record on the main form and then
back.

Please help.

Access 2003.