We help IT Professionals succeed at work.

When I delete one Remark in one record ..it deletes all

Jass Saini
Jass Saini asked
on
I have a code that writes the same Remark to all the records that have a value in BC1.  Bu when I delete one Remark, it deletes all the remark in the rest of the records that have a value in BC1.  

Private Sub Remarks1_AfterUpdate()
 Dim rst As DAO.Recordset
   Dim strRemarks As String
   
   Set rst = CurrentDb.OpenRecordset("Final_Table")
   strRemarks = Me.Remarks1

   Do While Not rst.EOF
      rst.Edit
        If Nz(rst![BC1Chng1]) <> "" Then
         rst![Remarks1] = strRemarks
      Else
         rst![Remarks1] = Null
      End If
      rst.Update
      rst.MoveNext
   Loop
      

End Sub

Open in new window


Not sure which line is doing this.  I have this code as an After Update event
Comment
Watch Question

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is this line:

         rst![Remarks1] = Null

Comment it out if you don't want this.

/gustav
Distinguished Expert 2017

Commented:
You are not selecting a specific record you want to update.  You are looping through the whole table and deleting all values.

Author

Commented:
Hello Gustav,

I tried to comment that line out and still if I comment out and delete.  It deletes all the Remarks1 "Input" in all the records

Pat--I want it to loop through when I am making a input into the Remarks1 field to copy the comment to all the records.  But if I delete a comment from the Remarks1 field ...I just want to be able to delete just that one.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Then correct like this to just exit the sub:
Private Sub Remarks1_AfterUpdate()
   Dim rst As DAO.Recordset
   Dim strRemarks As String

   strRemarks = Me.Remarks1   
   If strRemarks = "" Then Exit Sub

   Set rst = CurrentDb.OpenRecordset("Final_Table")
   Do While Not rst.EOF
      rst.Edit
        If Nz(rst![BC1Chng1]) <> "" Then
         rst![Remarks1] = strRemarks
      Else
         rst![Remarks1] = Null
      End If
      rst.Update
      rst.MoveNext
   Loop
      

End Sub

Open in new window

/gustav

Author

Commented:
Thank You that worked
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav