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

recordset invalid use of null

100+
P: 121
Hi,

i have a recordset in a function that is called on the afterupdate event in a form

Basically the recordset finds the relevant session records and adds up how long, in hours, the sessions took and wites the sum of the session times into a seperate table this all works fine when i add a record, more time is added and when i delete a record time is taken away but when i delete the last record the error message "invalid use of null" appears this is because the record that i am refering to in the recordset has been deleted before the code for the recordset has run i have tried running the function on the after del confirm, on delete, and before del confirm event but with no luck

Any help is greatly appreciated

Regards Phill

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim dbsCurrent As Database
  3.     Dim dbsLinkedData As Database
  4.     Dim rstQAssignedHrsSum As dao.Recordset
  5.     Dim rstTblAssignHrs As dao.Recordset
  6.     Dim strSeekProjID As String
  7.     Dim strSeekSessionID As String
  8.     Dim nullvalue As Variant
  9.     Dim rstExpr1 As String
  10.     Dim SumOfHrs As Integer
  11.     Dim HrsStore As Integer
  12.     Dim HrsTotal As Integer
  13.     Dim rstProjID As Integer
  14.     Dim rstSessTypeID As Integer
  15.     Dim rstCompletedHrs As Variant
  16.     Dim varbookmark As Variant
  17.  
  18.   'sets quer recordset and the the table where the data is going to be written
  19.  
  20.     Set dbsCurrent = CurrentDb
  21.     Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
  22.     Set rstTblAssignHrs = _
  23.         dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  24.     Set rstQAssignedHrsSum = _
  25.       dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
  26.  
  27.  
  28.     strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
  29.     strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
  30.  
  31.     'sets data to be searched
  32.  
  33.  
  34.     'loops query and adds up the total completed hours
  35. With rstQAssignedHrsSum
  36.  
  37. Do
  38.  
  39. Do Until rstQAssignedHrsSum.EOF
  40.  
  41.  
  42.         rstExpr1 = rstQAssignedHrsSum!expr1
  43.         HrsStore = HrsStore + rstExpr1
  44.  
  45.         .MoveNext
  46.         HrsTotal = HrsTotal + HrsStore
  47.         HrsStore = 0
  48.  
  49. Loop
  50.  
  51. Loop Until rstQAssignedHrsSum.EOF
  52.  
  53.         .Close
  54.  
  55. End With
  56. 'loops table until record is found and writes data to field
  57. With rstTblAssignHrs
  58.  
  59.     Do
  60.  
  61.     Do Until rstTblAssignHrs.EOF
  62.  
  63.  
  64.         rstProjID = rstTblAssignHrs!ProjectID
  65.         rstSessTypeID = rstTblAssignHrs!SessTypeID
  66.  
  67.  
  68.      If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
  69.  
  70.         rstTblAssignHrs.Edit
  71.  
  72.  
  73.         rstTblAssignHrs!completedhrs = HrsTotal
  74.         rstTblAssignHrs.Update
  75.  
  76.         End If
  77.  
  78.         .MoveNext
  79.  
  80.     Loop
  81.  
  82. Loop Until rstTblAssignHrs.EOF
  83.  
  84.         .Close
  85.  
  86.     End With
  87. 'Exit_Command16_Click:
  88.  
  89.  Exit Function
  90. 'Err_Command16_Click:
  91.  
  92.   '  MsgBox Err.Description
  93.   '  Resume Exit_Command16_Click
  94. Exit Function
  95. End Function
Jan 21 '09 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
For now, I'm just subscribing to this Thread and took the liberty to reformat your code so that I may get a better picture of what actually is going on. Will check in later.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim dbsCurrent As Database
  3. Dim dbsLinkedData As Database
  4. Dim rstQAssignedHrsSum As dao.Recordset
  5. Dim rstTblAssignHrs As dao.Recordset
  6. Dim strSeekProjID As String
  7. Dim strSeekSessionID As String
  8. Dim nullvalue As Variant
  9. Dim rstExpr1 As String
  10. Dim SumOfHrs As Integer
  11. Dim HrsStore As Integer
  12. Dim HrsTotal As Integer
  13. Dim rstProjID As Integer
  14. Dim rstSessTypeID As Integer
  15. Dim rstCompletedHrs As Variant
  16. Dim varbookmark As Variant
  17. '
  18. 'Sets Query Recordset and the the table where the data is going to be written
  19. '
  20. Set dbsCurrent = CurrentDb
  21. Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
  22. Set rstTblAssignHrs = dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  23. Set rstQAssignedHrsSum = dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
  24. '
  25. strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
  26. strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
  27. '
  28. 'sets data to be searched
  29. '
  30. 'loops query and adds up the total completed hours
  31. With rstQAssignedHrsSum
  32.   Do
  33.     Do Until rstQAssignedHrsSum.EOF
  34.       rstExpr1 = rstQAssignedHrsSum!expr1
  35.       HrsStore = HrsStore + rstExpr1
  36.         .MoveNext
  37.       HrsTotal = HrsTotal + HrsStore
  38.       HrsStore = 0
  39.     Loop
  40.   Loop Until rstQAssignedHrsSum.EOF
  41.     .Close
  42. End With
  43. '
  44. 'loops table until record is found and writes data to field
  45. With rstTblAssignHrs
  46.   Do
  47.     Do Until rstTblAssignHrs.EOF
  48.       rstProjID = rstTblAssignHrs!ProjectID
  49.       rstSessTypeID = rstTblAssignHrs!SessTypeID
  50.         If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
  51.           rstTblAssignHrs.Edit
  52.             rstTblAssignHrs!completedhrs = HrsTotal
  53.           rstTblAssignHrs.Update
  54.         End If
  55.           .MoveNext
  56.     Loop
  57.   Loop Until rstTblAssignHrs.EOF
  58.     .Close
  59. End With
  60.  
Jan 22 '09 #2

100+
P: 121
Hi,

I have found a work around i just placed this piece of code in the delete event and i am able to control when the record is deleted

Thanks for your help though

Regards Phill

Expand|Select|Wrap|Line Numbers
  1. Dim Msg, Style, Title, Response1, MyString
  2. Msg = "Are You Sure You Want To DELETE The Record ?"   ' Define message.
  3. Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
  4. Title = "Delete"  ' Define title.
  5. Response1 = MsgBox(Msg, Style, Title)
  6. If Response1 = vbYes Then    ' User chose Yes.
  7. Response = acDataErrContinue
  8.  
  9. ChkAssignedHrsdel
  10.  
  11.    'DoCmd.SetWarnings False
  12.    DoCmd.RunCommand acCmdDelete
  13.  
  14. Else                    ' User chose No.
  15.  
  16.  
  17.   ' MyString = "No"     ' Perform some action.
  18.     DoCmd.CancelEvent
  19. End If
  20.  
  21.  
Jan 22 '09 #3

P: 10
Look at your .Movenext. Is it possible that is is trying to move to a record that no longer exists or beyond the number of records. Say your Do Loop has 8 records. After the 8th record, it is still executing a .Movenext. Since you have already reached your 8th record, it is trying to go to a record that doesn't exist. You should test for that. Then if you have reached the last record, skip the .Movenext. I had that problem with a Do Loop I had.

Hope this helps,

Steve
Jan 23 '09 #4

Post your reply

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