?
Solved

ms access vba update memo field on form with date and additional text

Posted on 2014-07-28
7
Medium Priority
?
1,512 Views
Last Modified: 2014-08-01
using ms access 2k10. have an app with a form containing a memo field which is added to by various users from time to time. I would like to keep these running comments, add a date of entry on the form itself. Would to do the following
when form is opened, add line break and date and line break
set the cursor at the end for direct data entry
would like to use same form field without another form for text entry if possible
0
Comment
Question by:jsgould
[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 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 500 total points
ID: 40225529
I use a button with the caption "New Comment" just below and to the right of the memo text box. The code behind the button is

On Error Resume Next
Me.SetFocus
DoCmd.GoToControl "comments"
SendKeys Now & " --> "
SendKeys "^{ENTER}{LEFT}"
Err.Clear
On Error GoTo 0

Open in new window


... where "comments" is the name of the memo text control.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40225533
... also, in the "comments" control OnEnter event procedure, I have

Comments.SelLength = 0

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40225871
FWIW, ....This is a bad way to store this type of data.
If these are separate "date" entries, ...then they should be stored in separate "Records"

Having all of this info in one record/field will make in near impossible to extract certain date info.
Example:
Show me all the notes for March 20, 2014?
Show me all the notes from last week
Show me all the noted between May 6the and May 14th.

They way you are asking to do this violates basic relational database theory.

I would normalize this data out to a new "Comments" field.
With the text and the current date as the fields.
Then set the default value for the date field to: Now()

Then relate this table to the main table.

JeffCoachman
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 500 total points
ID: 40226779
I recommend instead making a linked table with a NoteDate and NoteText field, and displaying it in a datasheet subform.  Here is an example:
 Notes subformYou will need to run an update query to transfer the notes from the Notes field in the main table into the Notes table, and afterwards you can delete that field from the main table.  The link is though a foreign key ID field in tblNotes.
For more information on fixing these kinds of normalization errors, see my Access Archon #229 (Fixing Normalization Errors) -- here is the link for downloading it:
http://www.helenfeddema.com/Files/accarch229.zip
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 40227787
jsgould,

Helen states the same thing I did, ...so you can follow here example and sample file.

Again, ...you can certainly do his with line breaks, ...but trust me...
The time will come when you will need to "separate out" this data (Filter, Sort, Report, query)
It will be much harder to do these things if all the dates are in one field/record separated by line breaks.

Also note that using a separate/related table, you can set the current date as a default value instead of relying on the data entry person to type in the correct date, ..(in the correct format)

Finally note that even if you use a memo field, ...memo fields can cause problems when data needs to be related to another table.

JeffCoachman
0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 40227932
I agree with the other experts.  In the long run, it is far better to normalize and use a subform.  Even if you don't have any specific reporting requirement at this time, you don't want to be caught having to separate all this data.  It is always better to build flexibility into your design whenever you can.  In this case, it is easier to be flexible since the properly normalized solution won't require any code and the non-normalized version will require it.

However, if you elect to ignore that advice, then separate the new empty from the old by having an unbound text box on the form where the new note is entered.  That way you can lock the existing note to keep them from changing it or accidentally typing over it.  Then in the BeforeUpdate event of the form, you can concatenate the unbound field with the bound field and add the date and separator yourself.  As an alternative, you can use the AfterUpdate event of the unbound control but this will cause a problem if you are not careful because if they go back and change it a second time, you've already prepended the note to the bound field so you have to reset it to the .oldvalue and concatenate the update with the old value.
0
 

Author Closing Comment

by:jsgould
ID: 40234859
divided points evenly, all responses were good. Tks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

741 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