Re: Subform RecordsetClone 'No Current Record' error

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Larry (anonymous_at_discussions.microsoft.com)
Date: 02/24/04 

Date: Tue, 24 Feb 2004 15:24:29 -0800

John,

I had that thought about the record not being saved yet,
but the more I looked at the code and then considered the
fact that the same code seemed to be working perfectly
fine with 3 other subforms, the less I started to think
this was THE issue. And (like your comment) I just
couldn't see why that would specifically provide a '3021 -
no current record' error.

I've run the code MANY times with all the debug.print
statements engaged.

Here's what the output looks like when it's working
correctly (I updated the 3d row from a 'no' value to be
the 2d 'yes' value in a subform):

cboPrimaryYN_AfterUpdate
  cbo.Value = -1
  lngDuplicateCount = 1
SetOppositeRstBooleans
  strRowIdVal = 1077641898
  bCategoryVal = True
  bCategoryValOpposite = False
  record count = 3
    row id val = 1
      category val = False
    row id val = 1077641898
      category val = False
      row id match - no change
    row id val = -1705331741
      category val = True
      val to be changed detected

You can see here that your statement about the record
under alteration not being saved is correct. I set the
control on the 2d record to 'yes', but the recordset shows
a 'false' value above. Despite this, the final result on
the subform is correct - the 2d row on the subform IS
updated to a 'yes' value, and the 3d row is changed to
a 'no' value.

Here's what the output looks like when it results in
the "3021 - no current record error" (Also a case where I
edited the 3d row on a subform from a 'no' to be the
2d 'yes' in the subform):

cboPrimaryYN_AfterUpdate
  mfrm name is 'email_subform'
cboPrimaryYN_AfterUpdate
  mfrm name is 'email_subform'
  lngDuplicateCount = 1
SetOppositeRstBooleans
  strRowIdVal = -1232239679
  bCategoryVal = True
  bCategoryValOpposite = False
  record count = 3
    row id val = 1954785984
      category val = False
    row id val = -1232239679
      category val = False
      row id match - no change
    row id val = 1766822120
      category val = True
      val to be changed detected

You'll note in this last case that the record count (3) is
correct to what I see represented on the form. But in this
case when I reach the 3d record, no indication is provided
in the immediate window that the record was ever reached.

Incidentally, if I comment out the ".edit", ".Fields
(strCategoryCol) = bCategoryValOpposite", and ".Update"
steps in the procedure, the error message never comes up.

Here's an example of the same situation as the previous
example, but with the ".Edit", ".Fields(strCategoryCol)
=..." and ".Update" steps commented out:

cboPrimaryYN_AfterUpdate
  mfrm name is 'email_subform'
cboPrimaryYN_AfterUpdate
  mfrm name is 'email_subform'
  lngDuplicateCount = 1
SetOppositeRstBooleans
  strRowIdVal = -1232239679
  bCategoryVal = True
  bCategoryValOpposite = False
  record count = 3
    row id val = 1954785984
      category val = False
    row id val = -1232239679
      category val = False
      row id match - no change
    row id val = 1766822120
      category val = True
      val to be changed detected

So it seems that the 3d row is present in the recordse,
but the problem arises for some reason when I include
statements to edit/update that record. I'm still puzzled...

Both the subforms used above are based on explicitly
constructed SELECT statements based on a single table, set
as dynasets with no locks. I can verify that the subform
rows used in the "email" example are in fact present in
the underlying table as they should be...

>-----Original Message-----
>Larry-
>
>You say that you're calling this code from the
AfterUpdate event of the
>control. However, the row might still be "dirty" (not
saved) when this code
>runs. If that's the case, then the new value hasn't been
saved yet when you
>try to examine it using RecordsetClone. I'm not sure why
that would give
>you a No Current Record - but that's the only thing I can
think of that's
>causing this. Have you tried setting Halt on All Errors
and then looking at
>the row the code thinks it is working with? You clearly
have lots of
>commented Debug statements in this. Do they give you a
clue if you
>un-comment them?
>
>To check to see if the row has been saved and if not
force a save, do this:
>
> If frm.Dirty Then
> frm.Dirty = False
> End If
>
>--
>John Viescas, author
>"Microsoft Office Access 2003 Inside Out"
>"Running Microsoft Access 2000"
>"SQL Queries for Mere Mortals"
>http://www.viescas.com/
>(Microsoft Access MVP since 1993)
>"Larry" <anonymous@discussions.microsoft.com> wrote in
message
>news:0bb001c3fb1d$1fa7f470$a501280a@phx.gbl...
>> 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
>>
>
>
>.
>

Relevant Pages

  • Subform RecordsetClone No Current Record error
    ... of rows all displayed in an Access 2000 subform. ... The "after update" event of the form control ... Dim bCategoryValOpposite As Boolean ...
    (microsoft.public.access.formscoding)
  • Re: Is this a Sound Database Design? -- procedurenames must be unique in same module
    ... Since these are PRIVATE functions, it is ok to have another Private function with this name in ANOTHER module sheet. ... Created a form with a subform in it, named it TabSubform and then created a command button, named it SwitchTabs. ... Dummy is an unbound control -- really tiny because I use labels instead of command buttons and a label does not get the focus ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is this a Sound Database Design? -- code: SwitchTabs
    ... The "command buttons" (put in quotes because I actually used label ... Dummy is an unbound control -- really tiny because I use labels instead ... Sometimes, the subforms do not have linking fields, like a lookup subform ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is this a Sound Database Design? -- code: SwitchTabs
    ... The "command buttons" (put in quotes because I actually used label controls) to switch subforms are colored so that you can see which one is active ... TabID is an unbound control -- it is set when the form loads ... Sometimes, the subforms do not have linking fields, like a lookup subform ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Can I control which Tab is selected ... code to swap subform sourceObject
    ... The "command buttons" to switch subforms are colored so that you can see which one is active ... I define a calculated control on the subform and set it equal to the specified control on the main form for LinkMasterFields ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.forms)