Update the records only when I orginal Remarks field

So I have been able to update the remarks field based off if there is an input in BC1Chng1....The code that was provided works great as along as I am in the orginal Remarks1 field.  I want the user to do their inputs into BC1Chng1 field as those will change.  I just want it to look at see if there is a value in BC1Chng1 for the remarks1 to copy down into those records.  The Remarks in Remark1 field will always remain the same.

Here is the code:  It copies down just fine ....But I need to be in the Orginal field where I type the Remarks into the Remark1 field.

Dim rst As DAO.Recordset
   Dim strRemarks As String
   
   Set rst = CurrentDb.OpenRecordset("Final_Table")
   [u]strRemarks = Me.Remarks1[/u]

   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


The line where I have it underlined is the issue...It give me a Run-time error '94  Invalid use of Null
Jass SainiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
Then you may need:
Dim rst As DAO.Recordset
   Dim varRemarks As Variant
   
   Set rst = CurrentDb.OpenRecordset("Final_Table")
   [u]varRemarks = Me!Remarks1.Value[/u]

  If Not IsNull(varRemarks) Then 
      Do While Not rst.EOF
          rst.Edit
              rst![Remarks1].Value = varRemarks
          rst.Update
          rst.MoveNext
      Loop
   End If
   rst.Close

   Set rst = Nothing
      
End Sub

Open in new window

/gustav
0
 
Robert ShermanOwnerCommented:
You can change that line to

strRemarks = Nz(Me.Remarks1)

Open in new window

0
 
Robert ShermanOwnerCommented:
Also, if you have a both a field and an unbound textbox with the same name, this is going to cause problems.   What exactly are you trying to refer to with Me.Remarks1 ?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Gustav BrockCIOCommented:
What you miss is that the field can be Null. A string can't hold that.
So change the data type of the variable to Variant:
Dim rst As DAO.Recordset
   Dim varRemarks As Variant
   
   Set rst = CurrentDb.OpenRecordset("Final_Table")
   [u]varRemarks = Me!Remarks1.Value[/u]

   Do While Not rst.EOF
      rst.Edit
         If Not IsNull(rst![BC1Chng1].Value) Then
            rst![Remarks1].Value = varRemarks
         Else
            rst![Remarks1].Value = Null
         End If
      rst.Update
      rst.MoveNext
   Loop
   rst.Close

   Set rst = Nothing
      
End Sub

Open in new window

Do not use Nz as Rey suggested, as it will either fail if your field doesn't accept zero-length strings or insert a zero length string which you most like don't want.

/gustav
0
 
Jass SainiAuthor Commented:
Hello,

So again,  if I am not in the orginal Remarks1 field ...it will not copy to the rest of the records with a BC1 input

I have to be on a record that has something in the Remarks1 field for it to copy to the rest of the records.  Other wise it deletes that Remarks1 field.
0
 
Jass SainiAuthor Commented:
Thank You
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.