Solved

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

Posted on 2014-07-28
7
1,419 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
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!

 
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 36

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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