Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    1
    Thanks
    0
    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?

    Thanks!

  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,533
    Thanks
    0
    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.

  4. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,492
    Thanks
    3
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •