Update a table through VBA

[Thompyt]

I am trying to update the [Units].COID1 using VBA.

Private Sub COLevel_Click()
Dim rv As DAO.Recordset
Dim A As Integer, B As String, C As String, COID1 As Integer

Set rv = CurrentDb.OpenRecordset("Units")

If rv.BOF And rv.EOF Then
                rv.Close
            Else
                rv.MoveLast
                rv.MoveFirst
    Do While Not rv.EOF
        
        For A = 1 To 10
            B = Chr(64 + A)
            C = "'" & B & " *'"
        
        If rv!Netbase Like C And rv!Echelon = "CO" Then
                    COID1 = A
        Else
                    COID1 = 0
        End If

        rv.Edit
        rv!COID1 = COID1
        rv.Update
        rv.Close
        
        Next A
              rv.MoveNext
    Loop

End If

Set rv = Nothing

End Sub

I error on: "Runtime Error '3420'; Object invalid or no longer set."
If rv!Netbase Like C And rv!Echelon = "CO" Then

What I am trying to do is query the table where the NetID begins with the letter A through J using chr(64 + A) and Echelon ="CO" using the Like Command and adding in B. This should output to the corresponding record in Column COID1.

Any suggestions?

Thanks

[ssanfu]

You shouldn't close the recordset until you aren't using it any more.

    Do While Not rv.EOF
        
        For A = 1 To 10
            B = Chr(64 + A)
            C = "'" & B & " *'"
        
        If rv!Netbase Like C And rv!Echelon = "CO" Then
                    COID1 = A
        Else
                    COID1 = 0
        End If

        rv.Edit
        rv!COID1 = COID1
        rv.Update
        rv.Close
        
        Next A
              rv.MoveNext
    Loop

Try

Private Sub COLevel_Click()
    Dim rv As DAO.Recordset
    Dim A As Integer, B As String, C As String, COID1 As Integer

    Set rv = CurrentDb.OpenRecordset("Units")

    If rv.BOF And rv.EOF Then
        rv.Close
    Else
        rv.MoveLast
        rv.MoveFirst
        Do While Not rv.EOF

            For A = 1 To 10
                B = Chr(64 + A)
                C = "'" & B & " *'"

                If rv!Netbase Like C And rv!Echelon = "CO" Then
                    COID1 = A
                Else
                    COID1 = 0
                End If

                rv.Edit
                rv!COID1 = COID1
                rv.Update

            Next A
            
            rv.MoveNext
        Loop

    End If

    rv.Close
    Set rv = Nothing

End Sub

[Thompyt]

Thanks Steve!