Prev: Flashing Label
Next: No Delete
From: Cathy on 24 Feb 2010 12:19
Is there a way that I can lock users out of records created before a certain
date?

I have a field on the records to indicate the date created. I just can't
think of a way to prevent users from making changes to them based on it.
They of course would need access to still create new records and change
records after the given date.

Thank you so much in advance for any help you can provide.
From: Daryl S on 24 Feb 2010 13:29
Cathy -

You cannot set row-level security in Access. You can in other more powerful
databases that you can use as a back-end. That said, if your users are not
sophisticated, you can create your user interface to lock records that have
create dates before this certain date. You need to think if this 'certain
date' is fixed, or will it change with time? Is it relative to the current
date? If it is a fixed date (and you don't think it will change more often
then every couple years), then I would make it a global parameter, and create
a public function to return it wherever it is needed. If it is relative to
the current date, then you can set up that date difference to be a global
parameter, and create a public function to return the create date before
which no users can modify the data. If it is something that will change
often, but cannot be determined by today's date, then I would suggest storing
this in a table with the effective date of the 'certain date' as the key
value. You can then maintain this data as needed. I would write a global
function that does a DLOOKUP into this table for the 'certain date' with the
greatest effective date less than or equal to today's date.

Then, on your forms that display the records, you can set code like this in
a form function, and call it as needed from the form's Open event and Current
event:

If Me.CreateDate <= CertainDate Then
Me.txtID.Enabled = FALSE
Me.txtField1.Enabled = FALSE
... for all the controls on the form that contain data on the record.
Else
Me.txtID.Enabled = TRUE
Me.txtField1.Enabled = TRUE
... for all the controls on the form that contain data on the record.
End If

You need to be careful about any queries you run - if any of them are action
queries, then you should exlude records before the CertainDate in the same
way.

If you have sophisticated users that can write queries or open the tables,
there is no way to stop them from making changes. You should back up those
records and store them in another database for safekeeping.

--
Daryl S


"Cathy" wrote:

> Is there a way that I can lock users out of records created before a certain
> date?
>
> I have a field on the records to indicate the date created. I just can't
> think of a way to prevent users from making changes to them based on it.
> They of course would need access to still create new records and change
> records after the given date.
>
> Thank you so much in advance for any help you can provide.
 | 
Pages: 1
Prev: Flashing Label
Next: No Delete