Solved

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

Posted on 2014-07-28
7
1,469 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 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 38

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

696 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