By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Pasting multiple records in a form After Update

100+
P: 121
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


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
  10.  
  11. 'sets quer recordset and the the table where the data is going to be written
  12.  
  13.     Set dbsCurrent = CurrentDb
  14.     Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ClientData03")
  15.  
  16.     Set rstTblAssignHrs = _
  17.         dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  18.     Set rstQAssignedHrsSum = _
  19.       dbsCurrent.OpenRecordset("Q_SFormTotalHrs", dbOpenDynaset)
  20.  
  21.  
  22.   Do While Not rstTblAssignHrs.EOF
  23.  
  24.  
  25.         TableProjID = rstTblAssignHrs!ProjectID
  26.         TableSessionID = rstTblAssignHrs!SessTypeID
  27.  
  28. 'sets completed hours to zero
  29.  
  30.         rstTblAssignHrs.Edit
  31.         rstTblAssignHrs!CompletedHrs = 0
  32.         rstTblAssignHrs.Update
  33.  
  34.  
  35.   Do While Not rstQAssignedHrsSum.EOF
  36.  
  37.         QueryProjID = rstQAssignedHrsSum!ProjID
  38.         QuerySessionID = rstQAssignedHrsSum!SessTypeID
  39.         TotalHrsSum = rstQAssignedHrsSum!sumofexpr1
  40.  
  41.         rstQAssignedHrsSum.MoveNext
  42.  
  43. 'edits total hours
  44.  
  45.  If TableProjID = QueryProjID And TableSessionID = QuerySessionID Then
  46.  
  47.         rstTblAssignHrs.Edit
  48.         rstTblAssignHrs!CompletedHrs = TotalHrsSum
  49.         rstTblAssignHrs.Update
  50.  
  51.  
  52.  ''This sets reports sent to false becuse assigned hours has been edited
  53.  
  54.     If rstTblAssignHrs!CompletedHrs <= rstTblAssignHrs!AssignHours * 60 / 100 * 75 Then
  55.  
  56.         rstTblAssignHrs.Edit
  57.         rstTblAssignHrs!ReportSent = False
  58.         rstTblAssignHrs.Update
  59.  
  60.     End If
  61.  
  62.  
  63.  End If
  64.  
  65.  
  66.     Loop
  67.  
  68.         rstTblAssignHrs.MoveNext
  69.         rstQAssignedHrsSum.MoveFirst
  70.  
  71.     Loop
  72.  
  73.  
  74.  
  75. rstTblAssignHrs.Close
Mar 9 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 121
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
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.

-Stewart
Mar 12 '09 #3

missinglinq
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

100+
P: 121
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
Mar 13 '09 #5

Post your reply

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