Insert multiple records with multiple tables having the same ID into a table using DAO in access

See more:

SQL

MS-Access

(1) I have 4 tables (A-Sched,B-Trans,(C-ItemRecep and D-ItemPharm)) with one Lookup table called D-TransDetail. Below is the relationship diagram and the DAO record set for storing records.

[Image 1][^]

This model works perfect when it is only "ItemRecep"(Table C), but the subforms were not reporting correctly on the parent form the moment I introduce the new "ItemPharm"(Table D).
I expected the form to display a new subform transaction Itempharm. (with diff ID i.e 4) containing it's own sub items with Price subtotal. And the Grand Total from the figure above adding up the 2 sub totals.

P.S -Please let me know if my explanation is not understood, I am ready to provide more information

What I have tried:

[Image 1][^]

[Image 2][^]

[Image 3][^]

Public Sub RecpSchedule1()
    'Consultation ONLY
    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rt As DAO.Recordset, rd As DAO.Recordset, ri As    DAO.Recordset
    Dim lngTransId As Long
    Dim lngItemRecepId As Long


    Set db = CurrentDb
    Set rs = db.OpenRecordset("Sched")
    Set rt = db.OpenRecordset("Trans")
    Set ri = db.OpenRecordset("ItemRecep")
    Set rd = db.OpenRecordset("TransDetail")
    
    lngItemRecepId = Nz(DMax("ID", "ItemRecep"), 0) + 1 'Next ItemRecep ID
    lngTransId = Nz(DMax("ID", "Trans"), 0) + 1 'Next Trans ID
    
        With rs
            .AddNew
            !SDate = Me.txtSchedDate
            !PatientName = Me.cmbPatientName
            !RegNo = Me.txtRegNo
            !DateOfBirth = Me.txtAge
            !Gender = Me.txtGender
            !PatientClass = Me.PatientClass
            !RecepSchedule = True
            .Update
        End With
        

        With rt
            .AddNew
            !ID = lngTransId
            !SchedRegNo = Me.txtRegNo
            ![Total_RecepFee] = Me.txtConsFee + ![Total_RecepFee]
           .Update
        End With

        With ri
            .AddNew
            !ID = lngItemRecepId
            !ItemName = "ConsFee"
            !Price = Me.txtConsFee.Value
            !Dept = "Reception"
            .Update
        End With

        With rd
            .AddNew
            !TransID = lngTransId
            !TransID = DMax("ID", "Trans")
            !ItemRecepID = DMax("ID", "ItemRecep")
            .Update
        End With



        rs.Close
        rt.Close
        ri.Close
        rd.Close
        
     Set rs = Nothing
     Set rt = Nothing
     Set rd = Nothing
     Set ri = Nothing
     Set db = Nothing

     End Sub



     Public Sub RecpSchedule2()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rt As DAO.Recordset, rd As DAO.Recordset, ri As    DAO.Recordset
    Dim lngTransId As Long
    Dim lngItemRecepId As Long


    Set db = CurrentDb
    Set rt = db.OpenRecordset("Trans")
    Set ri = db.OpenRecordset("ItemRecep")
    Set rd = db.OpenRecordset("TransDetail")

    lngItemRecepId = Nz(DMax("ID", "ItemRecep"), 0) + 1 'Next ItemRecep ID
    lngTransId = Nz(DMax("ID", "Trans"), 0) + 1 'Next Trans ID

        With ri
            .AddNew
            !ID = lngItemRecepId
            !ItemName = "IOPFee"
            !Price = Me.txtIOPFee.Value
            !Dept = "Reception"
            .Update
        End With

        With rd
            .AddNew
            !TransID = lngTransId
            !TransID = DMax("ID", "Trans")
            !ItemRecepID = DMax("ID", "ItemRecep")
            .Update
        End With

        rt.Close
        ri.Close
        rd.Close

     Set rt = Nothing
     Set rd = Nothing
     Set ri = Nothing
     Set db = Nothing
     End Sub

(2) I created 2 Queries ("TransQry" and "SubTransQry") from the tables above.

(3) Finally I created a parent form (frmAccount)containing a child "TransForm" which also Parents it's own child "SubTransForm".