Solved

Looking at One field and basing the Comments and Looping through the rest of the records

Posted on 2015-02-18
15
83 Views
Last Modified: 2015-02-20
Here is what I am trying to complish:

I want the code to look at BC1Chng1 if there is a value for it to copy the Comment in the Comment field to the other records where there is values in BC1.  So it is looking at BC1  checking to see if a value is there and if yes repeating the Comments in the comment field.

here is my code for far

Private Sub UpdateAllComments_Click()

Dim memoContent As String


If Nz(Me.BC1Chng1, "") <> 0 Then
Do While Not Me.Recordset.EOF
    memoContent = Me.Remarks1
    Me.Remarks1 = memoContent
    Me.Repaint
    Me.Recordset.MoveNext
    Loop
    'Me.Remarks1 = memoContent
    'Me.Repaint
    
 

    MsgBox "Already at last record"
 End If
End Sub

Open in new window

0
Comment
Question by:Jass Saini
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 84
ID: 40617155
I assume you're doing this on a form?

If so, you can do this:

If Nz(Me.BC1Chng1, "") <> "" Then
  Currentproject.Connection.Execute "UPDATE SomeTable SET Comment='" & Me.Comment & "' WHERE BC1 IS NOT NULL"
End If
0
 

Author Comment

by:Jass Saini
ID: 40617279
Will that look at the records on the form or table???
0
 
LVL 84
ID: 40617384
It would update the Comment column for all records in "SomeTable" with the value entered in the BC1Chng1 textbox, but only if those records have a non-Null value in the BC1 field.

If you want something different, you will have to explain exactly what you want, and please be precise.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40617516
Your problem is a symptom of an improperly structured schema.  The real solution is to add the missing table to the hierarchy.  That way you only need to store the comments once.
0
 

Author Comment

by:Jass Saini
ID: 40619108
The way I have it now...it updates the according to the records on the form but to the table.  But only those records.  What I would like it to do is:  The first input for the user is to type the value in BC1 and add the comment to that record.  If there are any other BC1 input, to copy the first comment to this record.  It takes away the time for the user to retype the comment.  This way the comment is consistent.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40619179
I think the comments should be in a separate linked table, possibly displayed in a datasheet subform, like this:

Comments subform
Normally, comments would be linked by the key field of the main form, but in your case possibly they should be linked by the BC1 field.
0
 

Author Comment

by:Jass Saini
ID: 40619209
Here is my code...it copies the comments to all the Remarks1 fields..I just need it figure out how for it to look at the BC1 and if there something in BC1 and copy the Remarks into Remarks1 otherwise have Remarks1 be Null.

Dim memoContent As String
'memoContent = Me.Remarks1

If Nz(Me.BC1Chng1, "") <> 0 Then
Do While Not Me.Recordset.EOF
    memoContent = Me.Remarks1
    Me.Recordset.MoveNext
    Me.Remarks1 = memoContent
    Me.Repaint
   Loop
Else
    MsgBox "Already at last record"
End If
End Sub

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Jass Saini
ID: 40619740
Also I have over Forty fields of Comments also I wanted everything on one table which the data will be upload into another system for viewing and reporting??
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 40619873
Your Nz() code will yield either a text value or an empty string, so it will never yield zero, and thus it will always be true.  Compare it to "" instead.  Also, your code copies the contents of Remarks1 to the next record.  If this is really what you want, you will get the same value in all records, which could be done more easily with a simple update query.

If I understand what you want, it is to look at each record, and copy the comments from the Remarks field to the Remarks1 field if there is a value in the BC1 field; if there is no value in the BC1 field, set Remarks1 to Null.  I would do this in code using a recordset (whatever recordset the form is based on).  Here is some sample code:

Public Sub CopyRemarks()

   Dim rst As DAO.Recordset
   Dim strRemarks As String
   
   Set rst = CurrentDb.OpenRecordset("tblTestRemarks")
   
   Do While Not rst.EOF
      rst.Edit
      strRemarks = Nz(rst![Remarks])
      If Nz(rst![BC1]) <> "" Then
         rst![Remarks1] = strRemarks
      Else
         rst![Remarks1] = Null
      End If
      rst.Update
      rst.MoveNext
   Loop
      
End Sub

Open in new window


All of this is quite apart from whether this is a good or reasonable thing to do -- it may not be.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40620844
Also I have over Forty fields of Comments also I wanted everything on one table which the data will be upload into another system for viewing and reporting??
How does this relate to your original question? Here on EE, you should stick to one and ONLY one issue for a single question.

I believe Helen's code is pretty much what you asked for in the original question, but I believe you want to update the Remarks column based on the value the user entered in BC1Chng1? If so, then modify the code as shown below:

Public Sub CopyRemarks()

   Dim rst As DAO.Recordset
   Dim strRemarks As String
   
   Set rst = CurrentDb.OpenRecordset("tblTestRemarks")
   strRemarks = Me.BC1Chng1

   Do While Not rst.EOF
      rst.Edit
      If Nz(rst![BC1]) <> "" Then
         rst![Remarks1] = strRemarks
      Else
         rst![Remarks1] = Null
      End If
      rst.Update
      rst.MoveNext
   Loop
      
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40621323
My code worked directly with the recordset, not picking up a value from the form.  The strRemarks variable was set from each record as the code iterated through the recordset.  The problem with picking up a value from the form is that it is a value from one record only, which would then be written to all records with non-Null BC1.  I don't think this is what is wanted here, but I could be wrong.
0
 

Author Comment

by:Jass Saini
ID: 40621551
Hello Helen and Scott,

Scott I am sticking to my orginal question---Helen asked about making a new table and I didn't want to.

So the coding worked....had to change one or two things

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

   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

0
 

Author Closing Comment

by:Jass Saini
ID: 40621553
Awesome .....Thank you
0
 

Author Comment

by:Jass Saini
ID: 40621602
Hello,

One more thing....The code works....but then I have to go the orginal Remarks1 field (record) in order for it to work...

So the user goes through the records and enters in BC1 into 14 records ..I want it to be able to update the remarks after BC1 inputted.
0
 
LVL 84
ID: 40622338
You'll have to clarify that a bit. Your original question asked how to update all the records in the form's recordset with a value the user typed into your BC1Chng1 textbox. The code provided does that.

If your requirement has changed, you should probably ask that in a new question.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 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

13 Experts available now in Live!

Get 1:1 Help Now