Add a new child record when master record is created.

[TomJ58]

Hi All,

I have a master table with about 40 fields. In addition, I created eight child tables with read only datasheet views of limited fields from the master table. The child table views are based on certain critera in the master table and are being refreshed by using delete and append queries tied to the On Current property of a form. By double-clicking a specific child record, a form is opened to allow editing of the master record. Also, I am using Referential Integrity and Cascade Update Related Fields to update the child table when a change is made to the master. However, I do not know how to add a record to one or more child tables when a record is added to the master table. The new child record would have to be based on the same critera that the append queries use so it gets added to the correct child table or tables.

 

[jdraw]

It might be easier for us to understand if you would first tell us in plain English What you are trying to do. Your description is not clear. Tell us a little about the application.

 

[TomJ58]

Hi All,

Our company uses an html application in conjunction with SQL called CoStar to house protocols that the Computer Operations department uses to run all of our daily and adhoc production jobs. Because this application does a poor job of reporting, current leads have resorted to various Excel spreadsheets to track these protocols by job number, shift used, type of job, time of day, etc.

As a new lead and the one with the most knowlegde of MS Access, I took on the task of combining the spreadsheets into a single database. I have created the database (master table) and used the datasheet form view (child tables) to simulate the speadsheet veiws that the other leads are used to seeing. However, the master table contains more fields (about 40) than are displayed in the datesheet view. A tabular subform is used to add and edit records.

The child table views are based on certain critera, like job type or what shift the job runs, which are fields in the master table. These child tables are being refreshed by using delete and append queries tied to the On Load property of a form. By double-clicking a specific child record, a form is opened to allow editing of the master record. Also, I am using Referential Integrity and Cascade Update Related Fields to update the child table when a change is made to the master. However, I do not know how to add a record to one or more child tables when a record is added to the master table. The new child record would have to be based on the same critera that the append queries use so it gets added to the correct child table or tables.

 

[jdraw]

40 fields in a table is on the very high side of things. If the table were normalized, I'd be surprised to see more that 15 fields. So database design/structure may be part of the issue. (just an immediate observation, and may be way off base)

Forms and subforms are usually set up between tables that have a 1 to Many relationship.
1 being the "Master" and Many/subform children.

Can you post a dumbed down (remove confidential data) version of your database? Enough data to show the issue, but not the full operational data. And a description to follow along a specific form/subform record operation to assist anyone trying to help.

 

[TomJ58]

Maybe I am making my question more difficult than neccessary. The issue is propagating a new record in the master table to one or more child tables. I have a form for adding new records to the master table. Cascading the relationship between master and child tables only works with editing or deleting records. Is adding a new record to a child table handled by RI or do I need to create a VB script that invokes an append query to add the necessary data from the new master record to the appropriate child table?

 

[jdraw]

Do your Master and Child tables have a 1 to many relationship?

Did you set up Form/subForm and the LinkMasterField LinkChildField ?

Have you seen this video
https://www.youtube.com/watch?v=YZKN_-P6wck