424,303 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

Pasting multiple records in a form After Update

P: 121

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

Expand|Select|Wrap|Line Numbers
  1. Dim dbsCurrent As Database
  2.     Dim dbsLinkedData As Database
  3.     Dim rstQAssignedHrsSum As DAO.Recordset
  4.     Dim rstTblAssignHrs As DAO.Recordset
  5.     Dim QueryProjID As Integer
  6.     Dim QuerySessionID As Integer
  7.     Dim TableProjID As Integer
  8.     Dim TableSessionID As Integer
  9.     Dim TotalHrsSum As Integer
  11. 'sets quer recordset and the the table where the data is going to be written
  13.     Set dbsCurrent = CurrentDb
  14.     Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ClientData03")
  16.     Set rstTblAssignHrs = _
  17.         dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  18.     Set rstQAssignedHrsSum = _
  19.       dbsCurrent.OpenRecordset("Q_SFormTotalHrs", dbOpenDynaset)
  22.   Do While Not rstTblAssignHrs.EOF
  25.         TableProjID = rstTblAssignHrs!ProjectID
  26.         TableSessionID = rstTblAssignHrs!SessTypeID
  28. 'sets completed hours to zero
  30.         rstTblAssignHrs.Edit
  31.         rstTblAssignHrs!CompletedHrs = 0
  32.         rstTblAssignHrs.Update
  35.   Do While Not rstQAssignedHrsSum.EOF
  37.         QueryProjID = rstQAssignedHrsSum!ProjID
  38.         QuerySessionID = rstQAssignedHrsSum!SessTypeID
  39.         TotalHrsSum = rstQAssignedHrsSum!sumofexpr1
  41.         rstQAssignedHrsSum.MoveNext
  43. 'edits total hours
  45.  If TableProjID = QueryProjID And TableSessionID = QuerySessionID Then
  47.         rstTblAssignHrs.Edit
  48.         rstTblAssignHrs!CompletedHrs = TotalHrsSum
  49.         rstTblAssignHrs.Update
  52.  ''This sets reports sent to false becuse assigned hours has been edited
  54.     If rstTblAssignHrs!CompletedHrs <= rstTblAssignHrs!AssignHours * 60 / 100 * 75 Then
  56.         rstTblAssignHrs.Edit
  57.         rstTblAssignHrs!ReportSent = False
  58.         rstTblAssignHrs.Update
  60.     End If
  63.  End If
  66.     Loop
  68.         rstTblAssignHrs.MoveNext
  69.         rstQAssignedHrsSum.MoveFirst
  71.     Loop
  75. rstTblAssignHrs.Close
Mar 9 '09 #1
Share this Question
Share on Google+
4 Replies

P: 121

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
Mar 10 '09 #2

Expert Mod 2.5K+
P: 2,545
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.

Mar 12 '09 #3

Expert 2.5K+
P: 3,532
I'm still trying to figure out the "after update method!" Adding/editing any data in the Form_AfterUpdate always causes a major coding burp, because changing data triggers the Form_BeforeUpdate event, which in turn triggers the Form_AfterUpdate event, which in turn triggers the Form_BeforeUpdate, and so on and so forth! In other words, you create an endless loop!

Linq ;0)>
Mar 13 '09 #4

P: 121

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

Mar 13 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.