Can a record in a table be locked to updates based record.field va

[Jimbo213]

We want to lock a record when the status field on that record = 100.

If MasterTable.Status is 100 we'd like to lock that row from any updates
[both updates from the form that displays the table fields and from the table
in datasheet mode.

Is that possible and how to do it?

Here are my thoughts:

1) conditional format each of the 140 fields on the multi-tab form to be
disabled if Score=100 on that record

2) somehow [?] put disable code into each field's OnGotFocus event property

3) lock the specific record from any COMMIT somehow when the form is open to
that record

What-say the experts?

Thanks for your reply & assistance.
Jimbo213

 

[Rick Brandt]

We want to lock a record when the status field on that record = 100.

If MasterTable.Status is 100 we'd like to lock that row from any
updates [both updates from the form that displays the table fields
and from the table in datasheet mode.

Is that possible and how to do it?

Table datasheet view? Absolutely not. Allowing access directly to tables
and having ANY control whatsoever are mutually exclusive concepts..

Here are my thoughts:

1) conditional format each of the 140 fields on the multi-tab form to
be disabled if Score=100 on that record

On a form you only need to set AllowEdits to false.

2) somehow [?] put disable code into each field's OnGotFocus event
property

Fields ain't got no events. Events are only in Forms and Reports.

3) lock the specific record from any COMMIT somehow when the form is
open to that record

What-say the experts?

If you have 140 fields you have WAY bigger problems than worrying about
edits.

The closest thing to what you want is to implement user level security
(difficult and easily hackable) and use that to deny access to the table
entirely. Then you create a "run with owner's permissions" query that the
form is bound to and in that query you allow edits, but in the form using
the query you can use the current event to lock records meeting your
criteria.

 

[Jimbo213]

Thanks Rick - that give me a great start:

1) conditional format each of the 140 fields on the multi-tab form to
be disabled if Score=100 on that record

Rick Answered: On a form you only need to set AllowEdits to false.

2 Questions:

A) When I go to the form properties, there is only a Yes/No dropdown for
AllowEdits. How would code look to make AllowEdits=False if the database
tblD7MASTER field "Score" = 100?

B) Would I put the code you are about to provide in the form's OnCurrent
code or the OnLoad event?
By the way, I already have this code in the OnOpen event
=Forms!frmD7MASTER!CompanyBox.setfocus

Thanks for your reply & assistance.
Jimbo213

We want to lock a record when the status field on that record = 100.

If MasterTable.Status is 100 we'd like to lock that row from any
updates [both updates from the form that displays the table fields
and from the table in datasheet mode.

Is that possible and how to do it?

Table datasheet view? Absolutely not. Allowing access directly to tables
and having ANY control whatsoever are mutually exclusive concepts..

Here are my thoughts:

1) conditional format each of the 140 fields on the multi-tab form to
be disabled if Score=100 on that record

On a form you only need to set AllowEdits to false.

2) somehow [?] put disable code into each field's OnGotFocus event
property

Fields ain't got no events. Events are only in Forms and Reports.

3) lock the specific record from any COMMIT somehow when the form is
open to that record

What-say the experts?

If you have 140 fields you have WAY bigger problems than worrying about
edits.

The closest thing to what you want is to implement user level security
(difficult and easily hackable) and use that to deny access to the table
entirely. Then you create a "run with owner's permissions" query that the
form is bound to and in that query you allow edits, but in the form using
the query you can use the current event to lock records meeting your
criteria.

 

[Rick Brandt]

Thanks Rick - that give me a great start:


Rick Answered: On a form you only need to set AllowEdits to false.

2 Questions:

A) When I go to the form properties, there is only a Yes/No dropdown
for AllowEdits. How would code look to make AllowEdits=False if the
database tblD7MASTER field "Score" = 100?

Using the Current event...

Me.AllowEdits = (Me.Score <> 100)

You might need to also have...

Me.AllowDeletions = (Me.Score <> 100)

 

[Jimbo213]

Using the Current event...
Me.AllowEdits = (Me.Score <> 100)

Rick -

Managers want to always be able to edit the record. Their LogIn is MGR1

Is there a neat 1-line code to add an AND condition to your above code?
This is the result in "primitive" code:

AllowEdits = True if
the SignInName is MGR1
regardless of score value

AllowEdits = False if
SignInName is NOT MGR1 and
Me.Score = 100

 

[Rick Brandt]

Rick -

Managers want to always be able to edit the record. Their LogIn is
MGR1

Is there a neat 1-line code to add an AND condition to your above
code? This is the result in "primitive" code:

AllowEdits = True if
the SignInName is MGR1
regardless of score value

AllowEdits = False if
SignInName is NOT MGR1 and
Me.Score = 100

If Me.Score <> 100 _
Or SignInName = "MGR1" Then
AllowEdits = True
Else
AllowEdits = False
End If

 

[Jimbo213]

THANKS RICK - WORKED TERRIFIC

Posting final code for future "surfers"

Private Sub Form_Current()
'this code locks the row from editing to any user except MGR1 when the
overall status average = 100 [all applicable sub-status = 100]
If Me.Status_Score <> 100 _
Or Application.CurrentUser = "MGR1" Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

End Sub