Variables in an Update query

The code structure you have doesn't ever move back to the first record of rs1 (which reads from your Data_Intermediate table).

This means that the update will only be performed for the FIRST record of your Determinats table!

Is there any particular reason you're not just using a single UPDATE query (with a JOIN)?

Please don't take offense, but your approach (as well as the design flaw I've just highlighted) isn't very efficient.

I'm still trying to get my head round what you're trying to achieve, but won't this do what you want?

Public Sub XXXCheckFieldsXXX()
    DoCmd.RunSQL "UPDATE Data_Intermediate INNER JOIN Determinants " & _
                 "ON Determinants.DeterminantID = Data_Intermediate.DeterminantID " & _
                 "SET Data_Intermediate.DeterminantID = Determinants.DeterminantID " & _
                 "WHERE Determinants.[EA Code] = Data_Intermediate.DeterminantID"
End Sub

Select allOpen in new window

Hi Jez no offense taken, its good to know where my design is going wrong. Thanks for the second update on the correction of the code!!! It worked brilliantly.

Hi matthewspatrick, I think your solution worked for my original question, but it was my code that wasn't upto scratch - thanks for the numeric and text difference. And apologies as Jez's adaptation fixed my code so had to have most of the points.

Thanks guys!

If you want to go the record set route, you should have something like this:

Public Sub XXXCheckFieldsXXX()
    Dim rstDeterminants As Recordset
    Dim rstDataIntermediate As Recordset
 
    Set rstDeterminants = CurrentDb.OpenRecordset("Determinants", dbOpenSnapshot, dbReadOnly)  ' Change name of source table
    If rstDeterminants.EOF Then
        MsgBox "No records!"
        Exit Sub
    End If
    Set rstDataIntermediate = CurrentDb.OpenRecordset("Data_Intermediate", dbOpenTable)        ' Change name of destination table
 
    ' For each Data_Intermediate record
    Do Until rstDataIntermediate.EOF
 
        ' For each Determinants record
        rstDeterminants.MoveFirst
        Do Until rstDeterminants.EOF
            If rstDeterminants![EA Code] = rstDataIntermediate!DeterminantID Then
                rstDataIntermediate.Edit
                rstDataIntermediate!DeterminantID = rstDeterminants!DeterminantID
                rstDataIntermediate.Update
            End If
            rstDeterminants.MoveNext
        Loop
        rstDataIntermediate.MoveNext
    Loop
End Sub

Select allOpen in new window

The problem with the code in your question (apart from it not working!) is that you've got the overhead of compiling an SQL statement for EACH combination of records in your Determinants and Data_Intermediate tables.

Since you're looping through the Data_Intermediate table records anyway, it makes sense to edit each record as you go by (so to speak).

Once you add in the missing MoveFirst invocation (see ID 21324051 above), you can see that even the corrected record set strategy is rather inefficient, since it repeatedly goes back to the first record of the Determinants table for each record of the Data_Intermediate table.

By paring down the entire algorithm so a single UPDATE query Access only has to read each table once, which not only makes the whole process much faster but also gives you less code to maintain.

Less is more, as they say!  :-)

Just for future reference, you might like to bear in mind the other little tweaks I've made as well:

1. Removed unused variables
2. Reordered code to ONLY open Determinants record set when no records
3. Opened record sets as read-only where appropriate
4. Removed repeated MoveNext code
5. Relaced variables with direct record set field references
6. Used more consise field referencing

Hope that helps explain what I changed!  :-)

Jez that is fantastic!!

Thanks for taking both the time to correct my code and explain it. I've been punching above my weight with VBA code trying to learn it so this really helps a great deal.

Its all much appreciated, keep up the good work!!

Have a knockout time!  :-)