Solved Help with RecordsetClone

[KevinBaker]

I've got a form with a Continuous Subform. I've got a Delete button that will delete the currently selected record on the subform. Before the record is deleted I'm trying to get the ID of the previous record if one exist or the next record. Of course if there is only one record in the subform, then there would be no ID.

I don't think I fully understand when I'm at the BOF or EOF. If I'm on the first record in the subform, the code fails.

Private Sub cmDelTrans()
  Dim lngID As Long, lngPID As Long
 
  lngID = SF.A1IdNum

  SF.RecordsetClone.FindFirst "A1IdNum=" & lngID
    
  If Not SF.RecordsetClone.BOF Then
    SF.RecordsetClone.MovePrevious
    lngPID = SF.RecordsetClone.A1IdNum
  ElseIf Not SF.RecordsetClone.EOF Then
    SF.RecordsetClone.MoveNext
    lngPID = SF.RecordsetClone.A1IdNum
  End If
 
  If Not IsNull(lngPID) Then
    strSQL = "UPDATE tblAccount1 SET Balance=Null WHERE A1IdNum=" & lngPID & ";"
    db.Execute strSQL, dbFailOnError
  End If
 
  'Delete the selected entry and any children records
  db.Execute "DELETE tblAccount1.*, A1IdNum, COfA1IdNum FROM tblAccount1 WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"
End Sub

 

[MajP]

If Not SF.RecordsetClone.BOF Then

From

BOF, EOF properties (ADO)

Office developer client VBA reference documentation

learn.microsoft.com

BOF — Indicates that the current record position is before the first record in a Recordset object.

BOF is not the first record so if you are at the first record you are not BOF but you also cannot move previous
Probably need

If Not rs.bof and not RS.absolutePosition = 1Then

You are making continuous calls to recordset clone. Instead
dim rs as dao.recordset
set rs = sf.recordsetclone
now use rs
Is SF a global variable? Where is that referenced.

 

[KevinBaker]

If Not SF.RecordsetClone.BOF Then

From

BOF, EOF properties (ADO)

Office developer client VBA reference documentation

learn.microsoft.com

BOF is not the first record so if you are at the first record you are not BOF but you also cannot move previous
Probably need

If Not rs.bof and not RS.absolutePosition = 1Then

You are making continuous calls to recordset clone. Instead
dim rs as dao.recordset
set rs = sf.recordsetclone
now use rs
Is SF a global variable? Where is that referenced.

Thanks MajP, will apply your recommendations. SF is just a var I use for the SubForm. On the Parent I have this

Dim SF As Form_frmChecking_sub

And set it on form open

 

[theDBguy]

Just curious, why did you need the previous or next id?

 

[KevinBaker]

I clear the balance field, which it turns triggers some recalc code

 

[cheekybuddha]

'Delete the selected entry and any children records
  db.Execute "DELETE tblAccount1.*, A1IdNum, COfA1IdNum FROM tblAccount1 WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"

Are you sure this does what you think, Kevin?

When you delete a record you delete the *whole* record, not just some fields.
"DELETE * FROM tblAccount1 WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"
If you want to delete the values in specific fields you need an UPDATE statement.
"UPDATE tblAccount1 SET A1IdNum = NULL, COfA1IdNum = NULL WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"

However, perhaps back up a second and work out whether you should be doing this at all. It looks like you're storing calculated values (eg COfA1IdNum ??) Maybe I've mis-read that but, as you know, you should only do that if calculating on the fly is too slow.

Re. your original question: why you need to do it is difficult to understand, but I think your logic should be more like:

Private Sub cmDelTrans()
  Dim lngID As Long, lngPID As Long
 
  lngID = SF.A1IdNum

  With SF.RecordsetClone
    .FindFirst "A1IdNum=" & lngID
    .MovePrevious
    If Not .BOF Then        ' A previous record exists
      lngPID = .A1IdNum
    Else                    ' No previous record
      .MoveNext             ' Back to where you started
      .MoveNext             ' Move to next record
      If Not .EOF Then      ' There is a next record
        lngPID = .A1IdNum
      End If
    End If
  End With

' The logic in the following statements doesn't make sense!!!

  If Not IsNull(lngPID) Then
    strSQL = "UPDATE tblAccount1 SET Balance=Null WHERE A1IdNum=" & lngPID & ";"
    db.Execute strSQL, dbFailOnError
  End If
 
  'Delete the selected entry and any children records
  db.Execute "DELETE tblAccount1.*, A1IdNum, COfA1IdNum FROM tblAccount1 WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"
End Sub

 

[KevinBaker]

This is a form that works like a check register and it does store the running balance, which is a calculated value. I know storing calculated values isn't wise, but it's what I've got to work with at the moment, however I only calculate or re-calculate balances when necessary.

This code is used to determine which record I need to start recalculating from. In working with this over the last day, I realized I only need to check if there is a record after the current record. All balances in the records above the current record would not be effected when the current record is delete, only the balances below the current record would need to be updated. Here's the updated code that from initial testing appears to work.

Private Sub cmDelTrans()
  Dim rst As DAO.Recordset, lngID As Long

  'Get the current record ID
  lngID = SF.A1IdNum

  Set rst = SF.RecordsetClone
  rst.FindFirst "A1IdNum=" & lngID
  rst.MoveNext

  If Not rst.EOF Then
    lngPID = rst!A1IdNum 'this is the next record ID after the selected record
  End If

  rst.Close
  Set rst = Nothing
 
  'If there was a next record, then delete the value in the Balance field, which will trigger a recalc later in other code 
  If Not IsNull(lngPID) Then
    strSQL = "UPDATE tblAccount1 SET Balance=Null WHERE A1IdNum=" & lngPID & ";"
    db.Execute strSQL, dbFailOnError
  End If
 
  'Delete the selected entry and any children records
  db.Execute "DELETE tblAccount1.*, A1IdNum, COfA1IdNum FROM tblAccount1 WHERE A1IdNum=" & lngID & " OR COfA1IdNum=" & lngID & ";"
End Sub

 

[cheekybuddha]

Yes, that's the way. Do the move and then immediately test whether you are at .EOF

If false, then there is a next record