• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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

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
Satish Kumar G N
Asked:
Satish Kumar G N
  • 5
  • 5
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Satish Kumar G NStudent Author Commented:
Hi crystal, can I get a more precise answer for the same? using an example? or considering my tables ?

thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Satish Kumar G NStudent Author Commented:
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
 
Satish Kumar G NStudent Author Commented:
Hi Crystal,
a gentle remainder regarding the screenshots asked by you. Kindly help me with the same.

Regards,
Satish Kumar G N
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Satish -- I will look at this later today
1
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Satish Kumar G NStudent Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
if you are done with this question, please close it. Thank You.
0
 
Satish Kumar G NStudent Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now