CODEWITHSUNDEEP
vbams-accessdao
Liberty Crown Infotech
Liberty Crown Infotech

Reputation: 39

How to insert multiple records with the same ID into a table using DAO in MS Access

(1) I have 3 tables (A-Sched,B-Trans and C-ItemRecep) with one Lookup table called D-TransDetailRecep. Below is the relationship diagram. relationship diagram

(2) I am inserting values into the table fields from a form through a click button that implements the DAO below:

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("TransDetailRecep")

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] = Nz((Me.txtConsFee + Me.txtIOPFee + ![Total_RecepFee]), 0)

       .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("TransDetailRecep")

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

    'With rt
       ' .AddNew
       ' !ID = lngTransId
        '!SchedRegNo = Me.txtRegNo
        '![Total_RecepFee] = Nz((Me.txtIOPFee + ![Total_RecepFee]), 0)
       '.Update
   ' End With

    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

enter image description here (3) My challenge: The field for the Total_RecepFee should show 500 where the Price is 500 and show 1200 where the Price is 1200. (What I have now is that both is showing the sum of the values)

Upvotes: 0

Views: 1239

Answers (1)

Kostas K.
Kostas K.

Reputation: 8508

The problem lies here:

rd!TransID = rt!ID
rd!ItemRecepID = ri!ID

The rt.Update and ri.Update methods have not been run to save the recordset. But even so, .AddNew creates a new record therefore rt!ID and ri!ID are both Null.

You could try to save both rt and ri recordsets first and then retrieve the Max(ID) (since this will be the last added ID) through the DMax function. However, if multiple users have access to add data simultaneously, this could be risky (actually it would be very risky).

I would suggest why not scrapping the autonumber ID and handle it manually?

Dim lngId as Long
    lngId = DMax("ID", "Trans") + 1
    'You can now feed it to both tables

Try the updated code below. I haven't been able to test it myself.

Public Sub RecpSchedule1()

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

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Sched")
    Set rt = db.OpenRecordset("Trans")
    Set ri = db.OpenRecordset("ItemRecep")
    Set rd = db.OpenRecordset("TransDetailRecep")

        With rs
            .AddNew
            !SDate = Me.txtSchedDate
            !PatientName = Me.cmbPatientName
            !RegNo = Me.txtRegNo
            .Update
        End With

        With rt
            .AddNew
            !SchedRegNo = Me.txtRegNo
            !TDate = Me.txtSchedDate
            .Update
        End With

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

        With rd
            .AddNew
            !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

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

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

        With rd
            .AddNew
            !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

Update:

In order to handle the IDs manually, you need to determine the next IDs for tables Trans and ItemRecep upon entering data.

Dim lngTransId as Long
    lngTransId = Nz(DMax("ID", "Trans"),0) + 1 'Next Trans ID

Dim lngItemRecepId as Long
    lngItemRecepId = Nz(DMax("ID", "ItemRecep"),0) + 1 'Next ItemRecep ID

With rt
    .AddNew
    !ID = lngTransId 
    !SchedRegNo = Me.txtRegNo
    !TDate = Me.txtSchedDate
    .Update
End With

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

With rd
    .AddNew
    !TransID = lngTransId 
    !ItemRecepID = lngItemRecepId 
    .Update
End With

Upvotes: 0

Related Questions