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

Recordset pasting multiple records

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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.     Dim dbsCurrent As Database
  4.     Dim dbsLinkedData As Database
  5.     Dim rstQAssignedHrsSum As dao.Recordset
  6.     Dim rstTblAssignHrs As dao.Recordset
  7.     Dim QueryProjID As Integer
  8.     Dim QuerySessionID As Integer
  9.     Dim TableProjID As Integer
  10.     Dim TableSessionID As Integer
  11.     Dim TotalHrsSum As Integer
  12.  
  13.  
  14.  
  15. 'sets quer recordset and the the table where the data is going to be written
  16.  
  17.     Set dbsCurrent = CurrentDb
  18.     Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
  19.     Set rstTblAssignHrs = _
  20.         dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  21.     Set rstQAssignedHrsSum = _
  22.       dbsCurrent.OpenRecordset("Q_SFormTotalHrs", dbOpenDynaset)
  23.  
  24.  
  25.  
  26. Do While Not rstQAssignedHrsSum.EOF
  27.  
  28.     QueryProjID = rstQAssignedHrsSum!ProjID
  29.     QuerySessionID = rstQAssignedHrsSum!SessTypeID
  30.     TotalHrsSum = rstQAssignedHrsSum!sumofexpr1
  31.  
  32.     rstQAssignedHrsSum.MoveNext
  33.     Do While Not rstTblAssignHrs.EOF
  34.  
  35.         TableProjID = rstTblAssignHrs!ProjectID
  36.         TableSessionID = rstTblAssignHrs!SessTypeID
  37.  
  38.     If TableProjID = QueryProjID And TableSessionID = QuerySessionID Then
  39.  
  40.     [F_ClientDetails]![SF_Session].Form.Dirty = False
  41.  
  42.         rstTblAssignHrs.Edit
  43.         rstTblAssignHrs!CompletedHrs = TotalHrsSum
  44.         rstTblAssignHrs.Update
  45.  
  46.     End If
  47.  
  48.        rstTblAssignHrs.MoveNext
  49.  
  50.  
  51.     Loop
  52.       rstTblAssignHrs.MoveFirst
  53. Loop
Query used in the recordset

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(Q_SFormTotalHrs1.Expr1) AS SumOfExpr1, Q_SFormTotalHrs1.ProjID, Q_SFormTotalHrs1.SessTypeID
  2. FROM Q_SFormTotalHrs1
  3. GROUP BY Q_SFormTotalHrs1.ProjID, Q_SFormTotalHrs1.SessTypeID;
  4.  
Regards Phill
Feb 4 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
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"?
Feb 5 '09 #2

100+
P: 121
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
Feb 19 '09 #3

Post your reply

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