Solved

Comment fields

Posted on 2015-02-12
21
133 Views
Last Modified: 2016-02-11
So my boss has asked me to have my comment field "Remarks" get copied into one other record.

So when the user inputs data in my "value" field, the user needs to leave a comments as to why.  So they would type in the first comment.  Another record that may need another value change will require the same comment.  She wants it to be able to take the first comment and copy it into the other comment field in the second record.  I have no idea on how to do this.  I was able to copy the comment into all my records into my table..But that is not what I want or need.  I am not good at coding.
Private Sub UpdateAllComments_Click()
   
    Dim strMsg As String, strQry As String, strRemark As String
    
    strMsg = "Update All Comments in the Current view.  IMPORTANT!!! Will update a the filtered records."
    
    If vbYes = MsgBox(strMsg, vbYesNo, "IMPORTANT!!  Confirmation") Then
        strMsg = "Please enter the remark"
        
        strRemark = InputBox(strMsg, "Remark")
        
        If Len(strRemark) > 0 Then
            strQry = "Update Final_Table set UpdatedDate = now(), UpdatedBy = """ & txtWelcome & """, Final_Table.Remarks1 = Allot_Q.Remarks1"
            '&  '--" & strRemark & "'"
            QuickQry strQry, False
            Me.Requery
        End If
    Else
        MsgBox "Action cancelled."
    End If
    
End Sub

Open in new window


I have my records queried to show only a handful of records..but the comments get copied to everything in the table.
0
Comment
Question by:Jass Saini
  • 9
  • 7
  • 5
21 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40605980
To copy the comment to the subset of records on your form, you can either:
1. Copy the filter conditions you used to limit the records displayed on your form into your dynamic SQL query (line 13 of your listing)
2. Use VBA to iterate through the records that are displayed on screen (the form's recordset)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606008
This isn't quite clear for me.  If you have specific comments that get typed into this Remarks field you can just use a combo box  with a set of canned comments.  They would then be able to type their own comment, or select one of the canned comments.  They can append to those comments as well.  

Ron
0
 

Author Comment

by:Jass Saini
ID: 40606059
Simon...How do I do number 1..

Irog...So if the user is typing the comment in ...my boss wants the same comment to be copied to the other record.  Management does not want me using canned comments as it limits the user.  I work for a budgeting division.  The more info the better
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 250 total points
ID: 40606064
This is very basic VBA to copy the memo text from the current record to the next record in a continuous form. Note that this copies from the CONTROL rather than the underlying DATASOURCE for the control.

Private Sub Command73_Click()
Dim memoContent As String
memoContent = Me.txtMyMemo 'NB This is the CONTROL name rather than the datasource field name
If Not Me.Recordset.EOF Then
    Me.Recordset.MoveNext
    Me.txtMyMemo = memoContent 'NB This is the CONTROL name rather than the datasource field name
    Me.Repaint
Else
    MsgBox "Already at last record"
End If
End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40606080
Sorry, our posts crossed. To do Number 1, you would add the where clause that you're using to filter the data for the current form.

To be clear, that may result in 1 or 100 or 9876 records records being updated with the identical comment.

When you say "Other record", it is currently unclear which record this is. Is it the next record from those displayed in a list, or is the form a single page form?
The record that is the "next record" will depend on current sort order, and there won't be a "next record" if you're at the bottom of the table.

A screenshot of your form would help us understand your request a little better.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606085
You are not limited by a combo box.  You can enter anything you want or select from the drop down.  After selecting, you can even modify or add to your selection.

Another option, if you just want to copy the field from a previous record, you can just type CTRL+" (i.e. the Control key and the Quotation mark) simultaneously.  This represents a DITTO shortcut.

Ron
0
 

Author Comment

by:Jass Saini
ID: 40606104
Irog, So I have fourteen quiered records and let's say I just need to copy the comment from the second records "Comment Field" to the tenth record?  My boss doesn't want the user to do anything but type in thier values.  I told her that they could copy and paste and she said no.
0
 

Author Comment

by:Jass Saini
ID: 40606110
Simon...your method works..but I need to be able to copy from the second record to the tenth record not consecutively..
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40606148
Odd request. Anything we coded would effectively mimic a copy and paste.
You could have one two buttons, one that 'copies' the memo field to a temporary storage area, and then another that 'pastes' it to one or more records as you go through them.
0
 

Author Comment

by:Jass Saini
ID: 40606156
I know that is Odd....I told my boss it's better to just have the copy and paste.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606176
I agree with Simon.  This is indeed strange.  Is it always the 2nd to the 10th record or does this vary.  If it varies, then I'm not sure why the user can't just use CTRL-C to copy and CTRL-V to paste.

Ron
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40606177
The only potential benefit I can think of is if you have selector checkboxes down a list of records so that you can 'mass paste' the value from the current record into all the selected records, or if you want to 'paste append' the comment at the end of any existing comment in the memo field, which can be fiddly if you have small text and/or a small memo box.
0
 

Author Comment

by:Jass Saini
ID: 40606182
Hello Irog,

The records will vary each time...and I agree with the fact the user should be able to use copy and paste, but like I said my boss doesn't even want that.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606329
Does your boss have any specific idea in mind of how this should be accomplished then?
0
 

Author Comment

by:Jass Saini
ID: 40606433
Irog...I only started here a few months ago and I don't think she has that much exposure to Access
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 40606438
You know what... I remember now doing something on a continuous form that may be what you're looking for.  I added 2 small icons used to do a copy and paste.  You can click the copy icon of any record and then click the paste icon of 1 or more records that you wanted to copy to.  Here's what it looked like.  Here I copied the Problem comment from the 2nd to the 4th record:
copy pasteIf this would work then all you'd need to do is add two command buttons to your detail section; on the OnClick event of the copy button, you'd copy your comment field to a global variable which you declare at the top of your form module; on the OnClick event of the paste button, you'd just set your comment to that global variable.

If you desire you can use pictures instead like I did and make the command buttons on top of them transparent.
0
 

Author Comment

by:Jass Saini
ID: 40606657
Cool ..that's what I need...How did you get the buttons to look like that?  Can you please share the code?  Coding is not my strong suite.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40606803
The code would be something like this:

Option Compare Database
Option Explicit

Dim strClipComment As String

Private Sub btnImgCopy_Click()
    If Me.Dirty Then Me.Dirty = False
    strClipComment = Nz(Me.txtComment)
    
End Sub

Private Sub btnImgPaste_Click()
    If Trim(clipComment) <> "" Then
        Me.txtComment = strClipComment
    End If
    
End Sub

Open in new window


With the code above, you would need two buttons on your form called btnImgCopy and btnImgPaste.  I used Command buttons with their Transparent property set to True and just had small images underneath them.  Those you can find by doing a search on Google Images.  In my case, I just cropped them from a screenshot I took of MS Word, made their backgrounds transparent and resized them so they wouldn't take too much memory.  However, you could just use plain buttons with the PictureCaptionArrangement property set to "No Picture Captions" and then just click on the ellipses by the Picture property and select the appropriate images (i.e. Clipboard and Copy Document)
0
 

Author Comment

by:Jass Saini
ID: 40606861
So I think the "COPY" works and the paste doesn't....I changed the txtComment to the name of my field



Private Sub btnImgCopy_Click()

Dim strClipComment As String
    If Me.Dirty Then Me.Dirty = False
    strClipComment = Nz(Me.Remarks1)
    
End Sub

Private Sub btnImgPaste_Click()
Dim strClipComment As String
    If Trim(clipComment) <> "" Then
        Me.Remarks1 = strClipComment
    End If
    
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40607046
That's not the only thing you changed.  You moved the declaration  of strClipComment from the top of the module to inside of each procedure.  The reason it was at the top was so that it's scope would be global to all the procedures in the form.  Since you put them inside each procedure, the scope of each one is only available to within each procedure.  You need to put it back to the top.
0
 

Author Closing Comment

by:Jass Saini
ID: 40608205
Thanks Guys
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 49
Excel VBA - copying formats 7 22
TT Copy Formula 3 16
TT Status Chang 3 33
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now