Recordset pasting multiple records

[phill86]

Hi,

i have a recordset that updates a table which works fine when you add, update and paste a single record but if you try and paste multiple records it will only update the table with the prevoius records and it will ignore the new pasted records i am running the following code on the afterupdate event in the form

Please Help

    
 
    Dim dbsCurrent As Database
    Dim dbsLinkedData As Database
    Dim rstQAssignedHrsSum As dao.Recordset
    Dim rstTblAssignHrs As dao.Recordset
    Dim QueryProjID As Integer
    Dim QuerySessionID As Integer
    Dim TableProjID As Integer
    Dim TableSessionID As Integer
    Dim TotalHrsSum As Integer
 
 
 
'sets quer recordset and the the table where the data is going to be written
 
    Set dbsCurrent = CurrentDb
    Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
    Set rstTblAssignHrs = _
        dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
    Set rstQAssignedHrsSum = _
      dbsCurrent.OpenRecordset("Q_SFormTotalHrs", dbOpenDynaset)
 
 
 
Do While Not rstQAssignedHrsSum.EOF
 
    QueryProjID = rstQAssignedHrsSum!ProjID
    QuerySessionID = rstQAssignedHrsSum!SessTypeID
    TotalHrsSum = rstQAssignedHrsSum!sumofexpr1
 
    rstQAssignedHrsSum.MoveNext
    Do While Not rstTblAssignHrs.EOF
 
        TableProjID = rstTblAssignHrs!ProjectID
        TableSessionID = rstTblAssignHrs!SessTypeID
 
    If TableProjID = QueryProjID And TableSessionID = QuerySessionID Then
 
    [F_ClientDetails]![SF_Session].Form.Dirty = False
 
        rstTblAssignHrs.Edit
        rstTblAssignHrs!CompletedHrs = TotalHrsSum
        rstTblAssignHrs.Update
 
    End If
 
       rstTblAssignHrs.MoveNext
 
 
    Loop
      rstTblAssignHrs.MoveFirst
Loop

Query used in the recordset

SELECT Sum(Q_SFormTotalHrs1.Expr1) AS SumOfExpr1, Q_SFormTotalHrs1.ProjID, Q_SFormTotalHrs1.SessTypeID
FROM Q_SFormTotalHrs1
GROUP BY Q_SFormTotalHrs1.ProjID, Q_SFormTotalHrs1.SessTypeID;

Regards Phill

[ChipR]

Based on the IF statement, it looks like your code is updating only records that exist in both tables. What do you mean by "the new pasted records"?

[phill86]

Hi Chip R

Sorry for the late reply i have been out of the loop for a while i have found a work around now but thanks anyway

Regards Phill