VBA Copy primary Key to table when a button is clicked

[pauld118]

Hello,

I have two forms that were created to enter data. The main form creates a record in the Parent Table. The second form populates a Child table which have a relationship with the Parent Table. The foreing key in the Child Table is = CaseID and the Primary key in the Parent Table is = CaseID, too.

I would like to add a button in the Main Form that when clicked creates a new record in the Child Table. When the new record is created in the Child Table, I would like to copy the primary key from the Parent Table to the Child Table. It seen that this will allow to enter the data in the Child Table relating it with the Parent Table. On the form that populates the Child table, I would like to add a button that will allow adding more records related the record created in the Parent table. So, in the child table one CaseID number can have many records.

I've tried a lot of VBA coding and for some reason error comes. So, I would like to start from scratch. I am REALLY new on VBA and it seen that this is complicated routine.

 

[RuralGuy]

In a MainForm/SubForm arrangement if this field is your LinkMaster/ChildFields then Access will do this for you without any coding.

 

[pauld118]

Yes, that is true. I forgot to tell that I don't wanna use subform. Because subform can only been seen in a tabular way.

 

[Thales750]

Yes, that is true. I forgot to tell that I don't wanna use subform. Because subform can only been seen in a tabular way.

For the subform, in the properties dialog box, set the default to form view, and disallow data sheet view.

 

[suburbanpsyco]

You might want to look into recordsets, if youre not using them already. I only mention this because you claim you are new to VBA. A Recordset will contain whichever data you choose from a table, via SQL. Create one for each table. When you click the button to create a new table on the recordset:

yourChildRecordSet.AddNew
yourChildRecordSet!CaseID = yourParentRecordSet!CaseID
yourChildRecordSet!otherValues = 'Whereever your values are comming from
yourChildRecordSet.update 'Saves the onformation to your table

Now, mind you, youre going to include some way of navigating to the desired ParentRecordSet record, but this is another of many tasks that are made almost trivial with a recordset.

Happy Coding!