How to insert main-form datum into sub-form's underlying table?

[qajaq]

I'm trying to develop a form to record bank deposits for a small non-profit agency. The deposit records need to include the date and total amount and a variable number of contributions. Those contributions need to be recorded individually, showing the name of the contributor and the amount. The number of contributions will vary from one deposit to another.




I've created two tables, one for the deposit summary data (with fields for date and amount, entered by the user, and a deposit ID generated automatically by Access), and another for the individual contributions (with fields for the contributor's name, amount given, and the ID number of the deposit in which it was included).


I've created two forms: a main form to solicit the deposit summary data, and a sub-form to solicit the list of individual contributors and amounts. Almost all works as hoped. The only part I'm not getting is how to get Access to insert the deposit ID (from the main form's underlying table) into the data table with the individual contributions.


I have created a one-to-many relationship: one deposit summary to many individual contributions, linking the ID field in the deposit summary table to the deposit_ID field in the contributions table.


Where to I go from here to get that deposit ID number into the contributions table?

[June7]

Is the deposit ID in main table set as the primary key?

Build form/subform arrangement. Set the subform container control Master/Child Links properties to the related deposit ID PK/FK fields. The ID will automatically save into the dependent subform record.

BTW, don't put amount in the main record. This should be calculated by summing the related contribution detail records.

[qajaq]

June7, thanks . . . yes, I had the primary key set up in the main (deposit summary) table. Found that my error was apparently in using different names in the two tables ("ID" in the summary table, and "deposit_ID" in the sub-table), even though I had the relationships linked. As soon as I changed the name of the PK field in the main table to "deposit_ID", it all worked as hoped. Thanks!

[June7]

The names don't have to be the same, just have to properly select them in the Links properties.

[qajaq]

I don't know what I was doing wrong or what I changed other than the field name, but I've changed the field-name in the main table back to simply "ID" and the connection works. Thanks again!

FWIW, the total deposit amount that I have the user enter on the main form is a validity check. They enter that from the deposit receipt, then enter individual contribution amounts from the manager's worksheet. If the sum of those entries doesn't match up with the summary total, they'll get a message to check their typing or look for a missed entry.

[June7]

Okay, sort of a double-entry bookkeeping technique.