move next???

[CHAOSinACT]

can anyone think what a rs.MoveNext would fail? it can't move on from first record and there are many. the compiler stops me on

rsJobsList.MoveNext

to debug. frankly weird i'm looking at query now, it has 37 records. seems stupidly simply....(yes, rsJobsList is a legit open adodb recordset - i'm using it elsewhere in code ok, go to move to next record and it says
index or primary key cannot contain "null" value. all i can think is that calculated values in the query causes the problem??? otherwise i just can't see it.

 

[CHAOSinACT]

removed calculated fields, no joy there either.... @#$@#$

 

[boblarson]

Can you give the rest of the code which includes all of the ADO code you are using?

 

[CHAOSinACT]

hey bob, pleasure as always
oh yeah, you can have it. there is more but this is all that effects... man this is ticking me off:

        Dim rsJobsList As ADODB.Recordset
        
        Set rsJobsList = New ADODB.Recordset
        
        rsJobsList.ActiveConnection = CurrentProject.Connection
        rsJobsList.Source = "qryRealJobList"
        rsJobsList.CursorType = adOpenDynamic
        rsJobsList.LockType = adLockOptimistic
        'open costing recordset
        rsJobsList.Open



        Dim rsCivilActivitySlips As ADODB.Recordset
        
        Set rsCivilActivitySlips = New ADODB.Recordset
        
        rsCivilActivitySlips.ActiveConnection = CurrentProject.Connection
        rsCivilActivitySlips.Source = "MYOB_CIVIL_qryActivitySlips_OPT"
        rsCivilActivitySlips.CursorType = adOpenDynamic
        rsCivilActivitySlips.LockType = adLockOptimistic
        'open costing recordset
        rsCivilActivitySlips.Open


    Dim TempCacheAmount As Currency

    TempCacheAmount = 0
    If Not rsJobsList.EOF Then
        rsJobsList.MoveFirst
        Do While Not rsJobsList.EOF
            rsCivilActivitySlips.Filter = "JobNumber like " & RJobNum(rsJobsList![Civil Job Number].Value) & "*"
            If Not rsCivilActivitySlips.EOF Then
                TempCacheAmount = TempCacheAmount + rsCivilActivitySlips!TotalSpent.Value
            Else
                TempCacheAmount = 0
            End If
            rsJobsList!CivilActivityCache.Value = TempCacheAmount
            rsJobsList.MoveNext      ' HANGS HERE<<<<<
        Loop

        
        TempCacheAmount = 0
    End If

i

 

[CHAOSinACT]

