Table adding records to another table

[chriscardwell06]

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?

 

[theDBguy]

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?

 

[chriscardwell06]

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.

 

[theDBguy]

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?

 

[chriscardwell06]

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.

 

[theDBguy]

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.

 

[chriscardwell06]

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.

 

[theDBguy]

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...

 

[Gasman]

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

 

[KHallmark]

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.