Acess 2003 - Form / SubForm linked fields

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

I have a form (header) with a subform (lines) linked to each other by four
fields.
My problem is when I need to change one field in the form (header)
automatically the record in the subform disapears.
I need a way to change the fields linked both in the form and subform at
same time in order to update the records and not lose the line record because
a linked field changed in the header.
Thank you.
 
I have a form (header) with a subform (lines) linked to each other by four
fields.
My problem is when I need to change one field in the form (header)
automatically the record in the subform disapears.
I need a way to change the fields linked both in the form and subform at
same time in order to update the records and not lose the line record because
a linked field changed in the header.
Thank you.
Click to expand...

What are these four fields? It sounds like you may be storing data
redundantly, if you need it to change in both tables!

Do you have a relationship defined between the tables with cascade updates
set?
 
Dont have relationships in the tables between those 4 fields because the
lines table is also used independently from the header table. The fiels are
doc number, type of doc, date of doc and code of client. The headers and
lines tables are used to store vending docs and those 4 fields link header
and lines (one header to one or more lines). In the form subform when I need
to change one of those 4 fields, the line record in the subform automatically
disapears when one of the linking fields change.
I think the data is not redundant but with no cascade relationship I need a
way to update the line fieds when the linking header field change before it
disapears from the subform.
thank you.
 
Dont have relationships in the tables between those 4 fields because the
lines table is also used independently from the header table. The fiels are
doc number, type of doc, date of doc and code of client. The headers and
lines tables are used to store vending docs and those 4 fields link header
and lines (one header to one or more lines). In the form subform when I need
to change one of those 4 fields, the line record in the subform automatically
disapears when one of the linking fields change.
I think the data is not redundant but with no cascade relationship I need a
way to update the line fieds when the linking header field change before it
disapears from the subform.
thank you.
Click to expand...

Sorry... but the data IS redundant, at least from your description.

One of the tables - the header, I'd guess - contains information about the
document as an entity... right? The type of doc and the date of doc are
attributes *of the document*, and as such those field should exist in the
table containing the unique document ID, *and noplace else*.

Could you explain why you feel that you must store the date of the document in
both tables? If the date is always to be the same in both tables, then my case
is made (it's redundant, it should only be in one); if it can be different in
the two tables, then it shouldn't be used in the link fields.
 
The date is the same in both tables. All those 4 fields are key fields in
both tables.
The data in both tables (Headers and lines) can be as folows:

Type_Doc, Number_Doc, Date, Cod_Client
F 1 05-05-08 100
NC 1 05-05-08 100
ND 1 05-05-08 100
R 1 05-05-08 100

Is there a way in the form/ sub form to change automatically (or by code)
the field changed in the form to be changed also in the subform.
Thank you.
 
The date is the same in both tables. All those 4 fields are key fields in
both tables.
The data in both tables (Headers and lines) can be as folows:

Type_Doc, Number_Doc, Date, Cod_Client
F 1 05-05-08 100
NC 1 05-05-08 100
ND 1 05-05-08 100
R 1 05-05-08 100

Is there a way in the form/ sub form to change automatically (or by code)
the field changed in the form to be changed also in the subform.
Thank you.
Click to expand...

AGAIN: please reread my post.

The date should NOT EXIST in the child table (based on what you've posted,
maybe there's information you have not posted which of course I do not know).

I'll assert that your table design is the problem.

Cascade updates will cascade changes "down" (i.e. changing the date in the
parent table will change it in the child table, if the date is one of the
fields in the enforced relationship), but I know of no way other than
brute-force VBA code to cascade *up* - and I cannot imagine any reason why you
would need to do so with properly designed tables.

If I'm misunderstanding, please explain why the date field (and for that
matter the COD Client field) needs to exist in both tables.
 
Back
Top