IDs of multiple records (1 Viewer)

[ZEEq]

hello everyone!
i have a insert query which inserts studentClassiD,StudentD ,invoiceDueDate into studentFeeInvoiceT table then i need to get the studentFeeInvoiceT.StudentFeeInvoiceID and insert that into StudentFeeInvoiceDetail and talking about multiple records qdf will insert many records into studentFeeInvoiceT how can i achieve this ??
regards

 

[jdraw]

Please show us your relationships window with tables extended to show all fields.

 

[ZEEq]

    Dim db As DAO.Database
          Dim qdf1 As DAO.QueryDef
          Dim rst As DAO.Recordset
          Dim strSQL1 As String
          Dim strSQL2 As String
          Dim lngStudentFeeInvoiceID As Long

          ' Define parameters for the query
          Dim invoiceDate As Date
          Dim InvoiceDueDate As Date
          Dim SessionYearID As Long
          Dim numRecords1 As Long

          ' Set up database object
20        Set db = CurrentDb()

          ' Get the ID of the last inserted record in the first destination table
30        lngID = db.OpenRecordset("SELECT @@Identity")(0)

          'always insert first day of month
40        InvoiceDueDate = DateSerial(Year([Forms]![StudentFeeBatchF]![txtInvoiceDueDate]), Month([Forms]![StudentFeeBatchF]![txtInvoiceDueDate]), 1)

          ' Set invoice date and session year ID
50        invoiceDate = [Forms]![StudentFeeBatchF]![txtInvoiceDate]
60        SessionYearID = [Forms]![StudentFeeBatchF]![SessionYearID]

          ' Define SQL for the first query
70        strSQL1 = "PARAMETERS [Forms]![StudentFeeBatchF]![txtInvoiceDate] DateTime, [Forms]![StudentFeeBatchF]![txtInvoiceDueDate] DateTime, [Forms]![StudentFeeBatchF]![SessionYearID] Long;" & _
              "INSERT INTO StudentFeeInvoiceJT (StudentID, StudentClassID, InvoiceDate, InvoiceDueDate)" & _
              "SELECT StudentClassJT.StudentID, StudentClassJT.StudentClassID, [Forms]![StudentFeeBatchF]![txtInvoiceDate], [Forms]![StudentFeeBatchF]![txtInvoiceDueDate]" & _
              "FROM (((SessionYearT INNER JOIN SessionYearTypeJT ON SessionYearT.SessionYearID = SessionYearTypeJT.SessionYearID) " & _
              "INNER JOIN (StudentT INNER JOIN (SessionGradeJT INNER JOIN (StudentClassJT INNER JOIN StudentFeesettingwithDiscount ON StudentClassJT.StudentClassID = StudentFeesettingwithDiscount.StudentClassID) ON SessionGradeJT.SessionGradeID = StudentClassJT.SessionGradeID) ON StudentT.StudentID = StudentClassJT.StudentID) " & _
              "ON SessionYearTypeJT.SessionYearTypeID = SessionGradeJT.SessionYearTypeID) " & _
              "LEFT JOIN (SELECT * FROM StudentFeeInvoiceJT WHERE Format(InvoiceDueDate,'yyyy-mm') = Format([Forms]![StudentFeeBatchF]![txtInvoiceDueDate],'yyyy-mm')) AS StudentFeeInvoiceFiltered ON (StudentClassJT.StudentClassID = StudentFeeInvoiceFiltered.StudentClassID) AND (StudentClassJT.StudentID = StudentFeeInvoiceFiltered.StudentID)) " & _
              "WHERE (((SessionYearT.SessionYearID)= [Forms]![StudentFeeBatchF]![SessionYearID]) AND ((SessionYearTypeJT.Status)=1) AND ((StudentFeesettingwithDiscount.FrequencyID)=1 OR (StudentFeesettingwithDiscount.FrequencyID)=3) AND ((StudentT.Status)=1) AND ((StudentFeeInvoiceFiltered.StudentID) Is Null) AND ((StudentFeeInvoiceFiltered.StudentClassID) Is Null));"


          ' Create and execute the first query
80        Set qdf1 = db.CreateQueryDef("", strSQL1)
90        qdf1.Parameters("[Forms]![StudentFeeBatchF]![txtInvoiceDueDate]") = InvoiceDueDate
100       qdf1.Parameters("[Forms]![StudentFeeBatchF]![txtInvoiceDate]") = invoiceDate
110       qdf1.Parameters("[Forms]![StudentFeeBatchF]![SessionYearID]") = SessionYearID
120       qdf1.Execute dbFailOnError
130       numRecords1 = qdf1.RecordsAffected

          ' Check if any records were inserted into StudentFeeInvoiceJT table
140       If numRecords1 = 0 Then
150           MsgBox "No records inserted into StudentFeeInvoiceJT table"
          
160       Else
              ' Show number of records inserted and success message
170           MsgBox numRecords1 & " records inserted into StudentFeeInvoiceJT table."
180           MsgBox "First query executed successfully."
190       End If
this is my code which inserts into studentFeeInvoiceJT

 

[Pat Hartman]

Why doesn't post #3 work?

 

[ZEEq]

Why doesn't post #3 work?

code insert records into studentfeeinvoiceJt but i dont know how to get all the IDs which i then have to insert into studentfeeinvoicedetail table

 

[ebs17]

The code is illogical.
Line of code 30 is in the wrong place.
Why are you naming query parameters like form text fields?

... LEFT JOIN (SELECT * FROM StudentFeeInvoiceJT ...

What is that if you need the studentFeeInvoiceT.StudentFeeInvoiceID?

i dont know how to get all the IDs which i then have to insert into studentfeeinvoicedetail table

Sample Database, with a meaningful relationship window.

 

[Pat Hartman]

If the query is not selecting the correct records, take it apart piece by piece. I'm not sure what the left join to Fee table is for. Isn't that where you want to append rows?

 

[ZEEq]

If the query is not selecting the correct records, take it apart piece by piece. I'm not sure what the left join to Fee table is for. Isn't that where you want to append rows?

query is inserting correct records into studentFeeInvoiceJT tables after that i want to insert studentFeeInvoiceJT.studentfeeinvoiceID along with StudentFeeSettingID and DiscountPct from StudentFeesettingwithDiscount into studentFeeInvoiceDetailJt table

 

[ZEEq]

The code is illogical.
Line of code 30 is in the wrong place.
Why are you naming query parameters like form text fields?

... LEFT JOIN (SELECT * FROM StudentFeeInvoiceJT ...

What is that if you need the studentFeeInvoiceT.StudentFeeInvoiceID?


Sample Database, with a meaningful relationship window.

thanks @ebs17 for pointing out that yes line 30 is at the wrong place
Please tell me lngID = db.OpenRecordset("SELECT @@Identity")(0) will this method work to retrieve multiple IDs(StudentFeeInvoiceID)?
studentFeeInvoiceDetailJt is linked with studentFeeInvoiceT.StudentFeeInvoiceID

 

[ebs17]

Please tell me lngID = db.OpenRecordset("SELECT @@Identity")(0) will this method work to retrieve multiple IDs(StudentFeeInvoiceID)?

Short answer: NO

Debug.Print lngID

What do you see as return?

By the way, the call is meaningless in the room, you don't use lngID in a row.

 

[Pat Hartman]

@@Identity can NEVER return more than one ID at a time. It only ever returns the most recent ID on the current thread.

You seem to be appending the billing data into two tables. You should only append it to the invoice table. You don't need it twice.