Solved Lock/Unlock Textboxes based on multiple criteria (1 Viewer)

randallst

Registered User.
Local time
Today, 18:05
Joined
Jan 28, 2015
Messages
64
Hi All,

I have been working on this for too long now and I am just banging my head against a wall now, admitting defeat and hoping someone might be able to help me!

Background is that im making an Internal Incidents database, and each text box is locked/unlocked depending on your Department (if you're the raiser / investigator etc). This seems to work fine, but what I want to do is when a record is "Closed" (this is a tick box called "Closed"), I want the record to be locked.

What I did do is put If Me.Closed.Value = TRUE Then Me.TEXTBOXNAMEHERE.locked = True for each textbox at the end of the following code, but it kept it all editable. I have also tried If Forms![Frm_Internal]![UserDepartment] = Me.Investigation_Department.Value And Me.Closed.Value = False Then Flag = 1 (i done this for the Flag = 2 and 3 lines too) but still no luck.

Any help is greatly appreciated. Sorry if my question is not clear, heads losing it and think i may need to walk away for a bit haha!

All the best
Stuart

Code:
Private Sub Form_Current()
On Error Resume Next

DoCmd.ShowToolbar "Ribbon", acToolbarNo
Application.CommandBars("Ribbon").Visible = False

Dim Flag As Integer
Flag = 0

'Display AD Name, Department and Permission Status
Me.User = Environ("Username")
Me.Permission = DLookup("Permissions", "Tbl_Users", "Alias=" & "User")
Me.UserDepartment = DLookup("Department", "Tbl_Users", "Alias=" & "User")

'Hide fields
Me.New_Incident_Button.Visible = True

'Checking Permissions
If Forms![Frm_Internal]![UserDepartment] = Me.Investigation_Department.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 1
If Forms![Frm_Internal]![UserDepartment] = Me.RaisedDepartment.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 2
If Forms![Frm_Internal]![Permission] = "Admin" And Forms![Frm_Internal]![Closed] = False Then Flag = 3

'Makes items accessable for "Investigating Departments"
If Flag = 1 Then Me.Contain_Details.Locked = False
If Flag = 1 Then Me.Date_of_Manufacture.Locked = False
If Flag = 1 Then Me.Cause_Details.Locked = False
If Flag = 1 Then Me.Where_Manufactured.Locked = False
If Flag = 1 Then Me.Where_Escaped.Locked = False
If Flag = 1 Then Me.Root_Cause.Locked = False
If Flag = 1 Then Me.Corrective_Details.Locked = False
If Flag = 1 Then Me.Resolution.Locked = False
If Flag = 1 Then Me.Verification_Details.Locked = False
If Flag = 1 Then Me.RequestClose.Locked = False

'Makes items accessable for "Raising Departments"
If Flag = 2 Then Me.Customer_Feedback.Locked = False
If Flag = 2 Then Me.Category.Locked = False
If Flag = 2 Then Me.Investigation_Area.Locked = False
If Flag = 2 Then Me.Investigation_Lead.Locked = False
If Flag = 2 Then Me.Concern_Details.Locked = False
If Flag = 2 Then Me.Feature_Affected.Locked = False
If Flag = 2 Then Me.Fault_Type.Locked = False
If Flag = 2 Then Me.Part_Number.Locked = False
If Flag = 2 Then Me.Shop_Order_Number.Locked = False
If Flag = 2 Then Me.Qty_Defective.Locked = False
If Flag = 2 Then Me.Unit_of_Measure.Locked = False
If Flag = 2 Then Me.Closed.Locked = False

'Makes items accessable for "Admins"
If Flag = 3 Then Me.Contain_Details.Locked = False
If Flag = 3 Then Me.Date_of_Manufacture.Locked = False
If Flag = 3 Then Me.Cause_Details.Locked = False
If Flag = 3 Then Me.Where_Manufactured.Locked = False
If Flag = 3 Then Me.Where_Escaped.Locked = False
If Flag = 3 Then Me.Root_Cause.Locked = False
If Flag = 3 Then Me.Corrective_Details.Locked = False
If Flag = 3 Then Me.Resolution.Locked = False
If Flag = 3 Then Me.Verification_Details.Locked = False
If Flag = 3 Then Me.Customer_Feedback.Locked = False
If Flag = 3 Then Me.Category.Locked = False
If Flag = 3 Then Me.Investigation_Area.Locked = False
If Flag = 3 Then Me.Investigation_Lead.Locked = False
If Flag = 3 Then Me.Concern_Details.Locked = False
If Flag = 3 Then Me.Feature_Affected.Locked = False
If Flag = 3 Then Me.Fault_Type.Locked = False
If Flag = 3 Then Me.Part_Number.Locked = False
If Flag = 3 Then Me.Shop_Order_Number.Locked = False
If Flag = 3 Then Me.Qty_Defective.Locked = False
If Flag = 3 Then Me.Unit_of_Measure.Locked = False
If Flag = 3 Then Me.Closed.Locked = False
If Flag = 3 Then Me.RequestClose.Locked = False

End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2013
Messages
12,783
why not just change the whole form

me.allowedits=not me.closed
 

arnelgp

error reading drive A:
Local time
Tomorrow, 02:05
Joined
May 7, 2009
Messages
11,157
Code:
Private Sub Form_Current()
On Error Resume Next

DoCmd.ShowToolbar "Ribbon", acToolbarNo
Application.CommandBars("Ribbon").Visible = False

Dim Flag As Integer
Flag = 0

'Display AD Name, Department and Permission Status
Me.User = Environ("Username")
Me.Permission = DLookup("Permissions", "Tbl_Users", "Alias=" & "User")
Me.UserDepartment = DLookup("Department", "Tbl_Users", "Alias=" & "User")

'Hide fields
Me.New_Incident_Button.Visible = True

'Checking Permissions
If Forms![Frm_Internal]![UserDepartment] = Me.Investigation_Department.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 1
If Forms![Frm_Internal]![UserDepartment] = Me.RaisedDepartment.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 2
If Forms![Frm_Internal]![Permission] = "Admin" And Forms![Frm_Internal]![Closed] = False Then Flag = 3

'Lock all first
'Makes items accessable for "Admins"
Me.Contain_Details.Locked = True
Me.Date_of_Manufacture.Locked = True
Me.Cause_Details.Locked = True
Me.Where_Manufactured.Locked = True
Me.Where_Escaped.Locked = True
Me.Root_Cause.Locked = True
Me.Corrective_Details.Locked = True
Me.Resolution.Locked = True
Me.Verification_Details.Locked = True
Me.Customer_Feedback.Locked = True
Me.Category.Locked = True
Me.Investigation_Area.Locked = True
Me.Investigation_Lead.Locked = True
Me.Concern_Details.Locked = True
Me.Feature_Affected.Locked = True
Me.Fault_Type.Locked = True
Me.Part_Number.Locked = True
Me.Shop_Order_Number.Locked = True
Me.Qty_Defective.Locked = True
Me.Unit_of_Measure.Locked = True
Me.Closed.Locked = True
Me.RequestClose.Locked = True

Select Case Flag
Case 1
    'Makes items accessable for "Investigating Departments"
    Me.Contain_Details.Locked = False
    Me.Date_of_Manufacture.Locked = False
    Me.Cause_Details.Locked = False
    Me.Where_Manufactured.Locked = False
    Me.Where_Escaped.Locked = False
    Me.Root_Cause.Locked = False
    Me.Corrective_Details.Locked = False
    Me.Resolution.Locked = False
    Me.Verification_Details.Locked = False
    Me.RequestClose.Locked = False


Case 2
    'Makes items accessable for "Raising Departments"
    Me.Customer_Feedback.Locked = False
    Me.Category.Locked = False
    Me.Investigation_Area.Locked = False
    Me.Investigation_Lead.Locked = False
    Me.Concern_Details.Locked = False
    Me.Feature_Affected.Locked = False
    Me.Fault_Type.Locked = False
    Me.Part_Number.Locked = False
    Me.Shop_Order_Number.Locked = False
    Me.Qty_Defective.Locked = False
    Me.Unit_of_Measure.Locked = False
    Me.Closed.Locked = False

Case 3
    'Makes items accessable for "Admins"
    Me.Contain_Details.Locked = False
    Me.Date_of_Manufacture.Locked = False
    Me.Cause_Details.Locked = False
    Me.Where_Manufactured.Locked = False
    Me.Where_Escaped.Locked = False
    Me.Root_Cause.Locked = False
    Me.Corrective_Details.Locked = False
    Me.Resolution.Locked = False
    Me.Verification_Details.Locked = False
    Me.Customer_Feedback.Locked = False
    Me.Category.Locked = False
    Me.Investigation_Area.Locked = False
    Me.Investigation_Lead.Locked = False
    Me.Concern_Details.Locked = False
    Me.Feature_Affected.Locked = False
    Me.Fault_Type.Locked = False
    Me.Part_Number.Locked = False
    Me.Shop_Order_Number.Locked = False
    Me.Qty_Defective.Locked = False
    Me.Unit_of_Measure.Locked = False
    Me.Closed.Locked = False
    Me.RequestClose.Locked = False

End Select
End Sub
 

randallst

Registered User.
Local time
Today, 18:05
Joined
Jan 28, 2015
Messages
64
Code:
Private Sub Form_Current()
On Error Resume Next

DoCmd.ShowToolbar "Ribbon", acToolbarNo
Application.CommandBars("Ribbon").Visible = False

Dim Flag As Integer
Flag = 0

'Display AD Name, Department and Permission Status
Me.User = Environ("Username")
Me.Permission = DLookup("Permissions", "Tbl_Users", "Alias=" & "User")
Me.UserDepartment = DLookup("Department", "Tbl_Users", "Alias=" & "User")

'Hide fields
Me.New_Incident_Button.Visible = True

'Checking Permissions
If Forms![Frm_Internal]![UserDepartment] = Me.Investigation_Department.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 1
If Forms![Frm_Internal]![UserDepartment] = Me.RaisedDepartment.Value And Forms![Frm_Internal]![Closed] = False Then Flag = 2
If Forms![Frm_Internal]![Permission] = "Admin" And Forms![Frm_Internal]![Closed] = False Then Flag = 3

'Lock all first
'Makes items accessable for "Admins"
Me.Contain_Details.Locked = True
Me.Date_of_Manufacture.Locked = True
Me.Cause_Details.Locked = True
Me.Where_Manufactured.Locked = True
Me.Where_Escaped.Locked = True
Me.Root_Cause.Locked = True
Me.Corrective_Details.Locked = True
Me.Resolution.Locked = True
Me.Verification_Details.Locked = True
Me.Customer_Feedback.Locked = True
Me.Category.Locked = True
Me.Investigation_Area.Locked = True
Me.Investigation_Lead.Locked = True
Me.Concern_Details.Locked = True
Me.Feature_Affected.Locked = True
Me.Fault_Type.Locked = True
Me.Part_Number.Locked = True
Me.Shop_Order_Number.Locked = True
Me.Qty_Defective.Locked = True
Me.Unit_of_Measure.Locked = True
Me.Closed.Locked = True
Me.RequestClose.Locked = True

Select Case Flag
Case 1
    'Makes items accessable for "Investigating Departments"
    Me.Contain_Details.Locked = False
    Me.Date_of_Manufacture.Locked = False
    Me.Cause_Details.Locked = False
    Me.Where_Manufactured.Locked = False
    Me.Where_Escaped.Locked = False
    Me.Root_Cause.Locked = False
    Me.Corrective_Details.Locked = False
    Me.Resolution.Locked = False
    Me.Verification_Details.Locked = False
    Me.RequestClose.Locked = False


Case 2
    'Makes items accessable for "Raising Departments"
    Me.Customer_Feedback.Locked = False
    Me.Category.Locked = False
    Me.Investigation_Area.Locked = False
    Me.Investigation_Lead.Locked = False
    Me.Concern_Details.Locked = False
    Me.Feature_Affected.Locked = False
    Me.Fault_Type.Locked = False
    Me.Part_Number.Locked = False
    Me.Shop_Order_Number.Locked = False
    Me.Qty_Defective.Locked = False
    Me.Unit_of_Measure.Locked = False
    Me.Closed.Locked = False

Case 3
    'Makes items accessable for "Admins"
    Me.Contain_Details.Locked = False
    Me.Date_of_Manufacture.Locked = False
    Me.Cause_Details.Locked = False
    Me.Where_Manufactured.Locked = False
    Me.Where_Escaped.Locked = False
    Me.Root_Cause.Locked = False
    Me.Corrective_Details.Locked = False
    Me.Resolution.Locked = False
    Me.Verification_Details.Locked = False
    Me.Customer_Feedback.Locked = False
    Me.Category.Locked = False
    Me.Investigation_Area.Locked = False
    Me.Investigation_Lead.Locked = False
    Me.Concern_Details.Locked = False
    Me.Feature_Affected.Locked = False
    Me.Fault_Type.Locked = False
    Me.Part_Number.Locked = False
    Me.Shop_Order_Number.Locked = False
    Me.Qty_Defective.Locked = False
    Me.Unit_of_Measure.Locked = False
    Me.Closed.Locked = False
    Me.RequestClose.Locked = False

End Select
End Sub

You sir are a star! I have never used Case before though, so a new learning there. I really appreciate that and it will allow me to sleep tonight now! :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 28, 2001
Messages
18,742
Just a point of curiosity... This is being run in a Form_Current routine, and you have another form named explicitly. What, if any, is the structural relationship between those forms? What I mean is parent/child, or two forms that happen to be open at the time, or something else.
 

Users who are viewing this thread

Top Bottom