Solved

Comment fields

Posted on 2015-02-12
21
134 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:Simon
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:
Simon 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:Simon
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:Simon
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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:Simon
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

896 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

11 Experts available now in Live!

Get 1:1 Help Now