Access Webapp : Using After Update Event in & Retain Macro Values till Changed.

Posted on 2016-09-13
Last Modified: 2016-10-04
I have 2  Tables  namely

User Entry - Primary Data with Fields ( SAP ID, Project Name , Project Manager, Group Head)
User Entry - Planning Data Fields(SAP ID, Project Manager, Project Name Group Head etc & many more other than Primary Data)

Fields of
User Entry - Planning Data Fields(SAP ID, Project Manager, Project Name Group Head)

are Updated with After Insert Event: MACRO (Create Record) in

User Entry - Primary Data with Fields ( SAP ID, Project Name , Project Manager, Group Head)

Successfully the data From User Entry - Primary Data gets Inserted to User Entry - Planning Data.

Problems what I am Facing is
1. Once after Inserting a data if I want to UPDATE any data in User Entry - Primary Data it doesn't Get updated the same in User Entry - Planning Data.

I tried After Update:
Edit Record :
              Set Field :
                       Name: User Entry- Planning Data.SAP ID
                       Value: [User Entry - Primary Data].[SAP ID]

it gives me following error

2. When I insert Values in User Entry - Primary Data it just remains for one record in User Entry - Planning Data.

After saving that record if I want to add a new Record it all the values will be blank, where as I want those values which are there in User Entry - Primary Data till I change them.


Kind Regards,
Satish Kumar G N
Question by:Satish Kumar G N
  • 5
  • 5
LVL 84
ID: 41797712
From what I can gather, you want to be able to UPDATE data in your child tables when data in the Parent tables change? So when you change the ProjectName in the first table, you want to update the ProjectName in your second table?

If so, then you shouldn't be handling your data in this manner. Access is a relational database system, and you should be storing data in a relational manner. Take the two tables you mentione, for example:

[User Entry - Primary Data] with Fields ( SAP ID, Project Name , Project Manager, Group Head)
[User Entry - Planning Data] Fields(SAP ID, Project Manager, Project Name Group Head etc & many

Assuming you're storing data about a Project, then you should have distinct tables for Project, ProjectTasks, ProjectEmployees, Employees, etc. You should not be storing duplicated data across multiple tables. Instead, you should "relate" tables appropriately, and your updates will be immediately recognized.

I would suggest you read up on database design before getting too much further into your project. There are many web resources to learn about table structures, normalization, etc.
LVL 19
ID: 41798301
use SetLocalVar to capture the value. SetLocalVar LocalVariableName =value.
Then in the edit record context, reference the local variable name:
Value =[LocalVariableName]

Here is a presentation on Access Web Apps that you may find helpful:

Author Comment

by:Satish Kumar G N
ID: 41801353
Hi crystal, can I get a more precise answer for the same? using an example? or considering my tables ?

LVL 19

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41801637
to do that, I need more information :

paste screenshot of the whole macro
paste screenshots of your table designs
paste screenshot of your view
paste screenshot of the source for your view

At as general rule:
don't use spaces or special characters such as "-" in names ... instead, use upper and lower case

Author Comment

by:Satish Kumar G N
ID: 41804420
Hi crystal,
thanks for your reply. Here are the screenshots I am attaching as asked by you. If you need anymore please do let me know.


Note: There are 2 problems I am facing as described above in my first question post. kindly reply for both.


Satish Kumar G N
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

by:Satish Kumar G N
ID: 41805964
Hi Crystal,
a gentle remainder regarding the screenshots asked by you. Kindly help me with the same.

Satish Kumar G N
LVL 19
ID: 41806580
thank you, Satish -- I will look at this later today
LVL 19
ID: 41807791
half the fields from UerEntryPrimaryData are repeated in UserEntryPlanningData.  This, as Scott already pointed out, is not necessary! It is also bad design because you are not taking advantage of using a relational database. The UserEntryPlanningData table needs to  store the ID from UserEntryPrimaryData as a foreign key (name it something like PrimaryDataID so you know what it relates to). When you define this field, choose Lookup Field for the data type  (even though that is really not a data type) and follow the wizard steps.  Then Access will automatically link the Primary and Foreign Key values and you can retrieve the contents of all the fields you are now duplicating. It will be much easier!

So how can you see the values from the primary table along with data from the related table? By defining a query using both tables in the source.

In UserEntryPrimaryData (such long table names! better to use short names -- and everything in a table is Data so not necessary to include in the name), is SAP ID a unique field?

The best way we can help is by helping you have a better design first ... and then with the macros. Please don't be discouraged by this advice -- it is common for folks to get started building a database without knowing much about designing one.  Access is a very powerful tool. We want you to build good databases and be successful!

Author Comment

by:Satish Kumar G N
ID: 41829192
hello I have actually created a new question Access Webapp: Table to Query to Table

there in comments I have mentioned what I am looking for, could someone help me with the same.
LVL 19
ID: 41829199
if you are done with this question, please close it. Thank You.

Author Closing Comment

by:Satish Kumar G N
ID: 41829205
thanks for your continuous help and follow-up crystal, i have raised a new question in Webapp: Table to Query to Table

there i jave briefed in a comment , if you cold have a look and help me with the same. thanks

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now