Setting Default value

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

I have a table where Employee insurance enrollment data is held, (tbl125).
Once a year, this data will be transferred to an archive table and new data
will be entered in the original table. Tbl125 contains a date field where
the enrollment date is stored. This date can vary from year to year, but not
from employee to employee. Currently the transfer to the archive table is
handled via a command button. What I'd like to have happen is after the user
transfers the data, they would be given the option to set the new default
enrollment date. This date would then be used for all employee enrollments
into the current year's data. How can I do that programatically?

Thanks,
 
Since the date cannot vary, why store it in that table at all?
Why not set up a separate table which stores only the one date per year.
Or, am I misunderstanding something?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Try using Allen Browne's tblSys idea for storing the user defined default
date. Then, on your entry form's Before_Update event, you can refer to the
date that the user specified via Elookup (or Dlookup)

tblSys:
http://allenbrowne.com/ser-18.html

Elookup:
http://allenbrowne.com/ser-42.html

If this will work and you would like me to post it I have a function that I
use that edits the Value in tblSys that you could run when the user inputs
the desired default date.

I think this should do it...

-jack
 
CurrentProject.Connection.Execute "ALTER TABLE tableName ALTER COLUMN
columnName SET DEFAULT newDefaultValue"


should do. It is a command where you need ADO
(CurrentProject.Connection.Execute) because DAO (CurrentDb.RunSQL ) won't
work. There are probably other ways to do it, though.




Vanderghast, Access MVP
 
Back
Top