By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Lock fields based on status change

P: 1
i have a form that captures data but once a specific status is selected it can no longer be changed (status 3 or 6),
then if status = 5 it locks specific fields on the form but leaves a few fields still open.
else all fields are available.
I have some VB knowledge but not much.
I'm hoping i don't have to setfocus on each field then change the property since there are many. Then is the status changes back from 5 to 4 have to undo the same way...
Here's what i have so far:
Private Sub Status_Change()

If Status.Value = 3 Or Status.Value = 6 Then

MsgBox "You cannot change the status of this NCR Case, please see a CAPA administrator."


If Status.Value = 5 Then
Jun 8 '12 #1
Share this Question
Share on Google+
2 Replies

P: 759
First of all, Welcome to the forum !

In a form you have not fields.
Only the tables and the queries have fields.
A form have only controls that can be (or not) bound to a field.

Is not necessary to set focus to each control but is necessary to set, for each control, it's locked property as you need based on the status.

Use the tag property for each control to store the status when control is locked (or when is not locked).

In the bellow code I use the tag property to store the statuses when the control is locked (see also, the attachment):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub cmdStatus1_Click()
  5.     Call SetLock(1)
  6. End Sub
  8. Private Sub cmdStatus2_Click()
  9.     Call SetLock(2)
  10. End Sub
  12. Private Sub cmdStatus3_Click()
  13.     Call SetLock(3)
  14. End Sub
  16. Private Sub cmdStatus4_Click()
  17.     Call SetLock(4)
  18. End Sub
  20. Private Sub SetLock(Status As Long)
  21. Dim ctl As Control, i As Long, ArrayStatus
  22. On Error Resume Next
  23.     For Each ctl In Me.Controls
  24.         If ctl.Tag <> "" Then 'This control is afected by status
  25.             ctl.Locked = False 'Now, the control is, no more, locked
  26.             ctl.Value = "You can change me. Try !"
  27.             ArrayStatus = Split(ctl.Tag, ",")
  28.             For i = 0 To UBound(ArrayStatus)
  29.                 If ArrayStatus(i) = Status Then 'The Status "say" that this control must be locked
  30.                     ctl.Locked = True
  31.                     ctl = "Can't change me. Try !"
  32.             Exit For
  33.                 End If
  34.             Next i
  35.         End If
  36.     Next ctl
  37. End Sub
Attached Files
File Type: zip (37.4 KB, 73 views)
Jun 9 '12 #2

Expert 5K+
P: 8,638
A Variation along the same lines, with the Tag Property of 'Involved' Controls = 'Lock':
Expand|Select|Wrap|Line Numbers
  1. Private Sub Status_AfterUpdate()
  2. On Error Resume Next
  3. Dim ctl As Control
  5. With Me
  6.   If IsNull(![Status]) Then
  7.     Exit Sub
  8.   Else
  9.     For Each ctl In .Controls
  10.       If ctl.Tag = "Lock" Then
  11.         ctl.Locked = (![Status] = 3 Or ![Status] = 6)
  12.       End If
  13.     Next
  14.   End If
  15. End With
  16. End Sub
Jun 9 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.