• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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

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
Jass Saini
Asked:
Jass Saini
  • 7
  • 4
  • 3
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jass SainiAuthor Commented:
Will that look at the records on the form or table???
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
PatHartmanCommented:
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
 
Jass SainiAuthor Commented:
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
 
Helen FeddemaCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
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
 
Helen FeddemaCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Helen FeddemaCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
Awesome .....Thank you
0
 
Jass SainiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now