Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Need help with adding new fields

    Hi all, I have a database that includes the table structure shown in my attachment. My questions are as follows.

    1. If I add a field called Date_Further_Info_Reqd, then is it possible to auto generate a date in this field if the Review_Status field value = Require Further Information? (Note if the Review Status field is changed later, then the Date_Further_Info_Reqd date must remain unchanged.

    2. If I add a field called Date_Completed, is it possible to auto generate a date in this field if the Review_Status field value = Complete?

    3. If I add a Y/N field called Watchlist, is it possible to auto generate a date in a field called Watch_Date when the Watchlist field = Y?

    Any assistance would be greatly appreciated.
    Attached Images Attached Images
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In Access it isn't possible to do that sort of setting the value of a field based on the content of some other field when you are working at the table level. However, if you are working with a form that has that table as it's data source, it is pretty easy to do. The concept is to use the after update event of the control displaying the text to determine if the field contains the specific value, and then set the value of the date field on the form. In some cases you may want to use the after update event of the form rather than the control. You will need to have some familiarity with Visual Basic - in 2007 and 2010 the macro capability may be able to do what you want, but VBA is the preferred method.
    Wendell

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    In Access it isn't possible to do that sort of setting the value of a field based on the content of some other field when you are working at the table level.
    This is a true statement for Access 2007 and all prior versions, however, Access 2010 provides support for triggers at the table level, if you are using the newer .accdb file format. I haven't worked much with this feature, but I believe they are referred to as Data Macros. More information on Data Macros is available here:
    http://blogs.office.com/b/microsoft-...-triggers.aspx

    For all other prior versions of Access, use VBA code in a form, as Wendell suggests, but, keep in mind that such updates will *only* happen if data changes are made using that form.
    Last edited by tgw7078; 2012-06-25 at 10:33. Reason: Added URL to blog site for more information on data macros.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

Posting Permissions

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