Default Value in Table

[Jonathan Smith]

I am using Access 2002, with Access 2000 File Format.
I am having difficulty setting a Default Value in a particular field.
Following is the Database Schema, as it relates to this problem:

tbl2001
SvcStartDate Date/Time Date Service Started
SvcEndDate Date/Time Date Service Ended

I need to set the Default Value of SvcEndDate to equal SvcStartDate.

Aside from the lectures on Data Normalization, the doctrines of which I
am fully aware -- and the ramifications of Non-Normalized Data, of which
I am also fully aware -- I need help here.

I appreciate any/all input in this endeavor.

Jonathan Smith

 

[Duane Hookom]

You can't set the default value like this in a table. You can provide
something workable in a form. Add code to the after update event of the
SvcStartDate text box to set the value of the SvcEndDate text box.

 

[Jeff Boyce]

As Duane points out, forms give you the control to do this. Access has no
"triggers" (unlike SQL-Server), so this isn't doable working only in
(Access) tables.

So, I promise not to lecture on normalization ... but why do you need ANY
date pre-loaded in SvcEndDate? I can imagine a "service" that is typically
completed within one day, but then what useful information would #1/1/2003#
and #1/1/2003# provide? Any chance you are recording date & time?

Good luck

Jeff Boyce
<Access MVP>

 

[Tim Ferguson]

I need to set the Default Value of SvcEndDate to equal SvcStartDate.

At the time the DefaultValues get filled in, it's a new empty record so
there is no ScvStartDate value for it to be equal to.

It sounds like you need to catch the Event for SvcStartDate being updated
(i.e. filled in), which is a job for a form.

B Wishes


Tim F

 

[Jonathan Smith]

As Duane points out, forms give you the control to do this. Access
has no "triggers" (unlike SQL-Server), so this isn't doable working
only in (Access) tables.

So, I promise not to lecture on normalization ... but why do you need
ANY date pre-loaded in SvcEndDate? I can imagine a "service" that is
typically completed within one day, but then what useful information
would #1/1/2003# and #1/1/2003# provide? Any chance you are recording
date & time?

Good luck

Jeff Boyce
<Access MVP>

The necessity for the Pre-Loaded Date is to simplify Data Input, at the
Form Level. That Table Structure must meet Government Requirements for
Electronic Filing of Medical Claims.

Thanks for skipping the lecture.

Jon

 

[Jonathan Smith]

You can't set the default value like this in a table. You can provide
something workable in a form. Add code to the after update event of the
SvcStartDate text box to set the value of the SvcEndDate text box.

Duane,

I tried that approach, but apparently I am not as well-versed in Coding
as I has thought. Any suggestions on the code?

I have tried

txtSVCSTARTDATE_AfterUpdate
Me("txtSVCENDDATE") = "txtSVCSTARTDATE"

But it is not working.

Thanks for your help.

Jon

 

[Rick Brandt]

Duane,

I tried that approach, but apparently I am not as well-versed in Coding
as I has thought. Any suggestions on the code?

I have tried

txtSVCSTARTDATE_AfterUpdate
Me("txtSVCENDDATE") = "txtSVCSTARTDATE"

But it is not working.

Me!txtSVCENDDATE = Me!txtSVCSTARTDATE

 

[Jeff Boyce]

Jon

Oh now you have me worried... are you saying that Government Healthcare
regulations prohibit good normalization?! <g>

Jeff Boyce
<Access MVP>

 

[Jonathan Smith]

Jon

Oh now you have me worried... are you saying that Government Healthcare
regulations prohibit good normalization?! <g>

Jeff Boyce
<Access MVP>

Only in Arizona.

 

[Tim Ferguson]

That Table Structure must meet Government Requirements for
Electronic Filing of Medical Claims.

This does not make sense, I'm afraid. It is like saying that the metal used
for the engine mountings is dictated by what colour the outside is going to
be.

The business needs of the system may well include some form of export or
data transmission and specific presentation of same, but that is an ocean
away from the nuts and bolts of logical table design. There is no excuse
(oh, alright, very rarely then) to scupper a good logical design to satisfy
an external body -- it's the job of the query grid to bridge the gap.

Just my tuppence worth...


Tim F