Unwanted reference field updates when combining multiple documents...help?

[DaVinney]

Here is the simplified version of my issue:

Say I have two tables (Table1 and Table2) in Doc1 (a protected form).

I want contents of a cell in Table2 to duplicate textual contents of a corresponding cell in Table1.

In one approach, I use a text formfield in the Table1 cell (w/ Calculate on Exit). I use a Cross-reference field in the Table 2 cell to reference the Bookmark-Text.

Works okay, BUT here is my problem:

This form (Doc1) will be duplicated 2 or more times (Doc2, Doc3, etc.) as each separate file (form) will have different data entered into each respective Table1. Then, once each form is filled out, all the individual docs will be combined into a single document, composed of Doc1+Doc2+Doc3, etc.

Problem is, all the contents of the subject Table2 cell from each of the formerly individual docs will be the same as the Table1 cell in the first doc in the combined document (Doc1 in this example). Of course, this is highly problematic :eek:

Please offer any solutions or approaches to help me remedy this :think:. I will explain further if this in not clear. Thanks.

[Tinbendr]

I think I would, instead of a ref field, when the Exit event fires, write the info to the Table 2 cell directly.

[DaVinney]

Thanks for the reply, Tinbendr.

In the exit firing macro, how would I identify the cell in Table2 which is to receive the information? In that approach, wouldn't I run into the same problem after the multiple docs are combined into one doc if the user decides to edit the Table1 information?


I'm also pondering a solution that involves modifying the reference fields sequentially somehow within the VBA routine that combines the multiple documents after the individual forms are completed.

Hope to get more help with this :bug:

[Tinbendr]

In that approach, wouldn't I run into the same problem after the multiple docs are combined into one doc if the user decides to edit the Table1 information?
Hmm, yes, you're right. :doh:
You could have a Userform take in the info and write it to each table statically, but if they need to edit it, then it starts to become a can-of-worms.

I'm also pondering a solution that involves modifying the reference fields sequentially somehow within the VBA routine that combines the multiple documents after the individual forms are completed.Yes, this was what I was probably thinking. Merge to a Final document with all the info static.

[DaVinney]

I decided to take advice from Tinbendr and send the Table1 cell text to the corresponding Table2 cell instead of using a reference, taking advantage of the position relationship that Table2 always follows Table1. So I'm able to "reference" it in that way and avoid the unwanted updates while maintaining the ability to edit the information in Table1 and have Table2 update correctly.

Not ideal, but seems to work okay.