Go Premium for a chance to win a PS4. Enter to Win


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

916 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