Pasting multiple records in a form After Update

[phill86]

Hi,

i have a form that runs a query in a recordset on the after update method if i copy and paste one record at a time the query picks up the records in the underlying table but if i paste multiple records the query fails to pick up the set of pasted records i think the after update method is running before the table is properly updated when i paste a group of records I have also tried running the code from the after insert method with no luck

Any help is much appreciated

Regards Phill

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\ClientData03")
 
    Set rstTblAssignHrs = _
        dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
    Set rstQAssignedHrsSum = _
      dbsCurrent.OpenRecordset("Q_SFormTotalHrs", dbOpenDynaset)
 
 
  Do While Not rstTblAssignHrs.EOF
 
 
        TableProjID = rstTblAssignHrs!ProjectID
        TableSessionID = rstTblAssignHrs!SessTypeID
 
'sets completed hours to zero
 
        rstTblAssignHrs.Edit
        rstTblAssignHrs!CompletedHrs = 0
        rstTblAssignHrs.Update
 
 
  Do While Not rstQAssignedHrsSum.EOF
 
        QueryProjID = rstQAssignedHrsSum!ProjID
        QuerySessionID = rstQAssignedHrsSum!SessTypeID
        TotalHrsSum = rstQAssignedHrsSum!sumofexpr1
 
        rstQAssignedHrsSum.MoveNext
 
'edits total hours
 
 If TableProjID = QueryProjID And TableSessionID = QuerySessionID Then
 
        rstTblAssignHrs.Edit
        rstTblAssignHrs!CompletedHrs = TotalHrsSum
        rstTblAssignHrs.Update
 
 
 ''This sets reports sent to false becuse assigned hours has been edited
 
    If rstTblAssignHrs!CompletedHrs <= rstTblAssignHrs!AssignHours * 60 / 100 * 75 Then
 
        rstTblAssignHrs.Edit
        rstTblAssignHrs!ReportSent = False
        rstTblAssignHrs.Update
 
    End If
 
 
 End If
 
 
    Loop
 
        rstTblAssignHrs.MoveNext
        rstQAssignedHrsSum.MoveFirst
 
    Loop
 
 
 
rstTblAssignHrs.Close

[phill86]

Hi,

I think this problem occurs because when pasting multiple records the table is in "Pre-update" mode untill you click off the new pasted records i have tried to refresh and requery and on dirty set to false but none of this works is there any way i can force the records to be written to the table before i run the after update code?

Please help this is driving me nuts!!!

Regards Phill

[Stewart Ross]

Sorry Phill, but what you are doing is as unclear as your question, unusually in your case.

You mention 'pasting' records - what has this got to do with the code you have shown? The code is looping through two recordsets in what appears to be another database on drive C, editing individual fields and updating values as you go. I can't see what this has to do with pasting anything.

If you could give a more coherent view of what you are really trying to do it would help us to help you more easily.

I would strongly advise against making assumptions about what is going on regarding update events. Unless you have traced the event sequences for yourself such assumptions can be seriously misleading and send you and us on entirely the wrong path, especially when the logic of what you are trying to achieve is not obvious...

Please be aware that form record events such as the before and after update ones are not normally triggered by copying and pasting records into a table, just as they are not triggered if you modify a field using VBA code; the events concerned are triggered when users modify fields in the underlying dataset manually. If you are, say, manually amending a record and you then Paste Append some other records the current record will be saved by Access first, triggering its Before and After Update events, but not for the pasted records at all...

It is safer and simpler to assume until proven otherwise that there is an underlying logic error in what you are trying to achieve, or your code, or both.

-Stewart

[missinglinq]

I'm still trying to figure out the "after update method!" Adding/editing any data in the Form_AfterUpdat e always causes a major coding burp, because changing data triggers the Form_BeforeUpda te event, which in turn triggers the Form_AfterUpdat e event, which in turn triggers the Form_BeforeUpda te, and so on and so forth! In other words, you create an endless loop!

Linq ;0)>

[phill86]

Hi,

Thanks for the reply sorry i didnt make the question clearer

I have a form with two sub forms. The sub forms are embedded in a tab control the first sub form contains records that are related to training sessions in these records it contains start and end dates and times and the duration of the sessions is worked out from the start and end times

so for example...

Project 1 task 2 start 1/1/09 09:00 - 1/1/09 12:00 3hr duration

The secound sub form contains task asignment details for example

Project1 task 1 Assigned hrs 10 completed hours 5
Project1 task 2 Assigned hrs 5 completed hours 3

so basically the code that i posted works out how many hours have been completed for the assigned tasks in form 2 in the sessions from form 1

so for example project 1 task 2 could have several record entries where the hours sum up to 10 completed hours

the code then rights these summed hours to a sperate table Assigned hours which is on sub form 2

This all works fine when i copy and paste a single record in sub form 1 but if i copy and paste several records in sub form 1 it will not update the completed hours in sub form 2 this is because the query i am using in the recordset runs before the records are "properly written" to the table...

I have since discovered a work around instead of using a tab control i use a button for each form and set the visible property for each of the forms to false until the relative button is pressed which turns the visible property to true this has the effect of taking the focus of the pasted records on form 1 which means the records get "properly written" and the query is able to pick up the data from the newly pasted records.

I hope my ramblings now make sense and altough i do have a work around it would be nice to know why this happens because i have come across this problem before with little success

Thanks for your time much appreciated

Phill