Run time error '3164': 'Field cannot be updated' - Access VBA issue

Question

I'm having an issue with a piece of VBA I've written for Access. I have a table with a concatenation field 'Concat' (string field) and field called 'Age' (integer field with numerical values).

There are another 61 fields (named '0','1','2'...'60' respectively) where the code needs to work though: I want the code to loop through and, per record entry - for the VBA to Dlookup using the Concat + age fields to another table (called: tbl_Final_Probabilities) and pull back a probability and populate each of these 61 fields with the correct probability. These fields are set up at a numerical field, data type as Single.

The code pulls the correct probability but when I try to update the record for that field at the code line: "rs.Fields(a) = b" (also highlighted in code), I get the error message: "Run time error '3164': 'Field cannot be updated'".

All help welcome on how I need to correct this please, the code used is below.

Punch and pie.

Code:

Dim rs As DAO.Recordset
Dim a As Integer
Dim b As Single
Dim lookup As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Circuit_plus_prob")

For a = 0 To 60

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst

            Do Until rs.EOF = True

            rs.Edit

            lookup = rs!Concat & (rs!age + a)

            b = DLookup("Prob_Date", "tbl_Final_Probabilities", "Concat2 = '" & lookup & "'")

            rs.Fields(a) = b  '- CODE BREAKS DOWN HERE

            rs.Update

            rs.MoveNext

            Loop

    End If

Next a

rs.Close
Set rs = Nothing

Thanks in advance for any and all help.

Gustav Gustav · Accepted Answer · 2016-10-28T14:41:10

You loop is turned inside out:

Dim rs As DAO.Recordset
Dim a As Integer
Dim b As Single
Dim lookup As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Circuit_plus_prob")

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        rs.Edit
        For a = 0 To 60
            lookup = rs!Concat & (rs!age + a)
            b = DLookup("Prob_Date", "tbl_Final_Probabilities", "Concat2 = '" & lookup & "'")
            rs.Fields(a).Value = b
        Next
        rs.Update
        rs.MoveNext
    Loop
End If
rs.Close

Set rs = Nothing

Run time error '3164': 'Field cannot be updated' - Access VBA issue

Code:

2 Answers