Input Box for comments in Microsoft Access

[nicolenwn]

Hi,

I have a database in Access that currently holds the status of a salesperson and the comments of the status. The comment is an open-ended field typed in by a user.

For example, if Jonny has the status of "On Leave" then the comment might say "26-11-09: Johnny is currently on medical leave and will return by 01-12-09". The comment is a compiled overtime and stored in a Text box.

However, my problem is that, when i transfer this column called "Comments" into excel, it populate all the comments into one single field. Thus the field in excel becomes very large and hideous.

So the solution i had was to create an input box.

Sub UserInput()
 
Dim iReply As Integer
 
    iReply = MsgBox(Prompt:="Please insert the latest comment", _
 
            Buttons:=vbYesNoCancel, Title:="Insert Comment")          
 
    If iReply = vbYes Then
 
        'Insert comment into TextBox as "Latest Comment" Then push previous    comment into another field
 
    ElseIf iReply = vbNo Then
 
       'Do Other Stuff
 
    Else 'They cancelled (VbCancel)
 
        Exit Sub
 
    End If
 
End Sub

The purpose of my soultion is to always save the last comment that the user types in into another textbox. So that if i export the "Comment" into excel, it will only show me the lastest comment. And if the user puts in a new comment, the previous comment will be saved in another textbox, or alternatively an array?

For now, i can only create the Input Box but i cannot do the function that allows me to save previous comments and only extract the latest comment into excel. Hopefully i can get some advice(:

Many thanks in advance for all the help!!
Nicole

[ADezii]

Hello Nicole, I not exactly clear as to what you are trying to accomplish, so I wrote a generic code Template that will hopefully point you in the right direction. First, a couple of simple assumptions:

1. You have a Text Box on your Form named txtLastComment which will always contain the Last Comment entered by the User.
2. You have 9 Text Boxes on your Form named Text1, Text2, ...Text9, which will contain all previously entered Comments up to a MAXIMUM of 10. Prior Comments will be 'Pushed' into this Text Boxes when additional ones are added.
3. The User will be prompted for a Comment, which if it is a legitimate value, be placed into txtLastComment. If a prior Comment exists, it will be 'Pushed' into the next available Text Box.
4. The code is not complete since you will need a mechanism to Clear the Text Boxes, and also to Reset the intCommentNumber to 0.
5. Any questions, please feel free to ask.
   

   Dim strComment As String
   Static intCommentNumber As Integer
    
   'Prompt the User to enter a Comment
   strComment = InputBox$("Please insert the latest Comment", "Insert Comment")
    
   'If the User enters nothing or Cancels, get outta there!
   If strComment = "" Then Exit Sub
    
   If intCommentNumber = 10 Then   'Has the MAX been reached
     MsgBox "Yoou have reached the MAXIMUM Number of Comments!"
       Exit Sub
   End If
    
   intCommentNumber = intCommentNumber + 1     'Increment Comment Counter
    
   If intCommentNumber = 1 Then        '1st Comment, nothing to Push
     Me.Controls("txtLastComment") = strComment
   Else        'Push the Comment in txtLastComment to the next available Text Box
     Me.Controls("Text" & CStr(intCommentNumber - 1)) = Me.Controls("txtLastComment")
    
     'Place most recent Comment in txtLastComment
     Me.Controls("txtLastComment") = strComment
   End If

6. This can also be accomplished using a Static Array with Redim Preserve to store the previous Comments, but would be more difficult.
7. Just for curiosity, what are you doing with the Prior Comments

[nicolenwn]

Thanks ADezill for your help!

Regarding your last quesiton (Question 7), i would want to keep the prior comments in a Memo Field preferably as I need it for record keeping, however the purpose of this is to separate the latest comment input by the user so as to allow the latest comment only to be exported into excel.

I have tried to export the whole textbox as a whole, however because excel has the 255 character limit, not all of my comments can be seen. Thus to avoid this i have to split the comments up.

So when the user first input a comment it is stored in the "Last Comment" box. And when exporting this into excel i have no problem. But later, after a few weeks of comments being added the problem arises. when i export i cannot view all of the comments i've written. Hence the need for this solution.

The last comment to always be saved in the "Last Comment" box and if a new comment comes in the previous comment in the last comment box goest into a memo field text box for record keeping sake. this memo field box will not be exported into excel but just kept. If the user wishes to see this last comment, they can do so by using the database directly.

I hope i don't confuse you as your help is greatly appreicated! many thanks in advance!! and if you have any questions please ask(:

Cheers mate,
Nicole

[ADezii]

1. If you insist on storing all Comments in a MEMO Field, then you can separate each Comment by a Carriage Return/Line Feed (either {ENTER} or {CTRL}{ENTER} depending on the ENTER Key behavior) which will result in each Comment being in its own Line progressing vertically
2. The Last Comment entered will always be the Last Line in the Memo Field.
3. You can now place each Comment in the Memo Field into an Array using Split(), then extract the Last Comment easily since it is located at the Upper Bound of the Array.
4. Hopefully this makes sense to you. I'll post the code assuming the MEMO Field is named [Comments], and if you have any questions, feel free to ask.
5. I'm sure other suggestions will come along, but since you wish to use a MEMO Field to store all Comments, they will probably be limited.
   

   Dim varComments As Variant
   If IsNull(Me![Comments]) Then Exit Sub
    
   'Last Comment entered will be the Highest Index in the Array
   varComments = Split(Me![Comments], vbCrLf)
    
   MsgBox varComments(UBound(varComments))        'Last Comment
    

6. To 'Push' a New Comment to the bottom of the MEMO Field (Stack):
   

   Me![Comments] = Me![Comments] & vbCrLf & "New Comment"

[Diabetes Diet]

This is the kind of thing I try to teach people. Can I count on a sequel?