?
Solved

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

Posted on 2015-02-18
15
Medium Priority
?
89 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
[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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 85
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 85
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

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
 

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 1000 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 85
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

719 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