Need help with adding new fields

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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-access/archive/2009/08/13/access-2010-data-macros-similar-to-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.

Reply | Quote