Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

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.

Thanks,
Bob C.
Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
Avatar of Bob Collison

ASKER

Hi Pat,

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

Explanation
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.

Solution
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?

Thanks.
Bob C.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I appreciate Pat's comments however the 'solution' wasn't what I implemented.