MS Access - locking fields (1 Viewer)

[tf-nitrof]

The Security Officer has asked me to create a little something to manage the records of the various tasks that must be done ​​throughout the day. Therefore, I chose MS Access to do the task.

Here is the schedule of the person responsible for the shift that starts at 9:30 in the morning:

9:30 a.m. to 6:00 p.m., after what the building is locked until the next morning, and
6:00 a.m. to 9:30 a.m., where someone else takes over for the new day.

So, everyday a new shift worker comes in and log himself in on the workstation.

Now here is my issue:

I want, the person who goes off shift at 9:30 have an extra 30 minutes to make any changes to the information he recorded in the database if required, after which he will have to contact the Security Officer. So, after 10:00 a.m., it is no longer possible for anyone to make changes to the previous day records.

In the table (T_Rapport of Svc), I have DateMod and TimeMod fields which are populated with NOW () and a field 'EnteredBy' which prevents anyone from changing records when activating the working form, with the exception of the person who created the record. The field 'EnteredBy’ is populated with the username of the person who is logged in.

According to my research, the fact of using DateTimeStamp would work, but where exactly I can apply this option still remains a mystery for me.

So, my question is as follows: How to prevent changes to the records past 10:00 AM?

Thank you in advance for your help in hoping my details are clear enough.

 

[Pat Hartman]

It is not necessary to have both date and time fields especially since they both contain the same information. Just keep one and use Now() to populate it. That saves both the date and the time. Use the FORM's Dirty event to recognize when the record is being changed. If the record is not new, compare the current value of now with the record being changed to see if the change is valid.

If Me.NewRecord Then
    Exit Sub
End If
If Date() = DateValue(Me.DateMod) Then  ' record is being modified on the same date it was created.
    Exit Sub
Else
    If DateDiff("d", Me.DateMod, Date) = 1 Then ' record was created yesterday
        If TimeValue(Now()) <= #10:00 AM# Then  ' allow change
            Exit Sub
        Else
            Msgbox "Yesterday's records can no longer be updated.", vbOKOnly
            Me.Undo
            Exit Sub
         End If
    Else
        Msgbox "Older records cannot be updated.", vbOKOnly
        Me.Undo
        Exit Sub
    End If
End If

 

[tf-nitrof]

Thanks a million Pat,

That was exactly what I was looking for.

Thanks again!


Stéphane

 

[tf-nitrof]

Hi Pat,

May I bother you once again???

Everything works on a day to day basic as we can do changes the next morning from 06:00am to 10:00am.

One point I forgot to mention is that the building is closed during the weekends, so, the person on duty on Friday can not make any changes on Monday morning.

So, how can I bypass the weekends (Saturdays and Sundays) and carry on with the same recordset that was created on the previous Friday?

I tried to play with Format(Me.DateMod, "dddd") to extract the day's name and use it in a condition (if... then... end if) but it doesn't work.

Thanks again for you help!

Stéphane

 

[Clon]

Hi, tf-nitrof

You can check the day of the week so that if it is monday, changes are allowed when date difference is 3 days or less.

The undo command doesn't work in the Dirty event, you can edit the record once you close the message box. You should use the BeforeUpdate event instead.

The code is the same, but you must add the new condition

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Exit Sub
    End If
    If Date = DateValue(Me.DateMod) Then    ' record is being modified on the same date it was created.
        Exit Sub
    Else
        If ((DateDiff("d", Me.DateMod, Date) = 1 Or (DatePart("w", Date, vbSunday) = 2 And DateDiff("d", Me.DateMod, Date) < 4))) And (TimeValue(Now()) <= #10:00:00 AM#) Then   ' allow change
            Exit Sub
        Else
            MsgBox "Record can no longer be updated.", vbOKOnly
            Me.Undo
            Exit Sub
        End If
    End If
End Sub

Additionally you must ensure that the user cannot change the system date.
Otherwise, it would be possible to trick the system so changes would be still possible outside the intended range. And the user shouldn't change the DataMod field: the database would allow the change if the new date is equal or greater than today.

This restriction can be easily bypassed by directly accessing the table, instead of using the form. You can help this by setting the right startup options, but still the user can hold "CTRL" key while running the application.

When I need more security, I divide the database in frontend and backend. The data are in the backend, and the frontend has access to them through ADO or DAO, using a secret user/password combination, different from the logged user.

Regards,