[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update the records only when I orginal Remarks field

Posted on 2015-02-20
7
Medium Priority
?
84 Views
Last Modified: 2015-02-23
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
0
Comment
Question by:Jass Saini
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40622419
You can change that line to

strRemarks = Nz(Me.Remarks1)

Open in new window

0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40622429
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40622773
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Jass Saini
ID: 40626078
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40626095
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
 

Author Closing Comment

by:Jass Saini
ID: 40626132
Thank You
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40626140
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question