Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2019, 11:08 AM   #1
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 32
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Table adding records to another table

We have a table that is an ODBC connections from our erp system. Any time a new sales order is added into the erp system I need the ODBC table to also create that record on another table. We will call it Engineering sales order table. I've googled and cannot find this answer. Is there a way to set this up?

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:17 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Table adding records to another table

Hi. Not sure I understand but an ODBC connected table, would indicate the table is stored in the ERP system. If this is the case, and you want the ERP system to duplicate data in this table, which is also in the ERP, wouldn't you need to have the ERP do it?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-18-2019, 11:24 AM   #3
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 32
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

The ERP system contains all the info about the order. But for instance another table (sorted by the same sales order) would contain info about which engineer was working on it for example. Obviously in the erp system when a new order is entered there won't be a record for that sales order (so 28283) on the engineering sales order table. I want to get the engineering sales order table to add the new record (so 28283) once it's entered into the erp system or the ODBC connection.

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:34 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
The ERP system contains all the info about the order. But for instance another table (sorted by the same sales order) would contain info about which engineer was working on it for example. Obviously in the erp system when a new order is entered there won't be a record for that sales order (so 28283) on the engineering sales order table. I want to get the engineering sales order table to add the new record (so 28283) once it's entered into the erp system or the ODBC connection.
Hi. Please remember we don't have any visibility to your environment, so what could be "obvious" to you may not be so obvious to us. For example, I have no idea if the engineering sales order table is stored in the ERP system or not. Is it?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-18-2019, 11:45 AM   #5
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 32
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

I apologize for that. No it is not in the ERP system. It is in the access file that we are building a database off of. The access file has the linked ODBC connections and it also has a table that I've made inside it that contains the engineering data.
chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 11:50 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
I apologize for that. No it is not in the ERP system. It is in the access file that we are building a database off of. The access file has the linked ODBC connections and it also has a table that I've made inside it that contains the engineering data.
Okay, thanks for the clarification. So, let's say the ERP table gets a new record, how would your Access table know it? The only thing I could think of is to manually execute a find unmatched query against the local and linked table; and if there's a mismatch, you can execute an APPEND query to the local table. So, someone could execute this process, or you could set it up to run every so often, or only when the db opens.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
chriscardwell06 (06-18-2019)
Old 06-18-2019, 12:01 PM   #7
chriscardwell06
Newly Registered User
 
Join Date: Aug 2011
Location: Tennessee
Posts: 32
Thanks: 2
Thanked 0 Times in 0 Posts
chriscardwell06 is on a distinguished road
Re: Table adding records to another table

That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.

chriscardwell06 is offline   Reply With Quote
Old 06-18-2019, 12:02 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.
Sounds like a plan. Good luck. Let us know how it goes...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-18-2019, 12:16 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,671
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Table adding records to another table

Quote:
Originally Posted by chriscardwell06 View Post
That might do what I need it to do. Anytime a user opens a specific form I could make it execute those steps and add the new order. I'll give that a try.
Might be less process intensive if you save the last retrieved number and retrieve all records with a number greater than that, and update at the end.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 07-11-2019, 10:48 AM   #10
KHallmark
Newly Registered User
 
Join Date: Jul 2019
Posts: 13
Thanks: 0
Thanked 1 Time in 1 Post
KHallmark is on a distinguished road
Re: Table adding records to another table

You will need to create a form for the main table and then click on the other table and drag it onto the form. Follow the prompts until you get to the choice to select fields or create your own and click ok. A subform is created automatically for this.

Then right click on any open space, click 'Properties'

go to link master fields, put your fields you want to match, ex. First name;Last Name; Cell Phone.... there needs to be a ; between each field in Link Master Fields properties and Link Child Fields. Save and close and now every time you create a new record, you will have to use the subform on the main form to create the new records. Basic data will be automatically copied over and then you can add the additional data.

KHallmark is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding records to a table plawrence85 Tables 4 09-23-2008 10:26 AM
Adding new records in one table & adding to other tables. Rusty Forms 1 04-29-2004 07:45 AM
Adding Records to a Table saross Modules & VBA 3 08-05-2003 01:59 AM
Adding Records to a table ciao Modules & VBA 3 06-15-2002 12:23 PM
[SOLVED] Adding Records to Table AngelaMC Forms 1 11-14-2000 01:30 PM




All times are GMT -8. The time now is 03:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World