Reputation: 1
I'm using a form to enter a component ID (CID).
After entering the Number in the Field I use the AfterUpdate() function with CID to look up a corresponding Prod ID in another Table.
The Matching Prod ID from the other Table will then be entered automatically into the field. The field is a multivalued Field.
Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset
Dim rstChld As DAO.Recordset2
Dim tmpVar
If Me.Dirty Then
Me.Dirty = False
End If
If StrComp(Me.Part, "Device") = 0 Then
tmpVar = DLookup("[Device Prod]", "subLookTblCIDDevice", "[CID Device] = '" & Me.CIDDevice & "'")
Set rst = Me.Recordset
If Me.Recordset.RecordCount = 0 Then
rst.MoveFirst
End If
If Not (rst.BOF And rst.EOF) Then
If rst.Updatable Then
rst.Edit
Set rstChld = rst!Prod.Value
rstChld.AddNew
rstChld.Fields(0) = tmpVar
rstChld.Update
rst.Update
Me.Bookmark = rst.LastModified
Set rst = Nothing
Set rstChld = Nothing
End If
End If
End If
End Sub
If the record exists and I'm changing the CID the corresponding field gets its correct corresponding ID.
If it is a new Record and the first of the recordset I get the error message
No Current Record - 3021
It can be mitigated by adding
If Me.Recordset.RecordCount = 0 Then
rst.MoveFirst
End If
But if it is a new Record and not the first record it changes the previous record.
I tried .AddNew
instead of .Edit
. This will create a new record after the one which has been updated.
I don't understand why it is jumping before or after the record.
Upvotes: 0
Views: 96