Solved

Update the records only when I orginal Remarks field

Posted on 2015-02-20
7
69 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 49

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 49

Accepted Solution

by:
Gustav Brock earned 500 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 49

Expert Comment

by:Gustav Brock
ID: 40626140
You are welcome!

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now