and here is the table - it complains on the first record i can check while in debugging (just to prove i'm not nuts)

 

[Banana]

You definitely need to put in the .Update command before you leave the record else MoveNext or anything navigation-related will not work. It's a known quirk of ADO - though the Update method is technically optional and should work implicitly, it doesn't and need to be explicit.

Also, you aren't actually using Dynamic cursors. Jet does not support Dynamic Cursors - ADO will silently substitute invalid specifications with closest specification (could be a Keyset or Static cursor in fact) without any errors.

 

[boblarson]

And a few other items:

1. You don't need this -
rsJobsList.MoveFirst

When you open it will be at the first record

2. A simple

Do Until rsJobsList.EOF

instead of

Do While Not rsJobsList.EOF

is good.

3.

 

[boblarson]

Hit the enter key by mistake -

3.

            rsJobsList!CivilActivityCache.Value = TempCacheAmount
            rsJobsList.MoveNext      ' HANGS HERE<<<<<
        Loop

would need:

[B]            rsJobsList.Edit[/B]
            rsJobsList!CivilActivityCache.Value = TempCacheAmount
[B]            rsJobsList.Update[/B]
            rsJobsList.MoveNext      ' HANGS HERE<<<<<
        Loop

 

[CHAOSinACT]

ok. i need the query to hold data (its a cache) and dynamic seemed the best intillisense help there, still does lol.


i added a rsJobsList.Update statement before the movenext, now it hangs on the update. i can do this with a query cant i? really don't want to use a table for certain reasons.

 

[boblarson]

ok. i need the query to hold data (its a cache) and dynamic seemed the best intillisense help there, still does lol.


i added a rsJobsList.Update statement before the movenext, now it hangs on the update. i can do this with a query cant i? really don't want to use a table for certain reasons.

Before you can update you have to

.EDIT

 

[CHAOSinACT]

btw
rsJobsList.Edit is considered to be "not a valid property" by access....

 

[CHAOSinACT]

check it out...

 

[boblarson]

Are you sure that query is updateable then?

 

[Banana]

As I said, ADO will silently substitute invalid specifications with something else. This may not be relevant (see Bob's last question). When you are learning about ADO, you may find it quite helpful in your test code to do something like this:

With rs
   .CursorLocation = ...
   .CursorType = ...
   .LockType = ...
   .Open ...
   Debug.Print .CursorLocation
   Debug.Print .CursorType
   Debug.Print .LockType
End With

This will help you get familiar with when and under what circumstances ADO will silently substitute an invalid specification with something else and correct your code since sometime the substitute may not be desirable.

HTH.

 

[CHAOSinACT]

yep...just typed a value into the cache manually and it let me save it and reopen so i'll say yes. if the rsCivilActivitySlips.CursorType = adOpenDynamic isn't right feel free to suggest something else...access says i can use it here.

 

[boblarson]

Personally, I used to use ADO a long time ago because it seemed easier to use than DAO, but these days I would rather use the "language of Access" and go with DAO. Why not use it instead of ADO?

 

[Banana]

Try the code above. It'll tell you that you have a Keyset cursor instead of Dynamic. From help file:

Recordset Behavior
The Microsoft Jet database engine does not support dynamic cursors. Therefore, the OLE DB Provider for Microsoft Jet does not support the adLockDynamic cursor type. When a dynamic cursor is requested, the provider will return a keyset cursor and reset the CursorType property to indicate the type of Recordset returned. Further, if an updatable Recordset is requested (LockType is adLockOptimistic, adLockBatchOptimistic, or adLockPessimistic) the provider will also return a keyset cursor and reset the CursorType property.

Where did you read that you can use it?

 

[CHAOSinACT]

Where did you read that you can use it?

Access suggest it in Intellisense. seemed logical.

 

[boblarson]

Here's your code rewritten with DAO (many fewer lines of code too):

    Dim rsJobsList As DAO.Recordset


    ' set and open the recordset
    Set rsJobsList = CurrentDb.OpenRecordset("qryRealJobList")



    Dim rsCivilActivitySlips As DAO.Recordset

    Set rsCivilActivitySlips = CurrentDb.OpenRecordset("MYOB_CIVIL_qryActivitySlips_OPT")


    Dim TempCacheAmount As Currency

    TempCacheAmount = 0
    If Not rsJobsList.EOF Then

        Do Until rsJobsList.EOF
            rsCivilActivitySlips.Filter = "JobNumber like " & RJobNum(rsJobsList![Civil Job Number].Value) & "*"
            If Not rsCivilActivitySlips.EOF Then
                TempCacheAmount = TempCacheAmount + rsCivilActivitySlips!TotalSpent.Value
            Else
                TempCacheAmount = 0
            End If
            rsJobsList.Edit
            rsJobsList!CivilActivityCache.Value = TempCacheAmount
            rsJobsList.Update
            rsJobsList.MoveNext
        Loop


        TempCacheAmount = 0
    End If

    rsJobsList.Close
    rsCivilActivitySlips.Close

    Set rsJobsList = Nothing
    Set rsCivilActivitySlips = Nothing

 

[CHAOSinACT]

Personally, I used to use ADO a long time ago because it seemed easier to use than DAO, but these days I would rather use the "language of Access" and go with DAO. Why not use it instead of ADO?

no prejudices, willing to redo it anyway i have to lol. in my learning (and i may have this backwards) but isn't ado the new one and dao the one (supposedly) being phased out?