MS Access Subforms

Question

I have an Access form that contains a sub form that contains a sub form (three forms - main, sub form, sub sub form). I can create a new record in the main form that resets the subform and the sub sub form with no problems. However, if I try to create a new record in the main sub form, it seems to carry the contents from the sub sub form and I end up overwriting the record details in the sub sub form.

For example, I open the main form to input a new invoice that has several line items, each one needing to be broke out by department. I enter the invoice date, vendor, invoice frequency and any comments for the invoice. I then use a drop-down list to select the appropriate line item on the invoice. Finally, I enter the departmental breakdown of the specific line item selected in the sub form. I then click the new record button in the sub form for the next line item on the invoice. But when I do, it auto populates the departmental breakdown from the previous line item and I overwrite the records, thus losing the records from the previous line item. My only work around is to create a completely new record in the main form for the same invoice vendor.

How can I get the new record in the sub form to reset the line items in the sub sub form?

Answer 1

Hi @Gary L Alford,

Based on your information, if I don't misunderstand anything about your situation, the problem is likely that the sub-subform is linked to the wrong parent key.

Based on your screenshot, your first sub-form control includes:

• Main form: frmInvoices
• Sub-form control: frmInvoiceServices
• Link Master Fields: InvoiceID
• Link Child Fields: InvoiceID

It's correct for the first level that one invoice can have many invoice service/line-item records. However, for the sub sub-form, I think the departmental breakdown should be linked to the current line-item record in the middle sub-form, not be linked only by InvoiceID. Otherwise, when you move to a new line item under the same invoice, Access still sees the same InvoiceID, so the old department breakdown records still appear. Then if you edit those rows, you are editing the previous line item’s breakdown, which explains why the old details get overwritten.

Hope my observation and sharing may be helpful for your concern. You can try editing the parent key to see if it works. If not, we can work together to figure out a solution.

If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.  

Thank you for your patience and understanding. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

These two images show the settings and how the forms are linked.