Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!
Course Of The Month
3 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do i fetch data from one table to another (lookup) - Related to Access Web APP
Posted on 2016-08-24
My question is related to Ms Access Webapp.
I have 2 Tables (Fields):
User Entry - Primary Data (SAP ID, Project Name, Project Manager)
User Entry - Planning Data (SAP ID (lookup from prev table), Project Name (lookup from prev table), Project Manager (lookup from prev table), many more values (not lookups from prev table))
I want to know how can I import values entered in
User Entry - Primary Data (SAP ID, Project Name, Project Manager)
to
User Entry - Planning Data (SAP ID (lookup from prev table), Project Name (lookup from prev table), Project Manager (lookup from prev table), many more values (not lookups from prev table))
what data macro to be used? or insert event or anything else.
Note: while answering please use my terms and answer please.
Thanks & Regards,
Satish Kumar G N
I have 2 Tables (Fields):
User Entry - Primary Data (SAP ID, Project Name, Project Manager)
User Entry - Planning Data (SAP ID (lookup from prev table), Project Name (lookup from prev table), Project Manager (lookup from prev table), many more values (not lookups from prev table))
I want to know how can I import values entered in
User Entry - Primary Data (SAP ID, Project Name, Project Manager)
to
User Entry - Planning Data (SAP ID (lookup from prev table), Project Name (lookup from prev table), Project Manager (lookup from prev table), many more values (not lookups from prev table))
what data macro to be used? or insert event or anything else.
Note: while answering please use my terms and answer please.
Thanks & Regards,
Satish Kumar G N
[X]
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
-
6
4
2- +1
14 Comments
Active today
Something like this:
The field lists in each part of the query need to match. Any fields not inserted into the destination table (e.g., field4), must be capable of auto-populating their value (i.e., accepts NULLs or be assigned a default value).
INSERT INTO MyOtherTable (field1, field2, field3)
SELECT field1, field2, field3 from MyOriginalTable;
The field lists in each part of the query need to match. Any fields not inserted into the destination table (e.g., field4), must be capable of auto-populating their value (i.e., accepts NULLs or be assigned a default value).
Hi Steve,
Thank you for your reply, I have referred to Ms Access Webapp, not SQL database. Please do have a check and respond back.
Kind Regards,
Satish Kumar G N
Thank you for your reply, I have referred to Ms Access Webapp, not SQL database. Please do have a check and respond back.
Kind Regards,
Satish Kumar G N
Active today
Behind every web app is a database, either SQL Server or Azure. AFAIK, to do what you want, you'll have to connect to that database to execute SQL.
Active 2 days ago
Satish,
1. Open the table On User Entry - Primary Data.
2. Click on the Design tab.
3. Hit the dropdown on "Create Data Macros"
4. Choose "After Insert"
the marco editor will open.
From the dropdown, choose "Create Record"
Enter the table User Entry - Planning Data in the "Create record In"
Then in the next drop down, choose "SetField"
Name the field.
Value is User Entry - Primary Data.<field name here>
Each field must be done separately.
I believe that will work. I don't use Web apps myself, so I'm basing this on what I've seen.
Jim.
1. Open the table On User Entry - Primary Data.
2. Click on the Design tab.
3. Hit the dropdown on "Create Data Macros"
4. Choose "After Insert"
the marco editor will open.
From the dropdown, choose "Create Record"
Enter the table User Entry - Planning Data in the "Create record In"
Then in the next drop down, choose "SetField"
Name the field.
Value is User Entry - Primary Data.<field name here>
Each field must be done separately.
I believe that will work. I don't use Web apps myself, so I'm basing this on what I've seen.
Jim.
Active 2 days ago
It's saying it cannot find "SAPID"
is that the correct field name?
Should it not be "[SAP ID]"?
Jim.
is that the correct field name?
Should it not be "[SAP ID]"?
Jim.
I solved the SAP ID Error, but it does show the error mentioned below, the access services error, if I solve that too, when I enter setfield it updated, but the data is showed in drop down list, I don't want in drop down list, it should automatically fetch the value inserted in User Entry - Primary Data . [SAP ID]
thanks
Sat
thanks
Sat
Active 2 days ago
<<I solved the SAP ID Error, but it does show the error mentioned below, >>
Really should ask a separate question for that.
But if you click on "Technical Details", what does it show you? It sounds though like it is simply the wrong data type (numeric vs text for example).
Again, I have not worked with web databases, so I may not know the answer to this. It would be better if you posted a separate question to attract the attention of others.
Jim.
Really should ask a separate question for that.
But if you click on "Technical Details", what does it show you? It sounds though like it is simply the wrong data type (numeric vs text for example).
Again, I have not worked with web databases, so I may not know the answer to this. It would be better if you posted a separate question to attract the attention of others.
Jim.
Hi Jim,
Actually what happens here is I have set
[SAP ID], [Project Name] to Short Text/Long Text then ;
Error:
Access can't convert the value of 'prj01' to the specified data type.
TECHNICAL DETAILS
Correlation ID: a9159f9d-8021-2000-ad06-23
Date and Time: 8/30/2016 10:58:51 PM
For Data Type: Number
Error:
You cannot add or change a record because a related record is required in table User Entry - Primary Data.
TECHNICAL DETAILS
Correlation ID: 09169f9d-b099-2000-ad06-21
Date and Time: 8/30/2016 11:05:27 PM
when I have normal Lookup it shows values inserted in User Entry - Primary Data as Drop down list in User Entry - Planning Data
Out Put Required :
All values in User Entry - Primary Data, Auto Filled to User Entry Planning - Data ; Not Drop Down List.
Thanks
Sat
Actually what happens here is I have set
[SAP ID], [Project Name] to Short Text/Long Text then ;
Error:
Access can't convert the value of 'prj01' to the specified data type.
TECHNICAL DETAILS
Correlation ID: a9159f9d-8021-2000-ad06-23
Date and Time: 8/30/2016 10:58:51 PM
For Data Type: Number
Error:
You cannot add or change a record because a related record is required in table User Entry - Primary Data.
TECHNICAL DETAILS
Correlation ID: 09169f9d-b099-2000-ad06-21
Date and Time: 8/30/2016 11:05:27 PM
when I have normal Lookup it shows values inserted in User Entry - Primary Data as Drop down list in User Entry - Planning Data
Out Put Required :
All values in User Entry - Primary Data, Auto Filled to User Entry Planning - Data ; Not Drop Down List.
Thanks
Sat
Active 2 days ago
I am not sure I understand what it is your trying to do here.
As I said, I have not worked with web databases at all. Let me try and find someone who has.
Jim.
As I said, I have not worked with web databases at all. Let me try and find someone who has.
Jim.
Active 2 days ago
https://support.office.com/en-us/article/Create-an-Access-app-25f3ab3e-510d-44b0-accf-b976c0813e71?ui=en-US&rs=en-US&ad=US
An Access app is a database that you use in a standard web browser, but which you design and modify in Access 2013 or later. The data and database objects are stored in SQL Server or Microsoft Azure SQL Database, so you can share the data within your organization using on-premises SharePoint 2013 or Office 365 for business.
Knowing that data seems to be stored in SQL server or Azure and the type of errors you are getting, it sounds like the data types may not be matching up.
It looks like the field in question is trying to convert text to a number.
Start by detailing your file layout with file types. Make sure the fields match up. If you have a date field, make sure you are saving date data formatted as a date. If you have an Integer field, make sure the data you are saving is an integer etc.
An Access app is a database that you use in a standard web browser, but which you design and modify in Access 2013 or later. The data and database objects are stored in SQL Server or Microsoft Azure SQL Database, so you can share the data within your organization using on-premises SharePoint 2013 or Office 365 for business.
Knowing that data seems to be stored in SQL server or Azure and the type of errors you are getting, it sounds like the data types may not be matching up.
Error:
Access can't convert the value of 'prj01' to the specified data type.
TECHNICAL DETAILS
Correlation ID: a9159f9d-8021-2000-ad06-237fc13f4fca
Date and Time: 8/30/2016 10:58:51 PM
For Data Type: Number
It looks like the field in question is trying to convert text to a number.
Start by detailing your file layout with file types. Make sure the fields match up. If you have a date field, make sure you are saving date data formatted as a date. If you have an Integer field, make sure the data you are saving is an integer etc.
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks.
Specify a start-up form through options:
Specify an Autoexec macro:
Us…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Course of the Month3 days, 6 hours left to enroll
751 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.