FindFirst Syntax and updating recordsets

[sxschech]

After some trial and error, I got the code to work using this syntax:

rs.FindFirst "ncesid = '" & rsold.Fields(1) & "'"

When I tried to use the actual field name for Fields(1) the code ran without posting an error message, but the values did not get updated in the table. These are what I tried:
rsold!ncessch
rsold.Fields("ncessch")

Below is the code in case need to see in context. The reason I am doing it this way is that the linked table "ccd" is joined to a linked csv text file. When I tried to do a regular update query, received a message "Updating data in a linked table is not supported by this ISAM". To get around this (instead of creating multiple temp tables), I wrote the following which gets the data from the two tables and then updates the values from null to the actual value.

Sub updateLocale_Click()
'Update locale where is null in CCD and found in text file
'20140529 ss
    Dim rsold As DAO.Recordset
    Dim rs As DAO.Recordset
    Dim stsql As String
    Dim intErr As Integer
    Dim intRecsadded As Integer
    Dim intListCount As Integer

On Error Resume Next
    intRecsadded = 0
    Set rsold = CurrentDb.OpenRecordset("SELECT tbl_CCD.unique_schid, tbl_CCD.ncesid, tbl_CCD.state, " & _
                                        "tbl_CCD.locale, Ccd_11_appended_simplified.ulocal11 " & _
                                        "FROM tbl_CCD " & _
                                        "INNER JOIN Ccd_11_appended_simplified " & _
                                        "ON tbl_CCD.ncesid = Ccd_11_appended_simplified.ncessch " & _
                                        "WHERE tbl_CCD.locale Is Null")
    
    Set rs = CurrentDb.OpenRecordset("Select * from tbl_CCD WHERE locale is null", dbOpenDynaset)
    
    rsold.MoveLast
    rsold.MoveFirst
    rs.MoveLast
    rs.MoveFirst
    'Debug.Print rs.RecordCount & " " & rsold.RecordCount
    With rs
        'Find the corresponding record in the rs table based on the id in rsold and then
        'update the value
        Do While Not rsold.EOF
            rs.FindFirst "ncesid = '" & rsold.Fields(1) & "'"
            If Not rs.NoMatch Then
                .Edit
                    rs!locale = rsold!ulocal11
                    intRecsadded = intRecsadded + 1 'keep a count of records added
                .Update
            End If
            rsold.MoveNext
            StatusBar "Processing row " & intRecsadded
        Loop
        intListCount = rsold.RecordCount
        rs.Close
        rsold.Close
    End With
    MsgBox intRecsadded & " records out of " & intListCount & " added from the list of ids.  " & _
                              "If the count differs from the total, then those records were not " & _
                              "added because..." _
                              , vbOKOnly, "Update List..."
    StatusBar ""
End Sub

 

[MajP]

If this worked rsold.Fields(1) and the others did not, my guess is you have a space in the name and you do not see it.
" ncesid" or "ncesid

 

[sxschech]

Thanks for your response. I found the problem, seems that I had been working on it for so long and trying different things, the problem is that the field is named ncesid in one table and ncessch in the other. Looks like in my select statement I picked ncesid rather than ncessch, so of course referring to rsold!ncessch wouldn't return anything.

Aside from that... (opinion/advice)
is this vba code a good start approach to updating as we will be expanding the update to multiple fields (at least 10) where those fields are null - missing data. If a particular field has a value, even if different we would not update that record so we can keep the original data. Also the text files have different headers in that the field names have the year appended to it, so I imagine that I will create a variable to handle the naming convention to standardize in the sql statement.

Example for 2011: Ccd_11_appended_simplified.ulocal11
Example for 2012: Ccd_12_appended_simplified.ulocal12

"ccd_" & styr & "_appended_simplified.ulocal" & styr