Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: trying to change and update a Rs value

    trying to change and update a Rs value
    Do While Not rs.EOF
    i = CInt(rs(0))
    rs.Edit <--- NO SUCH THING
    Select Case UCase(Left(rs("CpnyID"), 1))
    Case "M"
    rs(0) = i & "-M"
    Case "R"
    rs(0) = i & "-R"
    Case "A"
    rs(0) = i & "-A"
    Case Else
    MsgBox "Error Evaluating Company " & rs("CpnyID"), , "Error Company : " & rs("CpnyID")
    End Select
    rs.Update
    If Not rs.EOF Then rs.MoveNext
    Loop
    End With
    End Sub


    Dont know what im doing wrong i get the msg Cannot Update. Database or object is read only. WHat should i be doing that im not?

    THnks

    ~M

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: trying to change and update a Rs value

    Originally posted by mikezcg
    trying to change and update a Rs value
    Do While Not rs.EOF
    i = CInt(rs(0))
    rs.Edit <--- NO SUCH THING
    Select Case UCase(Left(rs("CpnyID"), 1))
    Case "M"
    rs(0) = i & "-M"
    Case "R"
    rs(0) = i & "-R"
    Case "A"
    rs(0) = i & "-A"
    Case Else
    MsgBox "Error Evaluating Company " & rs("CpnyID"), , "Error Company : " & rs("CpnyID")
    End Select
    rs.Update
    If Not rs.EOF Then rs.MoveNext
    Loop
    End With
    End Sub


    Dont know what im doing wrong i get the msg Cannot Update. Database or object is read only. WHat should i be doing that im not?

    THnks

    ~M
    Let's see the whole procedure that you are using. Are you trying to use ADO or DAO for the recordset?

  3. #3
    Join Date
    Oct 2003
    Posts
    311

    Re: trying to change and update a Rs value

    I think it has something to do with the table, i created the table from a qry on a linked table, but then deleted the tbl and then created it again, that didnt work either.

    sql = WrkTbl
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
    AdjCkNum

    Private Function WrkTbl() As String

    WrkTbl = "SELECT xtblMoRecSol.CheckNum, xtblMoRecSol.CheckDate, xtblMoRecSol.Name, xtblMoRecSol.CheckAmount, xtblMoRecSol.CpnyID " & _
    " FROM xtblMoRecSol " & _
    " GROUP BY xtblMoRecSol.CheckNum, xtblMoRecSol.CheckDate, xtblMoRecSol.Name, xtblMoRecSol.CheckAmount, xtblMoRecSol.CpnyID " & _
    " ORDER BY xtblMoRecSol.CheckNum; "
    end function

    Sub AdjCkNum()
    Dim i As Integer
    With rs
    GetMeRsInfo
    Do While Not rs.EOF
    i = CInt(rs(0))

    Select Case UCase(Left(rs("CpnyID"), 1))
    Case "M"
    rs(0) = i & "-MGMT"
    Case "R"
    rs(0) = i & "-RE"
    Case "A"
    rs(0).Value = i & "-AREC"
    Case Else
    MsgBox "Error Evaluating Company " & rs("CpnyID"), , "Error Company : " & rs("CpnyID")
    End Select
    rs.Update
    If Not rs.EOF Then rs.MoveNext
    Loop
    End With
    End Sub

  4. #4
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: trying to change and update a Rs value

    Originally posted by mikezcg
    trying to change and update a Rs value
    Do While Not rs.EOF
    i = CInt(rs(0))
    rs.Edit <--- NO SUCH THING
    Select Case UCase(Left(rs("CpnyID"), 1))
    Case "M"
    rs(0) = i & "-M"
    Case "R"
    rs(0) = i & "-R"
    Case "A"
    rs(0) = i & "-A"
    Case Else
    MsgBox "Error Evaluating Company " & rs("CpnyID"), , "Error Company : " & rs("CpnyID")
    End Select
    rs.Update
    If Not rs.EOF Then rs.MoveNext
    Loop
    End With
    End Sub


    Dont know what im doing wrong i get the msg Cannot Update. Database or object is read only. WHat should i be doing that im not?

    THnks

    ~M
    How did you define the RecordSet ?
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  5. #5
    Join Date
    Oct 2003
    Posts
    311

    Re: trying to change and update a Rs value

    Originally posted by Mixirica
    How did you define the RecordSet ?
    YEs -

    Public rs As ADODB.Recordset
    Public sql As String

    Sub Main()
    Set rs = New ADODB.Recordset

    rs.Open "Delete * from tblMoRecSol", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
    sql = GetSolRecSQL
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

    sql = WrkTbl
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
    AdjCkNum
    rs.Close
    Set rs = Nothing

    End Sub

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: trying to change and update a Rs value

    Originally posted by mikezcg
    YEs -

    Public rs As ADODB.Recordset
    Public sql As String

    Sub Main()
    Set rs = New ADODB.Recordset

    rs.Open "Delete * from tblMoRecSol", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
    sql = GetSolRecSQL
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText

    sql = WrkTbl
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
    AdjCkNum
    rs.Close
    Set rs = Nothing

    End Sub

    .Edit is not a method of the ADODB recordset object.

    It applies to DAO. For just changing a field value just omit the .Edit.


    Gregg

  7. #7
    Join Date
    Oct 2003
    Posts
    311

    Re: trying to change and update a Rs value

    Originally posted by basicmek
    .Edit is not a method of the ADODB recordset object.

    It applies to DAO. For just changing a field value just omit the .Edit.


    Gregg
    i tried that also. For some reason my rs is read only, i went into the table it self and changed data and it wasnt a problm. How can i change the read only status? i think it may have some thing to do with the 1st qry reads from a linked table.

    M~

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Wink Re: trying to change and update a Rs value

    Originally posted by mikezcg
    i tried that also. For some reason my rs is read only, i went into the table it self and changed data and it wasnt a problm. How can i change the read only status? i think it may have some thing to do with the 1st qry reads from a linked table.

    M~
    This may not be what you have time for but if it were me, I would try some of the same code on something simple. Back up and try pieces of the code at a time and verify the results as you go. I like to use MsgBox functions and look at the RecordCount of my recordsets to see if I have what I expect. Step through it line by line and fix it as you go.

    Where in the code is it failing for you?

    Keep trying.

    Gregg

  9. #9
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: trying to change and update a Rs value

    Hi guys !

    I´ve always used:

    Set rs = New RecordSet

    (I don´t know if we´ll have a problem using Set rs = New ADODB.Recordset, but...)
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  10. #10
    Join Date
    Oct 2003
    Posts
    311

    Re: trying to change and update a Rs value

    Originally posted by Mixirica
    Hi guys !

    I´ve always used:

    Set rs = New RecordSet

    (I don´t know if we´ll have a problem using Set rs = New ADODB.Recordset, but...)

    If you dont use ADODB.recorset, you are being ambiuous (I cant spell ) and i think your working with dao or atleast your not being explicit about using ado

  11. #11
    Join Date
    Oct 2003
    Posts
    311

    Re: trying to change and update a Rs value

    Originally posted by basicmek
    This may not be what you have time for but if it were me, I would try some of the same code on something simple. Back up and try pieces of the code at a time and verify the results as you go. I like to use MsgBox functions and look at the RecordCount of my recordsets to see if I have what I expect. Step through it line by line and fix it as you go.

    Where in the code is it failing for you?

    Keep trying.

    Gregg
    Its failing on the case statement where i try to change the value, thats where i get the msg its a readonly RS

    I like your idea, i will try doing the whole thing but starting off qrying a table thats in the db not a linked table,,, but still just cuase im qry a linked table to start, doesnt mean my table 2 rs down stream should be read only
    !~

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: trying to change and update a Rs value

    Originally posted by mikezcg
    Its failing on the case statement where i try to change the value, thats where i get the msg its a readonly RS

    I like your idea, i will try doing the whole thing but starting off qrying a table thats in the db not a linked table,,, but still just cuase im qry a linked table to start, doesnt mean my table 2 rs down stream should be read only
    !~
    In relation to Recordset or ADODB.Recordset, if you have your references set up where the ADODB reference is above the DAO reference it will be the default when you declare a variable as a recordset. If you use both in your code, it is good practice to put the library prefix first (DAO.Recordset / ADODB.Recordset).

    Try leaving the options argument (the last one for the open method) blank instead of adCmdText.

    Let me know how it goes.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •