473,287 Members | 3,228 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Locking records - Access 97

MS
Access 97 here.

I want a simple way to "lock" certain records on a form. Some records remain
"live" until all data is available which happens over time. When all the
fields are complete, I want the record to be able to be "locked", or
intentionally unlocked.

I thought of having a check box called "chkedit" that can be clicked when
the data entry for the record is complete. I have the following code in the
OnCurrent event for the form...

If Me.chkedit = True Then
Me.Allowedits = False
Else
Me.Allowedits = True
End if

This doesn't work. But when I have the same code in the in the AfterUpdate
event for the checkbox, it "locks" the record while the record is current,
but when moving off the record and returning to it the record is "unlocked"
again inspite of the CheckBox being true.

Ok, obviously there is a better way of "locking" a record. In the past I
have used the CheckBox method, and set each control to Locked if the
checkbox is true on the forms OnCurrent event. This works, but it is
cumbersome.

Any thoughts?

Cheers!

Nov 13 '05 #1
6 2769
You need both the AfterUpdate of the check box, and also the Current event
of the form.

Simplest way to maintain that is to add this line to Form_Current:
Call chkedit_AfterUpdate

Other suggestions:
1. Be sure to set the Default Value for the unbound check box, so it does
not default to Null.

2. If the record is dirty at the time you click this box, Access will allow
the edit to continue until the record is saved. This might confuse the user,
so you may want to explicitly save before locking.

3. Did you want to prevent deletions as well?

4. Once you set AllowEdits to No, you won't be able to change even the
unbound controls. Therefore it might be better to use a command button than
a check box.

The result will be something like this:

Private Sub cmdLock_Click()
Dim bAllow As Boolean

If Me.Dirty Then Me.Dirty = False
bAllow = Not Me.AllowEdits
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:tn*******************@news-server.bigpond.net.au...
Access 97 here.

I want a simple way to "lock" certain records on a form. Some records
remain "live" until all data is available which happens over time. When
all the fields are complete, I want the record to be able to be "locked",
or intentionally unlocked.

I thought of having a check box called "chkedit" that can be clicked when
the data entry for the record is complete. I have the following code in
the OnCurrent event for the form...

If Me.chkedit = True Then
Me.Allowedits = False
Else
Me.Allowedits = True
End if

This doesn't work. But when I have the same code in the in the AfterUpdate
event for the checkbox, it "locks" the record while the record is current,
but when moving off the record and returning to it the record is
"unlocked" again inspite of the CheckBox being true.

Ok, obviously there is a better way of "locking" a record. In the past I
have used the CheckBox method, and set each control to Locked if the
checkbox is true on the forms OnCurrent event. This works, but it is
cumbersome.

Any thoughts?

Cheers!

Nov 13 '05 #2
MS
Thanks for the reply Allen. BTW, love your site - truely an unselfish act of
public service :-)

I should have said originally that the check box is bound to a field in the
forms table, so that when the user returns to the record, if all fields are
not yet completed, the record is still "editable" if the check box is false.
When all fields are complete, the user clicks the check box (suposedly)
rendering the record "uneditable".

I was expecting the OnCurrent event of the form to "read" the status of the
check box, and make each record editable or non editable on a record to
record basis depending on whether the checkbox result was true or false.

Any thoughts?

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
You need both the AfterUpdate of the check box, and also the Current event
of the form.

Simplest way to maintain that is to add this line to Form_Current:
Call chkedit_AfterUpdate

Other suggestions:
1. Be sure to set the Default Value for the unbound check box, so it does
not default to Null.

2. If the record is dirty at the time you click this box, Access will
allow the edit to continue until the record is saved. This might confuse
the user, so you may want to explicitly save before locking.

3. Did you want to prevent deletions as well?

4. Once you set AllowEdits to No, you won't be able to change even the
unbound controls. Therefore it might be better to use a command button
than a check box.

The result will be something like this:

Private Sub cmdLock_Click()
Dim bAllow As Boolean

If Me.Dirty Then Me.Dirty = False
bAllow = Not Me.AllowEdits
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:tn*******************@news-server.bigpond.net.au...
Access 97 here.

I want a simple way to "lock" certain records on a form. Some records
remain "live" until all data is available which happens over time. When
all the fields are complete, I want the record to be able to be "locked",
or intentionally unlocked.

I thought of having a check box called "chkedit" that can be clicked when
the data entry for the record is complete. I have the following code in
the OnCurrent event for the form...

If Me.chkedit = True Then
Me.Allowedits = False
Else
Me.Allowedits = True
End if

This doesn't work. But when I have the same code in the in the
AfterUpdate event for the checkbox, it "locks" the record while the
record is current, but when moving off the record and returning to it the
record is "unlocked" again inspite of the CheckBox being true.

Ok, obviously there is a better way of "locking" a record. In the past I
have used the CheckBox method, and set each control to Locked if the
checkbox is true on the forms OnCurrent event. This works, but it is
cumbersome.

Any thoughts?

Cheers!


Nov 13 '05 #3
Okay: so the bound check box is appropriate.

You should find that the Form_Current event works fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:EO*******************@news-server.bigpond.net.au...
Thanks for the reply Allen. BTW, love your site - truely an unselfish act
of public service :-)

I should have said originally that the check box is bound to a field in
the forms table, so that when the user returns to the record, if all
fields are not yet completed, the record is still "editable" if the check
box is false. When all fields are complete, the user clicks the check box
(suposedly) rendering the record "uneditable".

I was expecting the OnCurrent event of the form to "read" the status of
the check box, and make each record editable or non editable on a record
to record basis depending on whether the checkbox result was true or
false.

Any thoughts?

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
You need both the AfterUpdate of the check box, and also the Current
event of the form.

Simplest way to maintain that is to add this line to Form_Current:
Call chkedit_AfterUpdate

Other suggestions:
1. Be sure to set the Default Value for the unbound check box, so it does
not default to Null.

2. If the record is dirty at the time you click this box, Access will
allow the edit to continue until the record is saved. This might confuse
the user, so you may want to explicitly save before locking.

3. Did you want to prevent deletions as well?

4. Once you set AllowEdits to No, you won't be able to change even the
unbound controls. Therefore it might be better to use a command button
than a check box.

The result will be something like this:

Private Sub cmdLock_Click()
Dim bAllow As Boolean

If Me.Dirty Then Me.Dirty = False
bAllow = Not Me.AllowEdits
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:tn*******************@news-server.bigpond.net.au...
Access 97 here.

I want a simple way to "lock" certain records on a form. Some records
remain "live" until all data is available which happens over time. When
all the fields are complete, I want the record to be able to be
"locked", or intentionally unlocked.

I thought of having a check box called "chkedit" that can be clicked
when the data entry for the record is complete. I have the following
code in the OnCurrent event for the form...

If Me.chkedit = True Then
Me.Allowedits = False
Else
Me.Allowedits = True
End if

This doesn't work. But when I have the same code in the in the
AfterUpdate event for the checkbox, it "locks" the record while the
record is current, but when moving off the record and returning to it
the record is "unlocked" again inspite of the CheckBox being true.

Ok, obviously there is a better way of "locking" a record. In the past I
have used the CheckBox method, and set each control to Locked if the
checkbox is true on the forms OnCurrent event. This works, but it is
cumbersome.

Any thoughts?

Cheers!

Nov 13 '05 #4
MS
Unfortunately I've never been able to get it work that way.

I'm back to looping through each control, and setting them to "locked"
depending on whether the bound checkbox result is true or false - of course
the bound check box is left unlocked. This effectly makes the record
"uneditable".

I've always thought this was a bit messy, and it would be much neater if I
could get Me.AllowEdits = False to work if the check box result is false on
the Form Current event - but i've never had any joy there. I must be missing
something!

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Okay: so the bound check box is appropriate.

You should find that the Form_Current event works fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:EO*******************@news-server.bigpond.net.au...
Thanks for the reply Allen. BTW, love your site - truely an unselfish act
of public service :-)

I should have said originally that the check box is bound to a field in
the forms table, so that when the user returns to the record, if all
fields are not yet completed, the record is still "editable" if the check
box is false. When all fields are complete, the user clicks the check box
(suposedly) rendering the record "uneditable".

I was expecting the OnCurrent event of the form to "read" the status of
the check box, and make each record editable or non editable on a record
to record basis depending on whether the checkbox result was true or
false.

Any thoughts?

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
You need both the AfterUpdate of the check box, and also the Current
event of the form.

Simplest way to maintain that is to add this line to Form_Current:
Call chkedit_AfterUpdate

Other suggestions:
1. Be sure to set the Default Value for the unbound check box, so it
does not default to Null.

2. If the record is dirty at the time you click this box, Access will
allow the edit to continue until the record is saved. This might confuse
the user, so you may want to explicitly save before locking.

3. Did you want to prevent deletions as well?

4. Once you set AllowEdits to No, you won't be able to change even the
unbound controls. Therefore it might be better to use a command button
than a check box.

The result will be something like this:

Private Sub cmdLock_Click()
Dim bAllow As Boolean

If Me.Dirty Then Me.Dirty = False
bAllow = Not Me.AllowEdits
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:tn*******************@news-server.bigpond.net.au...
Access 97 here.

I want a simple way to "lock" certain records on a form. Some records
remain "live" until all data is available which happens over time. When
all the fields are complete, I want the record to be able to be
"locked", or intentionally unlocked.

I thought of having a check box called "chkedit" that can be clicked
when the data entry for the record is complete. I have the following
code in the OnCurrent event for the form...

If Me.chkedit = True Then
Me.Allowedits = False
Else
Me.Allowedits = True
End if

This doesn't work. But when I have the same code in the in the
AfterUpdate event for the checkbox, it "locks" the record while the
record is current, but when moving off the record and returning to it
the record is "unlocked" again inspite of the CheckBox being true.

Ok, obviously there is a better way of "locking" a record. In the past
I have used the CheckBox method, and set each control to Locked if the
checkbox is true on the forms OnCurrent event. This works, but it is
cumbersome.

Any thoughts?

Cheers!


Nov 13 '05 #5
My personal preference is to loop through all the controls on the form to
set their Locked property also.

Below is the actual function I use. To lock all the controls in the current
form, use:
LockBoundContorls(Me, True)
and to unlock them again:
LockBoundContorls(Me, False)

The interesting parts are:
- only bound controls are locked;
- subforms are locked also;
- you can specify controls not to be locked.

------------------code starts--------------------
Public Function LockBoundControls(frm As Form, bLock As Boolean, ParamArray
avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form any
its subforms.
'Arguments frm = the form to be locked
' bLock = Trur to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to lock
(variant array of strings).
'Usage: Call LockBoundControls(Me. True)
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean

'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0& And Not ctl.ControlSource
Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0& Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If

Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
acPage, acPageBreak, acImage, acObjectFrame
'Do nothing

Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled on " & conMod & " at " &
Now()
End Select
Next

Exit_Handler:
Set ctl = Nothing
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod & "LockBoundControls")
Resume Exit_Handler
End Function
------------------code ends--------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"MS" <Em***@Myemail.com> wrote in message
news:aZ*******************@news-server.bigpond.net.au...
Unfortunately I've never been able to get it work that way.

I'm back to looping through each control, and setting them to "locked"
depending on whether the bound checkbox result is true or false - of
course the bound check box is left unlocked. This effectly makes the
record "uneditable".

I've always thought this was a bit messy, and it would be much neater if I
could get Me.AllowEdits = False to work if the check box result is false
on the Form Current event - but i've never had any joy there. I must be
missing something!

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Okay: so the bound check box is appropriate.

You should find that the Form_Current event works fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:EO*******************@news-server.bigpond.net.au...
Thanks for the reply Allen. BTW, love your site - truely an unselfish
act of public service :-)

I should have said originally that the check box is bound to a field in
the forms table, so that when the user returns to the record, if all
fields are not yet completed, the record is still "editable" if the
check box is false. When all fields are complete, the user clicks the
check box (suposedly) rendering the record "uneditable".

I was expecting the OnCurrent event of the form to "read" the status of
the check box, and make each record editable or non editable on a record
to record basis depending on whether the checkbox result was true or
false.

Any thoughts?

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
You need both the AfterUpdate of the check box, and also the Current
event of the form.

Simplest way to maintain that is to add this line to Form_Current:
Call chkedit_AfterUpdate

Other suggestions:
1. Be sure to set the Default Value for the unbound check box, so it
does not default to Null.

2. If the record is dirty at the time you click this box, Access will
allow the edit to continue until the record is saved. This might
confuse the user, so you may want to explicitly save before locking.

3. Did you want to prevent deletions as well?

4. Once you set AllowEdits to No, you won't be able to change even the
unbound controls. Therefore it might be better to use a command button
than a check box.

The result will be something like this:

Private Sub cmdLock_Click()
Dim bAllow As Boolean

If Me.Dirty Then Me.Dirty = False
bAllow = Not Me.AllowEdits
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:tn*******************@news-server.bigpond.net.au...
> Access 97 here.
>
> I want a simple way to "lock" certain records on a form. Some records
> remain "live" until all data is available which happens over time.
> When all the fields are complete, I want the record to be able to be
> "locked", or intentionally unlocked.
>
> I thought of having a check box called "chkedit" that can be clicked
> when the data entry for the record is complete. I have the following
> code in the OnCurrent event for the form...
>
> If Me.chkedit = True Then
> Me.Allowedits = False
> Else
> Me.Allowedits = True
> End if
>
> This doesn't work. But when I have the same code in the in the
> AfterUpdate event for the checkbox, it "locks" the record while the
> record is current, but when moving off the record and returning to it
> the record is "unlocked" again inspite of the CheckBox being true.
>
> Ok, obviously there is a better way of "locking" a record. In the past
> I have used the CheckBox method, and set each control to Locked if the
> checkbox is true on the forms OnCurrent event. This works, but it is
> cumbersome.
>
> Any thoughts?
>
> Cheers!



Nov 13 '05 #6
MS
Yup. Definitely the way to go.

I appreciate your help!

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
My personal preference is to loop through all the controls on the form to
set their Locked property also.

Below is the actual function I use. To lock all the controls in the
current form, use:
LockBoundContorls(Me, True)
and to unlock them again:
LockBoundContorls(Me, False)

The interesting parts are:
- only bound controls are locked;
- subforms are locked also;
- you can specify controls not to be locked.

------------------code starts--------------------
Public Function LockBoundControls(frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form any
its subforms.
'Arguments frm = the form to be locked
' bLock = Trur to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to lock
(variant array of strings).
'Usage: Call LockBoundControls(Me. True)
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean

'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0& And Not
ctl.ControlSource Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0& Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If

Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
acPage, acPageBreak, acImage, acObjectFrame
'Do nothing

Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled on " & conMod & " at " &
Now()
End Select
Next

Exit_Handler:
Set ctl = Nothing
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
"LockBoundControls")
Resume Exit_Handler
End Function
------------------code ends--------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"MS" <Em***@Myemail.com> wrote in message
news:aZ*******************@news-server.bigpond.net.au...
Unfortunately I've never been able to get it work that way.

I'm back to looping through each control, and setting them to "locked"
depending on whether the bound checkbox result is true or false - of
course the bound check box is left unlocked. This effectly makes the
record "uneditable".

I've always thought this was a bit messy, and it would be much neater if
I could get Me.AllowEdits = False to work if the check box result is
false on the Form Current event - but i've never had any joy there. I
must be missing something!

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Okay: so the bound check box is appropriate.

You should find that the Form_Current event works fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MS" <Em***@Myemail.com> wrote in message
news:EO*******************@news-server.bigpond.net.au...
Thanks for the reply Allen. BTW, love your site - truely an unselfish
act of public service :-)

I should have said originally that the check box is bound to a field in
the forms table, so that when the user returns to the record, if all
fields are not yet completed, the record is still "editable" if the
check box is false. When all fields are complete, the user clicks the
check box (suposedly) rendering the record "uneditable".

