2014-05-13, 09:50 #1
- Join Date
- May 2014
- Thanked 0 Times in 0 Posts
MS Access 2013 - move data from entry field to storage field, date/time stamp it and sort
In my table, I have a field called "Notes" and a field called "Current DateTime" with Now() set as the default value and I have a field called "Notes History" - I would like for the users to be able to type in the Notes field in the form and when they click a SUBMIT button, their entry will be moved down to the Notes History field, it'll be date/time stamped and the most recent notes history will appear at the top.
The Notes History field is for reading and searching only. And the Notes field is just for entry - once they click SUBMIT, that field should be empty and the entry should be displayed below in the Notes History field.
Is this even possible and if so, where do I start?
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-05-13, 20:50 #2
- Join Date
- Dec 2001
- Melbourne, Australia
- Thanked 23 Times in 23 Posts
For this exercise I normally have an associated NotesHistory table with an id, Date and Notes in it, then there's no limit to the number of notes you may have. With a memo field (which I assume you have defined Notes History as) has a limit of 64,001 characters. Not wanting to scare you but memo fields are usually the problem with databases go corrupt.
If you wish to go with your solution, you will need to do that in the beforeUpdate event of the form by:
[Notes History] = [Current Date Time] & " " & Notes & vbcrlf & [Notes History]
Make control for Notes History on the form so it is not editable.
2014-05-14, 07:35 #3
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 41 Times in 41 Posts
Pat has given you the essential details assuming that you use what is called a bound form - which means that the form (or subform) has the table as it's data source. However if you actually want to do it the way you describe, then you will need an unbound control at the top of the form and some VBA that is triggered by the AfterUpdate event that saves the record in you table, and refreshes the display form, presumably a continuous form sorted with the most recent entries at the top.
If you are a newcomer to Access databases, you may find it much easier to use the built-in capabilities of continuous forms, where you can set the form to Add new records only, and the new record is added at the bottom of the form. My guess is that you will also want this to be a subform that is embedded in a main form that displays other details about the object that you are adding notes to. Please post back if none of this makes sense to you, and let us know how much you have worked with Access.Wendell