Re: Subform RecordsetClone 'No Current Record' error

Tech-Archive recommends: Fix windows errors by optimizing your registry

anonymous_at_discussions.microsoft.com
Date: 02/25/04 

Date: Wed, 25 Feb 2004 06:17:21 -0800

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

>-----Original Message-----
>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)
>"Larry" <anonymous@discussions.microsoft.com> wrote in
message
>news:0ddb01c3fb2e$61d19b60$a301280a@phx.gbl...
>> 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...
>> >-----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

  • Re: Recordset or Array?
    ... We have control over it. ... One of the reasons I'd prefer to use recordsets instead of arrays. ... using an array if that will perform better. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: DataSets in CF?
    ... But the message I have been getting is that for recordsets as large as ... the one I will be working with that, on a PDA, I should not be using a ... >:|Data from DataSet is not duplicated in the control if you use Data Binding ... >:|Best regards, ...
    (microsoft.public.dotnet.framework.compactframework)