I was expecting the OnCurrent event of the form to "read" the status of
the check box, and make each record editable or non editable on a
record to record basis depending on whether the checkbox result was
true or false.

Any thoughts?

Cheers!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
> You need both the AfterUpdate of the check box, and also the Current
> event of the form.
>
> Simplest way to maintain that is to add this line to Form_Current:
> Call chkedit_AfterUpdate
>
> Other suggestions:
> 1. Be sure to set the Default Value for the unbound check box, so it
> does not default to Null.
>
> 2. If the record is dirty at the time you click this box, Access will
> allow the edit to continue until the record is saved. This might
> confuse the user, so you may want to explicitly save before locking.
>
> 3. Did you want to prevent deletions as well?
>
> 4. Once you set AllowEdits to No, you won't be able to change even the
> unbound controls. Therefore it might be better to use a command button
> than a check box.
>
> The result will be something like this:
>
> Private Sub cmdLock_Click()
> Dim bAllow As Boolean
>
> If Me.Dirty Then Me.Dirty = False
> bAllow = Not Me.AllowEdits
> Me.AllowEdits = bAllow
> Me.AllowDeletions = bAllow
> Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
> End Sub
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "MS" <Em***@Myemail.com> wrote in message
> news:tn*******************@news-server.bigpond.net.au...
>> Access 97 here.
>>
>> I want a simple way to "lock" certain records on a form. Some records
>> remain "live" until all data is available which happens over time.
>> When all the fields are complete, I want the record to be able to be
>> "locked", or intentionally unlocked.
>>
>> I thought of having a check box called "chkedit" that can be clicked
>> when the data entry for the record is complete. I have the following
>> code in the OnCurrent event for the form...
>>
>> If Me.chkedit = True Then
>> Me.Allowedits = False
>> Else
>> Me.Allowedits = True
>> End if
>>
>> This doesn't work. But when I have the same code in the in the
>> AfterUpdate event for the checkbox, it "locks" the record while the
>> record is current, but when moving off the record and returning to it
>> the record is "unlocked" again inspite of the CheckBox being true.
>>
>> Ok, obviously there is a better way of "locking" a record. In the
>> past I have used the CheckBox method, and set each control to Locked
>> if the checkbox is true on the forms OnCurrent event. This works, but
>> it is cumbersome.
>>
>> Any thoughts?
>>
>> Cheers!



Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ryan | last post by:
I have a problem with record locking / blocking within an application. The app is quite straight forward. Written in Delphi 5 using BDE to access a SQL 7 database (Win2K server). Every so often...
1
by: Justin | last post by:
Hello: I have a question regarding Record Locking in ASP...If the user x's out of the web page, how can I unlock the record? A co-worker told me it has to be done in Javascript, but he doesn't...
2
by: Scott Bryce | last post by:
I am creating a CGI application in Perl that uses an Access database. It will be hosted on an NT server. I have used flat file DBMs (tied hashes) on UNIX servers, but I am not familiar with how...
9
by: Lauren Wilson | last post by:
Hi Folks, We've been using Crypto ++32 to control licensed access to our widely distributed Access 2K app. Unfortunately, Sampson Multimedia appears to be out of business. Does anyone out...
0
by: brijeshmathew | last post by:
Hi I use Visual Basic 6, Service Pack 6, Microsoft ActiveX Data Objects 2.8 Library(msado15.dll) and access 2000 database using JET 4 OLE. I have an application that adds records simultaneously...
5
by: swapna_munukoti | last post by:
Hi all, Is there any tool to achieve record locking in MS Access 2000. Thanks, Swapna.
8
by: paulwilliamsonremove | last post by:
Hi, Using Access 2003. I am trying to delete records from one table/query ("qryHistoryPersonIDs") if they don't exist in another table ("qryDonations"). But the SQL syntax I came up with is...
9
by: master | last post by:
Actually, it is not only the record locking, what I need, and nobody seems to descibe this. Imagine the following scenario. There is a database with, say 10000 records with some unvalidated...
3
by: kstevens | last post by:
I have turned on record locking do to the fact that we are starting to have issues with the records not being locked. I have a button on a form that changes the recordsource of the form. When i...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.