Cannot Update Recordset

[ooch1]

Hi - can someone now help with my latest problem.

Below, is my full code for an update query i'm trying to excecute. Only when it come to actually perform the update (highlighted), i get an error message sayig the recordset is not updatable.

I have checked the connection, where readonly defaults to false and adopenkeyset and adlockoptimistic should allow for updates. The only thing i can think is that it has something to do with the SQL select query.

I am clueless as to how i can overcome this issue?????

Sub T2CUpt()

Dim rs As ADODB.Recordset
Dim strOrderQy As String
Dim i As Integer

'create recordset objest
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

strOrderQy = "SELECT A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum, A.Price," & _
                       "A.StandingCharge, A.[Site status], A.[Effective From Date], A.[Ceased Date], A.NA_SOURCE_STATUS, " & _
                       "a.NA_Ctrt_STATUS " & _
        "FROM [A01-NBS_GAS_ROOTDATA_20040923] as A " & _
        "GROUP BY A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum,A.Price, " & _
                            "A.StandingCharge, A.[Site status], A.[Effective From Date], A.[Ceased Date], A.NA_SOURCE_STATUS, " & _
                            "a.NA_Ctrt_STATUS " & _
        "ORDER BY A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum;"

With rs
    .Open strOrderQy, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic
        
 
        
    'Call T2CColUpt
    Dim F As Boolean
    Dim C, C2, M, M2, S, S2 As String
    Dim rs2 As Variant
    Dim cs As String
        
    Set rs2 = rs.Clone
    
    rs.MoveFirst
    rs2.Move 1
    Do Until rs2.EOF 'loop from 1st row last row
    
        cs = Nz(rs![NA_Ctrt_STATUS], "")
        C = Nz(rs![standingcharge], ""): M = rs![meterpointref]: S = Nz(rs![siterefnum], "") ' store Charge and MeterRef of row 2
        C2 = Nz(rs2![standingcharge], ""):  M2 = rs2![meterpointref]: S2 = Nz(rs2![siterefnum], "") ' store rs2's refs
             
        If F And ((M <> M2) Or (M2 = rs2.EOF)) And _
            (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then
            ' f is true and either MeterRef changed or we're in last row, so flag preceding row and set f to false
            [COLOR=red yellow]rs![NA_Ctrt_STATUS] = "T2C"
            .Update[/color]
            Debug.Print rs.GetString
            F = False
        ElseIf Not F And (M = M2) And C <> C2 And _
            (C = 0.1055 Or C = 0.1056 Or C = 0.1089 Or C = 0.0928 Or C = 0.1193) Then ' f is false and Charge changed for same MeterRef, so set f to true
            F = True
        End If
        rs.MoveNext
        rs2.MoveNext
        ' store Charge and MeterRef of current row
    Loop ' proceed with next row
   
    rs.Close
    Set rs = Nothing
    
End With
End Sub

Thanks for the help in advance.

OOch

 

[sanders720]

Is there an rs.update anywhere?

 

[PHV]

An aggregate query is NOT updatable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

Cheers PHV, (are you going to start charging me a fee soon??). So my next question is, is it at all possible to first utilise the aggregation functionality and then update the data? If so then how could i do it?

OOch

 

[PHV]

Why using a GROUP BY clause when you don't use any aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV, you are right i suppose in this instance there is no real need for it, so i have removed. Now the code seems to work fine, except fo when if reach the EOF.

Rather than the last movenext i have now replaced it with the following code:

rs.MoveNext
If rs2.EOF Then _
 rs2.MoveLast[COLOR=green](i'm sure there is a better way of stopping it moving forward???)[/color]
 Else rs2.MoveNext

For some reason, all my code doesn't seem to recognise the rs.EOF and so every time i run through i get the EOF is ture error, even though it should have been accomodated for. Does VBA recognise the EOF of a cloned recordset?

OOch

 

[ooch1]

For confimation it is that last sentence should have read rs2.EOF, because it never gets chance to reach the EOF of rs.

OOch