Updating Tables in Access

[DECNET]

Ok, here is the scenario. I have an equipment database to track software licences and server hardware.
I have a main table for the server hardware and three other tables listing different licences, versions, revisions, upgrades etc. The only field that links the 4 tables is the Server_Name. I have a form to enter the hardware details, so what I want to do is when a new server is added, it automatically creates a new record in the three software tables, that reflects the new server. Now I am sure I am missing something fundamental here, but I cannot for the life of me to get the Server_Name to update in the three disparate tables.

I have tried update and append queries and keep getting violation errors.

If anyone has an idea please let me know. DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.

 

[cmmrfrds]

Create a Form with the main table as the record source.
Create 3 subforms (one for each of the other tables), then link the subforms on the server_name field. Access will take care of filling in the foreign key in the 3 additional tables when you add a linking record.

 

[DECNET]

Thanks for that, I have now the main form and three subforms. However I still can't get the foreign keys to reflect the primary key in the main form. What should the relationships be? With a one-to-one join the related records aren't updated, with a one-to-many join, Access will not allow me to go to the specific record.
I'm sure I am missing something simple, but cannot see it.

Thanks DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.

 

[cmmrfrds]

Did you go to the upper left hand side of the subform and find the linking properties? You need to be right on the tip of the corner and it should show a place for the link fields. That is all it should take.

 

[DECNET]

Yes, checked that. The Child and Master fields are correct.
But adding a record to the Main Form doesn't update the Tables that the Sub Forms are derived from.

Is there something I need to do as an 'On Update' ??

Sorry, clutching at straws at the moment.

I have a work around, where if some data is entered into all Sub Forms, then the Tables reflect that. But in reality this would not happen, as not all the Sub Forms would be needed in all cases.

Thanks DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.

 

[cmmrfrds]

The tables that are the data source for the subforms will only be updated when you actually add data in the subform. The idea is to carry the primary key from the main form down into the subform as a foreign key. You only want a record added to the individual subform tables when you enter data into them. That is as expected. IS there something else you want to do????