Using expression builder in default value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Copwriter - 18 Sep 2004 07:51 GMT

I want the default value for a date field (short date format) to have the
same value as another date field. I have tried entering this into
Expression Builder as variations on =[dteStartDate], but it always returns
an error. How do I get the default value in one date field to reflect the
value in another?

Reply to this Message

Jeff Boyce - 19 Sep 2004 14:11 GMT

As I recall, Access won't let you use another field's value as part of an
expression for a Default Value. It makes sense, when you consider that a
Default Value is added to a new record when the record is created, and when
it is created, the "other" field won't have any value?!

But your post begs another question -- if you already have another field
that holds a value, why do you need to store it a second time, as a
"default" value?

Signature

More info, please ...

Jeff Boyce
<Access MVP

Reply to this Message

Copwriter - 20 Sep 2004 01:15 GMT

> But your post begs another question -- if you already have another field
> that holds a value, why do you need to store it a second time, as a
> "default" value?

Thanks for the reply. The database is to hold training records, what
persons attended various classes and when. The two date fields in question
are the start and end dates of each training session. It's anticipated that
most sessions will be only one day in length, so I want the default value
for the dteEndDate to be the same as the entered value for dteStartDate.
However, in case the class being entered does span multiple days, I want the
user to be able to enter a different (later) date for the dteEndDate field.

Reply to this Message

Jeff Boyce - 20 Sep 2004 02:44 GMT

In that case, I'd suggest using a form, rather than the table directly. In
the form, you can add code behind the control in which the start date is
entered, "forcing" the same date into the end date control.

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Copwriter - 20 Sep 2004 07:04 GMT

> In that case, I'd suggest using a form, rather than the table directly. In
> the form, you can add code behind the control in which the start date is
> entered, "forcing" the same date into the end date control.

I'm trying that, but I can't seem to get it right in the form. I'm trying

=[dteStartDate]

in the default value blank for the properties for dteEndDate on the form
that fills in tblClasses, but nothing happens when I put a date into
dteStartDate and then tab into dteEndDate. What am I doing wrong?

Reply to this Message

Jeff Boyce - 20 Sep 2004 11:16 GMT

See my original response AND my second reply. When you start a new record,
via table or form, the first (date) field will have no value.

You are still trying to put a "default" property value in.

My second reply mentioned putting code behind the "start date" control. Use
the AfterUpdate event and, if what is entered into the [Start Date] control
is actually a date (IsDate()), THAT'S when you set the value of the second
date control.

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Copwriter - 20 Sep 2004 23:50 GMT

> You are still trying to put a "default" property value in.
>
> My second reply mentioned putting code behind the "start date" control. Use
> the AfterUpdate event and, if what is entered into the [Start Date] control
> is actually a date (IsDate()), THAT'S when you set the value of the second
> date control.

Am I SOL if I don't know how to code in VBA? I was hoping to use Expression
Builder to set the event because of my deficiency there.

Reply to this Message

Jeff Boyce - 21 Sep 2004 01:54 GMT

Actually, I'd prefer to "spin" it as an opportunity ... and there is VERY
little coding that needs to happen.

If you open the properties window of the [Start Date] control on the form
(in design mode), you can double click on After Update to get an event
procedure. Use the "..." button on the far right of that row to open the
VBA editor.

Within the event procedure, add something like:

Me!txtYourEndDateControlName = Me!txtYourStartDateControlName

This (with <your control names inserted properly>) will copy whatever is in
your StartDate control into the EndDate control.

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Copwriter - 21 Sep 2004 21:42 GMT

That did it! Thanks very much for your help.

> Actually, I'd prefer to "spin" it as an opportunity ... and there is VERY
> little coding that needs to happen.
[quoted text clipped - 10 lines]
> This (with <your control names inserted properly>) will copy whatever is in
> your StartDate control into the EndDate control.

Reply to this Message