The DefaultValue property cannot be used in this way. It comes into play
immediately when a new row (record) begins to be inserted into a table,
before any other data is entered. At this stage there is no value in the
BLDG REC CONSTR DATE column (field) so there is nothing to govern what goes
into the I-H DIRECTORY column. By the time a user enters data into BLDG REC
CONSTR DATE the DefaultValue of the other column is already in play, so its
too late.
What 's needed is that a value is entered into the I-H DIRECTORY column
after a value is entered into the BLDG REC CONSTR DATE column. This cannot
be done at table level. only in form used for data enter. As data should
always be entered via a form, never in raw datasheet view of a table, this is
not a problem.
In a form data is entered via bound controls, e.g. a text box. Controls
have events associated with them which are triggered at certain stages. When
data is entered into a control the control is 'updated' so the relevant event
is the AfterUpdate event. You can govern what happens when an event is
triggered by means of code in the relevant 'event procedure'. So in this
case you'd enter code in the AfterUpdate event procedure of the BLDG REC
CONSTR DATE column. I'm assuming that this column is a date/time data type
containing full date/time values, not just a number data type containing the
year, so you need to assign the value "N/A" to I-H DIRECTORY if the date is
later than or equal to 1 January 1960.
So the code for the AfterUpdate event procedure would be:
If Me.[BLDG REC CONSTR DATE] >= #1960-01-01# Then
Me.[I-H DIRECTORY] = "N/A"
Else
Me.[I-H DIRECTORY] = Null
End If
What this does is set the value to "N/A" if the date entered is later than
or equal to 1 January 1960. Otherwise it sets it to Null, i.e. empties the
control so that you can insert your own value. Note how the date is enclosed
in the date delimiter # characters and is in the ISO date format of
YYYY-MM-DD. This makes it internationally unambiguous.
If by any chance the BLDG REC CONSTR DATE is a number field storing just the
year then amend the code to:
If Me.[BLDG REC CONSTR DATE] >= 1960
If you are unfamiliar with entering code in event procedures this is how its
done:
Select the BLDG REC CONSTR DATE control in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the lines of code between these two
existing lines.
Ken Sheridan
Stafford, England
EHammack said:
I am learning Access on my own via trial and error and the HELP topics. I
would like to insert a default value of "N/A" in [I-H DIRECTORY] if the value
in [BLDG REC CONSTR DATE] is greater than 1959. If [BLDG REC CONSTR DATE] is
less than or equal to 1959, I want to be able to insert text of my choice.
Can I do this with an expression in the default value setting within table
design?
Thanks in advance.
