Solved

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

Posted on 2014-07-28
7
1,343 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

747 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

14 Experts available now in Live!

Get 1:1 Help Now