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

Jimbo213 wrote:
> 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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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


"Rick Brandt" wrote:

> Jimbo213 wrote:
> > 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, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

Jimbo213 wrote:
> 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?

Using the Current event...

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

You might need to also have...

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


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

> 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
--
Thanks for your reply & assistance.
Jimbo213

Jimbo213 wrote:
>> 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

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

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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


--
Thanks for your reply & assistance.
Jimbo213


"Rick Brandt" wrote:

> Jimbo213 wrote:
> >> 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
>
> If Me.Score <> 100 _
> Or SignInName = "MGR1" Then
> AllowEdits = True
> Else
> AllowEdits = False
> End If
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>