Lock field based on date

[ItsBarbara]

Is it possible to lock certain fields automatically after a given date?? For
example, I need to restrict teachers from changing grades after a certain
date. Currently, I do this manually, but I would sure like to be able to set
it to happen automatically based on the current date.

 

[Klatuu]

You don't give enough detail to determine where to do this. If your teachers
are entering data directly into tabkes, then you can't control that. If,
hopefully, they are using a form to enter and modifiy grades, then it is
possible.
The first question is how will the form know what the cutoff date is for
modifying the grades? Also, are there records in the table for multiple
grading periods so that some can be changed and others cannot?
Once you have these issues figured out, the method is to put code in your
form that will compare the dates and lock the controls containing the data
you don't want altered. Where you do that will depend on the answers to the
above questions.

If it is that no records in the table may be altered, the easiest place is
in the form's open event:

If Date() >= CutOffDate Then
Me.Grade1.Locked = True
End If
The only problem is, if you use this form for adding new entries, you will
have to unlock them. That would be in the Current event:

If Me.NewRecord Then
Me.Grade1.Locked = False
Else
Me.Grade1.Locked = True
End If

Now, if some records can be changed, others can't, and new records may be
added then the Current Event should handle it all and look something like
this:

If Me.NewRecord Then
Me.Grade1.Locked = False
Else
If Date() >= CutOffDate Then
Me.Grade1.Locked = True
Else
Me.Grade1.Locked = False
End If
End If

 

[ItsBarbara]

I have teachers enter the grades on a form. On this form are fields for the
30 week grade, the 33 week grade and the 36 week grade. After they enter the
30 week grade, I lock that field only. Once they enter the 33 week field, I
lock those as well. etc. I know what date I want to use for each so I was
just wanting to set the field on the form to be read only after that date.

For example after April 3, the field for the 30 week grade would be locked
but the 33 and the 36 will remain open. Then I would want to have the 33
weekk grade closed after April 24th but the 36 week would remain open. That
would lock after May 15th.

Hopefully this better explains what I am doing.

 

[Klatuu]

Dim dtmCutOff30 As Date
Dim dtmCutOff33 As Date
Dim dtmCutOff36 As Date

dtmCutOff30 = DateSerial(Year(date), 3, 3)
dtmCutOff33 = DateSerial(Year(date), 3, 24)
dtmCutOff36 = DateSerial(Year(date), 5, 15)
Me.Grade30.Locked = Date() > dtmCutOff30
Me.Grade33.Locked = Date() > dtmCutOff33
Me.Grade36.Locked = Date() > dtmCutOff36

 

[ItsBarbara]

Thank you Thank you!!! This works great!!!!

Dim dtmCutOff30 As Date
Dim dtmCutOff33 As Date
Dim dtmCutOff36 As Date

dtmCutOff30 = DateSerial(Year(date), 3, 3)
dtmCutOff33 = DateSerial(Year(date), 3, 24)
dtmCutOff36 = DateSerial(Year(date), 5, 15)
Me.Grade30.Locked = Date() > dtmCutOff30
Me.Grade33.Locked = Date() > dtmCutOff33
Me.Grade36.Locked = Date() > dtmCutOff36

 

[Klatuu]

Great. Now, be aware this will work for future years provided the months and
days remain the same. If those dates are different next year, you will have
to modify the lines with the DateSerial functions to use the correct dates.

 

[ItsBarbara]

Yes, I realize this. I already had to change it because I needed April and
this was set to March.

Since I usually create solutions for folks at my school and usually don't
revisit the db very often, this date solution is great for me.

Thanks again for your help.

 

[Klatuu]

Well, i guess i get a failing grade. I don't know that April should be 4

 

[mnature]

Just out of curiousity, if you have a front-end (which is what I suppose this
is), and the computer that it is on has the date changed to an earlier date,
would that bypass this security lock? Or is the date analyzed by the
back-end, using whatever date the server is set to?

It's not whether you are paranoid. It is whether you are paranoid enough .
.. .

 

[Klatuu]

All code runs on the computer that opened the mdb, regardless of what
computer the mdb is on. That would be true if it is a front end or if it is
(shudder) and unsplit database. The Date function returns the system date,
which would be for the local computer.

I know what you are thinking, but there is only so much you can do.

 

[mnature]

Well, hypothetically speaking, you could have a query on the back-end that
finds the date on the server, and then use that date for the lock-out? If
you wanted to be obsessive-compulsive about the whole thing?

 

[Klatuu]

No, that is not the way it works. Even if you open a query on the server, it
runs on your computer.