?
Solved

Update the records only when I orginal Remarks field

Posted on 2015-02-20
7
Medium Priority
?
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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 51

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 51

Expert Comment

by:Gustav Brock
ID: 40626140
You are welcome!

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 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