Subform RecordsetClone 'No Current Record' error

• Previous message: lavanya: "progress bar"
• Next in thread: John Viescas: "Re: Subform RecordsetClone 'No Current Record' error"
• Reply: John Viescas: "Re: Subform RecordsetClone 'No Current Record' error"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 24 Feb 2004 13:28:20 -0800

I'm using a VB procedure to alter data in a related group
of rows all displayed in an Access 2000 subform. This
subform displays rows in list format. If the user sets a
yes/no field in one row to 'yes', the procedure should set
any other rows previously set to 'yes' to instead have
a 'no' value. The "after update" event of the form control
is set up to start this procedure if the current value of
the control is 'yes'.

The perplexing thing here is that I'm using the same
procedure in conjunction with 5 different subforms. In 3
cases it works perfectly. In the last 2 it gives me
a '3021 - No current record' error. I have closely
examined all 5 applications, and haven't noticed any
difference that seems related. All subforms are based on
dynasets.

At this point, I assume (and I'm hoping that) I'm missing
something OBVIOUS that another experienced programmer will
see quickly... Any suggestion will be gratefully
considered..

The procedure code is shown below...

Public Sub SetOppositeRstBooleans(ByVal frm As Form, _
                                  ByVal ctlRowId As
Control, _
                                  ByVal ctlCategory As
Control)
   Rem This procedure accepts specification of a form
control containing _
       a boolean value, a control containing an id value
for rows in a _
       forms recordset, and and object reference to the
form defining a _
       recordset to be examined. The procedure retrieves
the value of the _
       boolean in the row indicated by the id value, and
then sets the _
       value of that control on all other rows of the
recordset to the _
       opposite value. For instance, if the recordset has
rows 1,2,3, _
       the user passes row id 2, and row 2 has
value "True", rows 1 and 3 _
       will have their values set to "False".
    
   On Error GoTo Err_SetOppositeRstBooleans
   
   Rem References to form "RecordsetClone" property
require use of DAO _
       reference library when using an MDB database. If a
switch away _
       from MDB databases is made, a corresponding change
to ADODB _
       recordsets may be required here.
    
   Debug.Print "SetOppositeRstBooleans"
   
   Dim bCategoryVal As Boolean 'Boolean used for
comparison value
   Dim bCategoryValOpposite As Boolean
   Dim bValMatch As Boolean
   Dim rst As DAO.Recordset 'May need to be changed to
ADODB recordset - see note above
   Dim strRowIdCol As String
   Dim strRowIdVal As String
   Dim strCategoryCol As String
   Dim varBookmark As Variant
   
   Rem Prevent further operations if null values have been
passed
   If frm Is Nothing Then
      MsgBox mstrcModuleName & ".SetOppositeRstBooleans -
Input form set to 'Nothing'"
      GoTo Exit_SetOppositeRstBooleans
   End If
   If ctlRowId Is Nothing _
      Or ctlCategory Is Nothing Then
      MsgBox mstrcModuleName & ".SetOppositeRstBooleans -
Input control set to 'Nothing'"
      GoTo Exit_SetOppositeRstBooleans
   End If
   If IsNull(ctlRowId.value) _
      Or IsNull(ctlCategory.value) Then
      MsgBox mstrcModuleName & ".SetOppositeRstBooleans -
Input control has null value"
      GoTo Exit_SetOppositeRstBooleans
   End If
    
   Rem Retrieve values and target col names from controls
   bCategoryVal = ctlCategory.value
   If bCategoryVal Then
      bCategoryValOpposite = False
   Else
      bCategoryValOpposite = True
   End If
   strRowIdCol = ctlRowId.ControlSource
   strRowIdVal = ctlRowId.value
   strCategoryCol = ctlCategory.ControlSource
   varBookmark = frm.Bookmark
   
   'Debug.Print " strRowIdVal = " & strRowIdVal
   'Debug.Print " bCategoryVal = " & bCategoryVal
   'Debug.Print " bCategoryValOpposite = " &
bCategoryValOpposite
   
   Set rst = frm.RecordsetClone
   
   Debug.Print " record count = " & rst.RecordCount
   
   Rem Finds rows with matching values - set to opposite
value
   With rst
      '.Bookmark = varBookmark
      'Debug.Print " Modified row id = " & .Fields
(strRowIdCol)
      'Debug.Print " Current category value is '"
& .Fields(strCategoryCol) & "'"
      .MoveFirst
      If Not .BOF And Not .EOF Then
         Do Until .EOF
            Debug.Print " row id val = " & .Fields
(strRowIdCol)
            Debug.Print " category val = " & .Fields
(strCategoryCol)
            bValMatch = .Fields(strCategoryCol) Eqv
bCategoryVal
            If .Fields(strRowIdCol) <> strRowIdVal And
bValMatch Then
               Debug.Print " val to be changed
detected"
               .Edit
               .Fields(strCategoryCol) =
bCategoryValOpposite
               .Update
            ElseIf .Fields(strRowIdCol) = strRowIdVal Then
               Debug.Print " row id match - no change"
            End If
            .MoveNext
         Loop
      Else
         Debug.Print "SetOppositeRstBooleans: BOF or EOF"
      End If
   End With
   
   'rst.Bookmark = varBookmark
   
   rst.Close
   
   frm.Refresh
                                              
Exit_SetOppositeRstBooleans:
   Exit Sub
    
Err_SetOppositeRstBooleans:
   MsgBox mstrcModuleName & ".SetOppositeRstBooleans Err -
 " & Err.Number & " - " & _
          Err.Description
   Select Case Err.Number
      Case 3021
         MsgBox mstrcModuleName
& ".SetOppositeRstBooleans - This error has " & _
                "been noted and is being worked on... In
the meantime, the " & _
                "user should manually set all rows except
ONE to primary='no'"
      Case Else
   End Select
   Resume Exit_SetOppositeRstBooleans
   
End Sub

• Previous message: lavanya: "progress bar"
• Next in thread: John Viescas: "Re: Subform RecordsetClone 'No Current Record' error"
• Reply: John Viescas: "Re: Subform RecordsetClone 'No Current Record' error"
• Messages sorted by: [ date ] [ thread ]