Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2016-09-13
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 85
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 22
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 ?

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

LVL 22

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 2000 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

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 22
ID: 41806580
thank you, Satish -- I will look at this later today
LVL 22
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 22
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Introduction to Processes

722 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