Solved

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

Posted on 2016-09-13
11
44 Views
Last Modified: 2016-10-04
Hi,
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:
Eg:
Edit Record :
              Set Field :
                       Name: User Entry- Planning Data.SAP ID
                       Value: [User Entry - Primary Data].[SAP ID]

it gives me following error

Error--Edit-Record.png  
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.


Thanks

Kind Regards,
Satish Kumar G N
0
Comment
Question by:Satish Kumar G N
  • 5
  • 5
11 Comments
 
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.
0
 
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:
https://channel9.msdn.com/Events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps
0
 
LVL 1

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 ?

thanks
0
 
LVL 19

Accepted Solution

by:
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
ie:
UserEntryPlanningData
SapID
0
 
LVL 1

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.

Table-1.png
Table-2.png
View-1-.png
View-2-.png
After-Insert-Macro.png

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

Thanks

Regards,
Satish Kumar G N
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

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.

Regards,
Satish Kumar G N
0
 
LVL 19
ID: 41806580
thank you, Satish -- I will look at this later today
1
 
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!
1
 
LVL 1

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.
0
 
LVL 19
ID: 41829199
if you are done with this question, please close it. Thank You.
0
 
LVL 1

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
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Documentation is a big contentious issue in Agile. There is a reason for this. When you start your presentation on Agile you start by going through the 4 statements of agile manifesto (https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

762 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

17 Experts available now in Live!

Get 1:1 Help Now