recordset invalid use of null

[phill86]

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

 
    Dim dbsCurrent As Database
    Dim dbsLinkedData As Database
    Dim rstQAssignedHrsSum As dao.Recordset
    Dim rstTblAssignHrs As dao.Recordset
    Dim strSeekProjID As String
    Dim strSeekSessionID As String
    Dim nullvalue As Variant
    Dim rstExpr1 As String
    Dim SumOfHrs As Integer
    Dim HrsStore As Integer
    Dim HrsTotal As Integer
    Dim rstProjID As Integer
    Dim rstSessTypeID As Integer
    Dim rstCompletedHrs As Variant
    Dim varbookmark As Variant
 
  '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\ExampleAccess2000")
    Set rstTblAssignHrs = _
        dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
    Set rstQAssignedHrsSum = _
      dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
 
 
    strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
    strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
 
    'sets data to be searched
 
 
    'loops query and adds up the total completed hours
With rstQAssignedHrsSum
 
Do
 
Do Until rstQAssignedHrsSum.EOF
 
 
        rstExpr1 = rstQAssignedHrsSum!expr1
        HrsStore = HrsStore + rstExpr1
 
        .MoveNext
        HrsTotal = HrsTotal + HrsStore
        HrsStore = 0
 
Loop
 
Loop Until rstQAssignedHrsSum.EOF
 
        .Close
 
End With
'loops table until record is found and writes data to field
With rstTblAssignHrs
 
    Do
 
    Do Until rstTblAssignHrs.EOF
 
 
        rstProjID = rstTblAssignHrs!ProjectID
        rstSessTypeID = rstTblAssignHrs!SessTypeID
 
 
     If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
 
        rstTblAssignHrs.Edit
 
 
        rstTblAssignHrs!completedhrs = HrsTotal
        rstTblAssignHrs.Update
 
        End If
 
        .MoveNext
 
    Loop
 
Loop Until rstTblAssignHrs.EOF
 
        .Close
 
    End With
'Exit_Command16_Click:
 
 Exit Function
'Err_Command16_Click:
 
  '  MsgBox Err.Description
  '  Resume Exit_Command16_Click
Exit Function
End Function

[ADezii]

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.

 
Dim dbsCurrent As Database
Dim dbsLinkedData As Database
Dim rstQAssignedHrsSum As dao.Recordset
Dim rstTblAssignHrs As dao.Recordset
Dim strSeekProjID As String
Dim strSeekSessionID As String
Dim nullvalue As Variant
Dim rstExpr1 As String
Dim SumOfHrs As Integer
Dim HrsStore As Integer
Dim HrsTotal As Integer
Dim rstProjID As Integer
Dim rstSessTypeID As Integer
Dim rstCompletedHrs As Variant
Dim varbookmark As Variant
'
'Sets Query Recordset and the the table where the data is going to be written
'
Set dbsCurrent = CurrentDb
Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
Set rstTblAssignHrs = dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
Set rstQAssignedHrsSum = dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
'
strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
'
'sets data to be searched
'
'loops query and adds up the total completed hours
With rstQAssignedHrsSum
  Do
    Do Until rstQAssignedHrsSum.EOF
      rstExpr1 = rstQAssignedHrsSum!expr1
      HrsStore = HrsStore + rstExpr1
        .MoveNext
      HrsTotal = HrsTotal + HrsStore
      HrsStore = 0
    Loop
  Loop Until rstQAssignedHrsSum.EOF
    .Close
End With
'
'loops table until record is found and writes data to field
With rstTblAssignHrs
  Do
    Do Until rstTblAssignHrs.EOF
      rstProjID = rstTblAssignHrs!ProjectID
      rstSessTypeID = rstTblAssignHrs!SessTypeID
        If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
          rstTblAssignHrs.Edit
            rstTblAssignHrs!completedhrs = HrsTotal
          rstTblAssignHrs.Update
        End If
          .MoveNext
    Loop
  Loop Until rstTblAssignHrs.EOF
    .Close
End With

[phill86]

Fixed It

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

Dim Msg, Style, Title, Response1, MyString
Msg = "Are You Sure You Want To DELETE The Record ?"   ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Delete"  ' Define title.
Response1 = MsgBox(Msg, Style, Title)
If Response1 = vbYes Then    ' User chose Yes.
Response = acDataErrContinue
 
ChkAssignedHrsdel
 
   'DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdDelete
 
Else                    ' User chose No.
 
 
  ' MyString = "No"     ' Perform some action.
    DoCmd.CancelEvent
End If

[AXESMI59]

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