Run time error: 3021 No current record found in multiple table recordset loop

Question

I have a form that is filled with questions for different equipment. The user selects the equipment type then answers the questions for each category of tools. Each tool category is a table. Please see picture for example.

I am attempting my to add a new record into each table using a autonumber from the LineKey table as my ID, my primary keys. However I am getting a:

3021 error `No current record found'

when I am looping through each table adding the new record. If I run the code it errors out in the first loop at rec.Edit. If I walk through the code with breaks it goes through the first table loop but then errors out at rec.Edit for the second table. I am not sure what I am missing here.

CODE:

Private Sub btnUpDatabase_Click()
    Dim i As Integer, j As Integer, e As Integer, ID As Integer
    Dim db As Database
    Dim rec As Recordset
    Dim Value As String

    Set db = CurrentDb

    'Create Record ID "Key" in lineKey table
    Set rec = db.OpenRecordset("SELECT * FROM LineKeys")
    rec.AddNew
        rec("Line") = Me.txtLine.Value
        rec("Part Number") = Me.txtPartNumber.Value
        rec("Equipment") = Me.cmbEquip.Value
    rec.Update

    Set rec = CurrentDb.OpenRecordset("Select @@Identity")
    ID = rec(0)
    Set rec = Nothing

    Select Case cmbEquip.Value

        Case "OP10 Lathe"
            Dim strArray(0 To 6) As String
              strArray(0) = "Tool Holders"
              strArray(1) = "Chuck Jaws"
              strArray(2) = "Jaw Grippers"
              strArray(3) = "Drawbars"
              strArray(4) = "Actuators"
              strArray(5) = "Chatter Pin"
              strArray(6) = "Chatter Pin Spring"

            j = 0
            For e = 0 To 6
                Set rec = db.OpenRecordset("SELECT * FROM [" & strArray(e) & "]")
                rec.AddNew
                rec.Fields("ID") = ID

                For i = 1 To 7
                    rec.Edit
                    rec.Fields("A" & i & "") = Me.Controls("cmb" & j & i & "").Value
                    rec.Update
                Next

                j = j + 1

                Set rec = Nothing
            Next

    End Select

    Set rec = Nothing
    Set db = Nothing

End Sub

*Each tool category is a table.* ... reconsider this structure and normalize (organize data into logical related groupings) with one table having a tool category column — Parfait, Sep 12 '17 at 17:26
The problem is your `Currentdb.OpenRecordset("SELECT @@Identity")`. It should be `db.OpenRecordset("SELECT @@Identity")`, the db-object that has executed the insert or you won't get the correct id. Also take Parfait's advice and normalize your database. — BitAccesser, Sep 12 '17 at 22:57
Ups, seems I am wrong [with the scope.](https://stackoverflow.com/questions/5942781/select-identity-not-scoped-by-db-object#answer-5942862) — BitAccesser, Sep 12 '17 at 23:11
@Parfait Yes I agree but I will be restructuring it after I build the code... after I fix this issue. — holi4683, Sep 13 '17 at 14:34

score 1 · Accepted Answer · answered Sep 12 '17 at 17:48

Possibly, you are conflating the .AddNew and .Edit processes where the first was not fully closed and hence record locked.

Consider replacing the .Edit and move the .Update outside the nested For loop. So you are updating an entire row in one call and not individual columns iteratively in a loop. Also you can simplify your outer array looping:

      For each e In Array("Tool Holders", "Chuck Jaws", "Jaw Grippers", "Drawbars", _
                          "Actuators", "Chatter Pin", "Chatter Pin Spring")
            Set rec = db.OpenRecordset("SELECT * FROM [" & e & "]")

            rec.AddNew
            rec.Fields("ID") = ID
            For i = 1 To 7
                rec.Fields("A" & i & "") = Me.Controls("cmb" & j & i & "").Value
            Next i
            rec.Update

            j = j + 1
            Set rec = Nothing
        Next e

Once again, I advise not storing separate, similarly-structured tables by different values of a typology such as tool categories. Incorporate all into one Tool table without many (if any) looping. In this way, you do not change your schema for new categories and storage is more efficient as many rows are inexpensive compared to many tables.

Between trail and error and what you gave me I figured it out — holi4683, Sep 22 '17 at 13:15

Run time error: 3021 No current record found in multiple table recordset loop

1 Answers1