Results 1 to 3 of 3
  1. #1
    New Lounger
    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?


  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 26 Times in 26 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.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 57 Times in 57 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.

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