Solved

Update the records only when I orginal Remarks field

Posted on 2015-02-20
7
76 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 50

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
Industry Leaders: 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!

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40626140
You are welcome!

/gustav
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 47
Null or "" 28 53
Hidden queries with "~sq_f" and "~sq_r" 3 20
Open folder via explorer, but stop user from changing folder 6 19
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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