CODEWITHSUNDEEP
vbams-access
Luca Guarro
Luca Guarro

Reputation: 1168

Editing a record immediately after creating it

For my paste function, in certain cases, I need to be able to create a record and immediately edit it afterwards. This is because I need to know the value that gets generated for the AutoNumber field. The error I get is "Update or CancelUpdate without AddNew or Edit." I marked where this error pops up in my code sample.

I pasted my entire paste function in case that it might help. Though the code that I am unsure of how to properly execute is in the bottom 4th (everything below the ***).

If you want to know exactly what I am trying to do feel free to read the rest of the post though it should be sufficient without.

Essentially what I am trying to do is for each record in my clipboard, I want to make a duplicate of it - copying values of all the fields. There are exceptions however. The ones of interest are the AutoNumber field, "ESDNodeID", and "ParentID" which is the ESDNodeID of the record that the record inherits from.

The clipboard (which contains the ESDNodeID and the ParentID) of the nodes that already exist which are being copied are sorted so that if a child record has a parent record, its parent is the next one in the list. So my idea is that I can use the AutoNumber value that gets generated for the record's id to find out what it will be for its parent id (which should just be its id + 1 since it is next in the for loop).

Public Function Paste(nodeID As Long)
  Dim currScenarioID As Long
  Dim i As Long
  Dim saveParentIDs As Collection
  Set saveParentIDs = New Collection
  currScenarioID = Forms("Main")!Scenarios!ScenarioID

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field

  'We want to insert records into the ESDNodes table
  Set rstInsert = CurrentDb.OpenRecordset("ESDNodes")

  'We want to insert a record for each element in the clipboard
  For i = 0 To UBound(clipboard)
     'rstSource represents the record that we want to copy. Should only be 1 as ESDNodeID is unique.
     Set rstSource = CurrentDb.OpenRecordset("SELECT * FROM ESDNodes WHERE ESDNodeID = " & clipboard(i)(0))
     rstSource.MoveFirst
        With rstInsert
           'create a new record
           .AddNew
              'Want to copy all the fields
              For Each fld In rstSource.Fields
                 With fld
                    If .Name = "ESDNodeID" Then
                       'Skip Autonumber field
                    'If the field is the ParentID
                    ElseIf .Name = "ParentID" Then
                       'If the clipboard has a NULL value that means the node selected is the Parent
                       If IsNull(clipboard(i)(1)) Then
                          rstInsert.Fields(.Name).value = nodeID
                       'If the parent ID has already been created for another node, we want to grab that ID

                       ElseIf Contains(saveParentIDs, CStr(clipboard(i)(1))) Then
                          rstInsert.Fields(.Name).value = saveParentIDs(CStr(clipboard(i)(1)))
                       'If neither of these conditions pass, the parentID is handled after the for loop
                       End If
                    'We want the active scenario id
                    ElseIf .Name = "ScenarioID" Then
                       rstInsert.Fields(.Name).value = currScenarioID
                    'Copy all other fields direcly from record
                    Else
                       rstInsert.Fields(.Name).value = .value
                    End If
                 End With
              Next
              'If the parent ID was not set above, that means we have not yet created the record corresponding to its parentID
              'But because of how our clipboard is sorted, it will be the next one in the loop. Meaning that we can create this new record
              'with an empty parentID, and then predict the id of its parent by simply adding 1 to its id

              '*****************
              .Update
              .MoveLast
              If Not IsNull(clipboard(i)(1)) Then
                 If Not Contains(saveParentIDs, CStr(clipboard(i)(1))) Then
                    !parentID = !ESDNodeID + 1 'ERROR HERE
                    saveParentIDs.Add !parentID, CStr(clipboard(i)(1))
                    .Update
                 End If
              End If
              .Close
        End With
  Next i
  loadESDTreeView
End Function

Upvotes: 0

Views: 244

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

You should use method .Edit before changing existing recordset fields.

Also don't close rstInsert inside For, it will fail on next row.

Few more things.

May be I didn't catch the whole idea, but if you moving to last row before editing it in order to just read ESDNodeID, generated after .AddNew, you don't need to use .Update with .MoveLast, you can read the new Id right after .AddNew, it is available.

Predicting of autonumber field value is bad idea, especially in multiuser environment.

Database integrity is very important, so ParentID should have foreign key constrain on ESDNodeID, in this case database won't allow you to insert not existing yet ESDNodeID. Try to review the logic of this procedure.

Upvotes: 1

Related Questions