Locking Records

[Asif Rehman]

Hello

Is it possible to lock certain records in a form but not
all of them.

For example I have created a invoicing database and what I
would like to do is when a invoice is created after the
day it is created those records to be locked (kind of a
over night process) and any other previous, but to be able
to create to records when required.

I hope I have explained it well, any help will be much
appreciated.

Kind Regards
Asif

 

[Sandra Daigle]

There are a variety of ways to do this in a form - each one using the
Current Event of the form to check a status field or whatever field you have
that indicates whether the record is to be locked.

The easiest (though it has drawbacks) is to toggle the AllowEdits property
of the form. In the following, the record will be 'locked' when the
InvoiceStatus is >= 2. The drawback to using the AllowEdits property of the
form is that it also locks unbound controls when it is false. This means
that an unbound control used for locating a record is locked when the rest
of the form is locked. Not ideal in many cases.

Private Sub Form_Current()
Me.AllowEdits = me.InvoiceStatus<2
End Sub

Note that the above is a shortcut for the following


Private Sub Form_Current()
if me.invoiceStatus <2 then
me.allowedits=true
else
me.allowedits=false
end if
End Sub

The other way is to toggle the Locked property of individual controls based
on the value of InvoiceStatus. Put some keyword in the Tag property of each
control that should be conditionally locked then loop through all the
controls and toggle the locked property of the relevant controls.

Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "ToggleLock" Then
ctl.Locked = Me.InvoiceStatus>= 2
End If
Next ctl
Set ctl = Nothing
End Sub

 

[Bakema]

This is the way I do this, and it works fine for me:

Give the controls that you want to lock a tag in the
control propertysheet: tag property, for instance the
tag "lock" (without the quotes).

Then, on the form's activate or current event write a
little routine as follows:

For Each ctl In Me
If ctl.Tag = "lock" Then
ctl.BackColor = yellow
ctl.Locked = True
End If
Next

(this yellow is based on constant declaration whereby
yellow is a colournumber, the yellow colour indicates
that the control is locked)

If you put it in the current event every time you change
a record the controls will lock, in the activate event it
will open with the controls locked but once you unlock it
will not lock automatically. You can unlock the controls
with a togglebutton with a click event as follows:

If tgl = True Then
tgl.Caption = "Lock Record"
For Each ctl In Me
If ctl.Tag = "lock" Then
ctl.BackColor = white
ctl.Locked = False
End If
Next

Else
tgl.Caption = "Edit Record"
For Each ctl In Me
If ctl.Tag = "Lock" Then
ctl.BackColor = yellow
ctl.Locked = True
End If
Next
End if

(white is also a constant refering to the colournumber
for white, the tgl.caption lines change the caption to
indicate what happens when you click the tglbutton)

You may need to write a bit of VBA to manipulate the
toggle button on activate/current of the form, to set it
to false ('out') when the form opens, or changes a record
(on current):

tgl = false
tgl.caption = "Edit record"

Hope this helps.

Bakema