Subform RecordsetClone 'No Current Record' error

[Larry]

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

 

[John Viescas]

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]

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...

 

[Larry]

John,

I actually tried your proposed "save" method and now the
procedure seems to work corectly for the "email" example
outlined in my other response to your message.

Now I'm really confused. As outlined in my other message,
I don't really see a clear distinction between either
the "email" example or the "loctn" example, yet
the "email" example seems to need a non-"dirty" record as
the starting point, while application in the "loctn"
example doesn't seem to care.

If you can propose an explanation, I'd REALLY like to be
able to understand this...

Even without the explanation, I owe you a "thank you". I
just didn't think this could be the problem if the code
worked correctly in 3/5 subforms it was used with...

 

[Guest]

I accidentally pasted in the wrong output for example two
in my prior message containing example immediate window
output from debug.print statemetns...

Here's how it SHOULD have been... This is an example that
shows the error when 3 subform records are displayed with
the 1st and 2d set to 'no', the 3d set to 'yes'. In
response to my change of the 2d row to become the 2d 'yes'
value in the set of records, here is the immediate window
output.

This 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

The ".Edit", ".Fields(strCategoryCol)=..." and ".Update"
statements that seem to trigger the error should only be
encountered when the record generating the "val to be
changed detected" immediate window output is generated...

 

[John Viescas]

I don't really have an explanation - other than the fact that I know JET can
get upset if you have two recordsets open editing the same data and one is
in a "dirty" condition. A study done recently my another MVP showed that
Access actually opens *another* database object when you set a recordest
object equal to the RecordsetClone. You basically have two separate
sessions editing the same records. If one is "dirty," strange things can
happen. It is akin to the error a user sees when another user is in the
process of editing the same record. I think forcing the save is the best
solution - your code is then editing a "clean" recordset that is not in
conflict with what is going on in the form.

--
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)

 

[Guest]

Well, after finding that your proposed fix in fact did
result in correct operation of the procedure, I ended up
thinking that it MUST be the case that Access is using two
different copies of the "subform rows" in a way that is
more complex than I was understanding from the
explanations I'd read. Seems like there is no alternative
when realizing that the code WORKED in MOST situations
where it was applied, but NOT in all...

Again, thanks for the assistance...