Lock and Unlock Select Records

[CJ]

Hi (A2K3)

Is it possible to be able to lock a select group of records via a button and
also be able to unlock them with a button?

After a time period is closed off by the finance department, we want to be
sure that those records are not edited in the database. It would be ideal if
we were able to push a command button, enter an end date and then have all
of the records up to that time period "locked".

I'm sure that it has to tie in to the record locking property but I have no
idea how to go about it.

Can somebody help me out here please?

Thanks

 

[Guest]

I assume you have a field in your table that contains the date after which a
record should not be changed.

Here are 2 ways to do this. Both solutions assume that all your data entry
is done via a form.

Solution 1
Make sure to base your form on a query (or via a filter) to filter out all
records where the current date is greater or equal to the "Do-Not-Change"
date field. This won't work if you still need the users to be able to see
all the records whether or not they are to be 'locked".

Solution 2
Use the form's Current event to check if today's date is past the
"Do-Not-Change Date". If it is, then set the form's .AllowEdits to False.
This solution allows your users to still see the 'locked' records but can't
change anything.

e.g.
Private Sub Form_Current()
'Me.dtLock = field that contains date past which record is not to be
edited
'Only allow edits if today's date is than the value for dtLock
Me.AllowEdits = (Date < Me.dtLock)
End Sub

Private Sub Form_Open(Cancel As Integer)
'Set appropriate value for form's .AllowEdits property
Form_Current
End Sub

 

[CJ]

Brilliant, that should do it.

Thanks

 

[Guest]

Brilliant, that should do it.

Thanks

Slight tweak to handle when you are on a new record or if your date field is
Null.

Private Sub Form_Current()
If Me.NewRecord Then
Me.AllowEdits = True
Else
Me.AllowEdits = Not (Date >= Nz(Me.dtLock, Date + 1))
End If
End Sub

I created a sample and posted it on my website. Thank you for posting a
question that inspired that sample!

 

[CJ]

You are most welcome and thank you for the modification

CJ