Locking subform when certain box is checked

[SVE]

Hello everyone,

There are 2 parts to this question and as I am new to advanced functions in
Access, please be explicit.

1--I have a check box field called [Note Complete] on a subform called [BHT]
that, when clicked, I want a popup message asking the user if they are sure
they are finished with their data entry. If they choose yes, I want that
record to be locked from editing and deletion, but I want the user to be able
to view the locked records and also to be able to create a new record using
the navigation buttons on the bottom of the subform. If they choose no, then
I want the check box to remain unchecked and the user to be able to make
changes to the data in the subform's record.

2--Once that is done, what is the best way for the manager to be able to
make corrections to this locked subform. Should I create a new form/query
with only her having access? Or is there a better way?

Thank you all for your continued support and guidance.

 

[banem2]

Hello everyone,

There are 2 parts to this question and as I am new to advanced functions in
Access, please be explicit.

1--I have a check box field called [Note Complete] on a subform called [BHT]
that, when clicked, I want a popup message asking the user if they are sure
they are finished with their data entry.  If they choose yes, I want that
record to be locked from editing and deletion, but I want the user to be able
to view the locked records and also to be able to create a new record using
the navigation buttons on the bottom of the subform.  If they choose no,then
I want the check box to remain unchecked and the user to be able to make
changes to the data in the subform's record.

2--Once that is done, what is the best way for the manager to be able to
make corrections to this locked subform.  Should I create a new form/query
with only her having access?  Or is there a better way?

Thank you all for your continued support and guidance.

1 - Idea is:
When check box is checked and on 'Save record: Yes', save record and
run code to lock all fields. Locking the subform won't help as it will
lock navigation buttons too, as you noted already. Same code needs to
run on event OnCurrent of subform. Something like:

Sub sLockSubform()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
ctl.Locked = True
End If
Next ctl
Set ctl = Nothing
End Sub

Call this code with

Call sLockSubform

when: a) User answer 'Yes' b) from subform event OnCurrent

Above code locks only TextBoxes on form, but better idea is to use Tag
property and tag all fields you want to lock. Then you can use the
following code:

Sub sLockSubform()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "ToggleLock" Then
ctl.Locked = True
End If
Next ctl
Set ctl = Nothing
End Sub

Tag fields for locking with "ToggleLock" (without quotes). This code
was written by Sandra Daigle [Microsoft Access MVP].

2) Usually I have log-in form with users and admins. When there is
admin logged on, fields on form are not locked. You can simplify it
without log-in form and read only Windows user logged in with this
function and compare it with string, name of manager. Copy this
function in new module:

Function GetNetworkUserName() As String
' Returns network user name: Manager
Dim s As String, n As Long
n = 255
s = Space(255)
If NetworkUserName(s, n) > 0 Then
GetNetworkUserName = Left(s, n - 1)
Else
GetNetworkUserName = "Not Logged In"
End If
End Function

Then, when this form opens, you can check if GetNetworkUserName =
"Manager" and skip the code to lock fields. It goes like this:

Private Sub Form_Load()
If GetNetworkUserName <> "Manager" Then
' or any other Windows login name of Manager
Call sLockSubform
End If
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[SVE]

Thank you for your help. I think I misdirected you though. I need to lock
just the current record not the whole form.

Thanks again.

Hello everyone,

There are 2 parts to this question and as I am new to advanced functions in
Access, please be explicit.

1--I have a check box field called [Note Complete] on a subform called [BHT]
that, when clicked, I want a popup message asking the user if they are sure
they are finished with their data entry. If they choose yes, I want that
record to be locked from editing and deletion, but I want the user to be able
to view the locked records and also to be able to create a new record using
the navigation buttons on the bottom of the subform. If they choose no, then
I want the check box to remain unchecked and the user to be able to make
changes to the data in the subform's record.

2--Once that is done, what is the best way for the manager to be able to
make corrections to this locked subform. Should I create a new form/query
with only her having access? Or is there a better way?

Thank you all for your continued support and guidance.

1 - Idea is:
When check box is checked and on 'Save record: Yes', save record and
run code to lock all fields. Locking the subform won't help as it will
lock navigation buttons too, as you noted already. Same code needs to
run on event OnCurrent of subform. Something like:

Sub sLockSubform()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
ctl.Locked = True
End If
Next ctl
Set ctl = Nothing
End Sub

Call this code with

Call sLockSubform

when: a) User answer 'Yes' b) from subform event OnCurrent

Above code locks only TextBoxes on form, but better idea is to use Tag
property and tag all fields you want to lock. Then you can use the
following code:

Sub sLockSubform()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "ToggleLock" Then
ctl.Locked = True
End If
Next ctl
Set ctl = Nothing
End Sub

Tag fields for locking with "ToggleLock" (without quotes). This code
was written by Sandra Daigle [Microsoft Access MVP].

2) Usually I have log-in form with users and admins. When there is
admin logged on, fields on form are not locked. You can simplify it
without log-in form and read only Windows user logged in with this
function and compare it with string, name of manager. Copy this
function in new module:

Function GetNetworkUserName() As String
' Returns network user name: Manager
Dim s As String, n As Long
n = 255
s = Space(255)
If NetworkUserName(s, n) > 0 Then
GetNetworkUserName = Left(s, n - 1)
Else
GetNetworkUserName = "Not Logged In"
End If
End Function

Then, when this form opens, you can check if GetNetworkUserName =
"Manager" and skip the code to lock fields. It goes like this:

Private Sub Form_Load()
If GetNetworkUserName <> "Manager" Then
' or any other Windows login name of Manager
Call sLockSubform
End If
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP