Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Automatic Last Updated Field

    Hi Everyone,

    I have a field where when I click a command "Send Email" the date automatically appears in the "Date Email Sent" field. Well, if I have to send another email, the date is replaced with the current date and the old date that was in the field is erased. I would like the old date to stay, and the new date to just be added. What is the best way to make this happen? Do I need to write code for this to happen? Thanks for any advice.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by benz1984 View Post
    I would like the old date to stay, and the new date to just be added.
    Added where and to what: another row, another column, another table?
    Have a nice day!

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    I have the date appearing in a text box so it would need to be added to a text box unless in order to get multiple dates to appear everytime the send email button is pushed I have to change the text box to another control?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I meant: where and how to you intend to store the different dates? What "appears" in a control on a form comes from a table, either directly or through a query. If not, the data is "volatile" and it disappears forever when the from is closed.
    Have a nice day!

  5. #5
    Join Date
    Oct 2007
    Posts
    214
    Hello,

    It comes from a table as a field called "Last Updated". When I click the "send email" button it puts the date in the "Last Updated" field. However if I click the send email again on a different date, the date erases and puts the more recent date in its place. I would just like it to keep the last date and just put the new date along with it instead of erasing.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    just put the new date along with it instead
    What do you mean with that? You can't store two pieces of data in a single field, especially if this field is defined as a Date/Time data type. You need a secondary table on the Many side of a One to Many relationship to do that properly.
    Have a nice day!

  7. #7
    Join Date
    Oct 2007
    Posts
    214
    Ok that's what I thought. The date filed can only store one date and not add another without deleting the first date put in. What would be the best way to acheive the above?

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You'll need another Table and Form, for storing the Emails/Dates Sent.What you're describing here is the classic one-to-many scenario, with the 'one' side being the Addressee and the 'many' side being the Emails/Dates Sent. This is usually handled with a Main Form/Subform set up.

    In general terms the Main Form would be a Single View Form and based on a AddresseeTable. The Subform would probably be a Continuous View Form (to better display the 'Emails/Dates Sent') based on a Emails/Dates Sent Table.

    The Fields would typically be something like this:

    AddresseeTable
    AddresseeID 'Primary Key
    AddresseeName
    AddresseeEmailAddress
    ...and so forth

    Emails/DatesTable
    AddresseeID 'Foreign Key
    Emails/DatesID 'Primary Key
    DateSent
    ...and so forth

    Once you've designed your Main Form and Emails/Dates Sent Form, based on AddresseeTable and Emails/Dates Sent Table, respectively, open the Main Form in Form Design View and add a Subform Control to it. The Subform Wizard will walk you through the process. When asked, base your Subform on the Emails/DatesSent Form. Seeing that both Tables the Forms are based on have a Field named AddresseeID, the Wizard should ask if you want to link the Main Form /Subform using these Fields. Answer OK and Bob's your uncle!

    As you move through the Addressee Records in the Main Form, Access will automatically update the Emails/Dates Sent Records in the Subform and only show those Emails/Dates related to that particular Addressee.

    Good luck with your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Create a secondary table with a foreign key column to the main table and a Date/Time ('Last Updated') column. Next define a relationship: One (main table) to Many (secondary table). You then store the date in the secondary table, adding one row to it every time you click the "send email" button.
    Have a nice day!

Posting Permissions

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