Access 2010 Trigger Table Update For Unbound Field Change

Hi Experts,
I have a form containing data from Table 1 Field A.  The Form extracts data  from Table 2 using the Form On Current Event and displays the data in unbound Field B.  If I click a button the data is copied from Table 1 Field A to unbound Field B.  This works ok.

When I move to another record in Table 1 I want to write the data contained in unbound Field B to Table 2 before displaying the next record.

I have tried using various Form Events (Before Update / After Update / On Dirty) but the code to lookup and update the Table 2 record isn't triggered.

How can I trigger the update?
PS.  This is a very large complex application so it is not easy for me to provide a copy and there are many fields to which updates may apply on the form.

Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The code is not triggering in those events because they are only triggered when BOUND controls are dirtied and your control is not bound.

Your process doesn't make sense from a relational database perspective.  Are you really going to scroll through each and every record in table1 in order to update/add some data in table2?

Why are you duplicating data and what exactly are you trying to accomplish?   For example, are you trying to tag records for further processing?  A user examines a record and clicks to indicate further processing.  In this case, the code belongs in the click event of the button.  You might also need an OOPS! option to delete the tag you just inserted.   But you probably also need more than the PK in the tag file.  You probably need TagDate and TagBy as well.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

Thanks for your comments.  Actually I have solved the problem myself.  I will explain it below.

This Form is designed for the User to access Registration Data from an external organization (imported from Excel) and update the existing data in the Application with the latest / missing information.

The records being imported are previously filtered so that there are a reasonable number.  Table 1 is a linking table that contains the relevant data from both the imported data and the Application and is bound to the Form.  It's data only exists until the form is closed.

The form displays the data from both sources based on Surname since that is the best linkage possible.  The User browses until the correct pair of records are displayed.  The User clicks buttons to copy the data from Table 1 to the application table.  There is the provision for the 'Opps' scenario.

My solution is to add a field to Table 1 called 'TRIGGER_UPDATE'  initialized to a value of 'READ'.  When any field is updated the 'TRIGGER_UPDATE' value is set to 'UPDATE'.  When the Form moves off the Table 1 record it triggers a Table 1 Before Update Event.

If the 'TRIGGER_UPDATE' value is 'UPDATE',  the Application tables are updated with the latest keyed / selected / imported data.

The next record is then loaded Via the Form On Current Event and the 'TRIGGER_UPDATE' value is set to 'READ' to repeat the cycle.

The fact that the data isn't written until all changes have been made provides for the 'Opps' scenario.

I'm not sure what I do about awarding the points.  Could you please let me know?

Bob C.
If you are happy with your own solution and are not going to use mine, there is no need to assign points.  You can close with or without assigning points. Your choice.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor Commented:
I appreciate Pat's comments however the 'solution' wasn't what I implemented.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.