Solved

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

Posted on 2014-07-28
7
1,361 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
7 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 125 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 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 125 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 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

920 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

13 Experts available now in Live!

Get 1:1 Help Now