I have a Form that contains the Main Form (Expenses Table) a sub-form (Line Items Table) and a sub-sub-form (Expenses).
The problem is that the sub-subform (Expenses) is not updating the record it falls under (Line Items). This is forcing me to have to manually enter in the related record key into the controlling record.
Hello,
Images are not usually a great deal of help when resolving problems. A sample is typically better. Here the problem sems to be in the construction of the form.
You have Expenses table as main table. Your sub form is Line_Items and Line_Item_Expense_ID should link back to Expense_ID. Do not see this on your form. Likewise, Line_Item_Transaction_ID is not shown which is the connector to the sub sub form containing Transactions.
Linking between internal forms/sub forms/sub sub forms/etc. is done on internal form properties on the Data tab.
Edit 2020-12-12:
Have put together a ROUGH quick & dirty macro example to help show the problem(s) noted in the comments. Pressing the button on the transaction record will update the Line_Item selected. There is no error checking in the macro. There are too many to cover for this quick demo.
Sample ----- Expenses.odb
I’ve uploaded a copy of the database I am working on.
I’ve added the relevant form control data on the data tab. I have Expenses, Line_Items, and Transaction. Each Expense has MANY Line_Items. Each Transaction also has MANY Line_Items. The problem is that I need the Transaction form to update the Line_Item foreign key (Line_Item_Transaction_ID in this case). So, when I enter a new Transaction (which should be associated with the Line_Item), the Line_Item_Transaction_ID is not updating.
I will bypass the many, many, many negative aspects of the undertaking.
Based upon what is presented already, the only possibility is by using macros. I can give some type of sample but need to understand some of the process. So main table is expense. OK. Next is Line_Items. Can have multiple per Expense. OK.
Last is transaction. Now since you are entering a new item, there is no key until the transaction is saved. Now you state:
Each Transaction also has MANY Line_Items.
So how is it known that the Line_Item which is displayed is the one to be attached? What if it already has an ID in it? Could it be a correction? Could it be an incorrect Line_Item? Or other choice - don’t care just enter the ID? And how do you want to attach this transaction to many Line_Items?
Please some description of the process.
Edit:
Also, since the Transaction entered is going to add information to Line_Items, how is that to be saved - user or another macro?
Please see latest edit for sample.