• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 953
  • Last Modified:

Access 2010 looping through controls and disabling / locking / enabling / unlocking

I have this code below on a Forms On Current event called from a select

Select Case Me.Level
Case "DRL1"
   ProtectFields ("01")
Case "DRL2"
   ProtectFields ("02")
end select

Public Sub ProtectFields(ColumnNum As String)
Dim Ctl As Control
For Each Ctl In Forms!Sales_Contract_Review.Controls
' make sure i am looking at the correct textboxes
   If Ctl.Parent.Name = "Sales_Contract_Review" And IsNumeric(Mid(Ctl.Name, 1, 2)) And Mid(Ctl.Name, 3, 1) = "-" And IsNumeric(Mid(Ctl.Name, 4, 2)) Then
' protect all fields except ones with the passed variable in column 4-5
      If Mid(Ctl.Name, 4, 2) <> ColumnNum Then
 '        msgbox "lock:" & Ctl.Name
         Ctl.Enabled = False
         Ctl.Locked = True
      Else
 '        msgbox "unlock: " & Ctl.Name
         If Ctl.Value <> "M" Then
            Ctl.Enabled = True
            Ctl.Locked = False
         End If
      End If
   End If
Next
End Sub

Open in new window


it works as expected, all textboxes are locked except the ones i want.

i add the same code to an on afterupdate of an option box:
Select Case LevelOptions
Case 1
   ProtectFields ("01")
   Me.Ctl04_01_PC.SetFocus
Case 2
   ProtectFields ("02")
   Me.Ctl02_02_Nace.SetFocus
End Select

But this gives me unexpected results.  all fields stay protected AND the labels assocated to the textboxes gets grayed out (disabled) AND it fails on the SetFocus

any ideas?
0
BFanguy
Asked:
BFanguy
  • 6
  • 2
  • 2
  • +1
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
do not disable the controls, just lock them so you can still set the focus to the control.

is there any reason why you want to disable controls?
0
 
Swapnil NirmalCommented:
can you post your access file with this form. the expectation is confusing
0
 
mbizupCommented:
See capricorn1's comment regarding setting focus to controls... The set focus command will not work with a disabled control.  So make sure that the set focus command is taking you to an enabled control.

Also, from your comments in the code, it sounds like you only want this to apply to textboxes.

Try modifying your code like this to ensure that you are ONLY affecting textboxes, and not inadvertently disabling the Option Box that is currently in focus, which will cause the code to error and stop prematurely (added  Ctl.ControlType = acTextBox to the beginning of your IF statement):

Public Sub ProtectFields(ColumnNum As String)
Dim Ctl As Control
For Each Ctl In Forms!Sales_Contract_Review.Controls
' make sure i am looking at the correct textboxes
   If  Ctl.ControlType = acTextBox AND Ctl.Parent.Name = "Sales_Contract_Review" And IsNumeric(Mid(Ctl.Name, 1, 2)) And Mid(Ctl.Name, 3, 1) = "-" And IsNumeric(Mid(Ctl.Name, 4, 2)) Then
' protect all fields except ones with the passed variable in column 4-5
      If Mid(Ctl.Name, 4, 2) <> ColumnNum Then
 '        msgbox "lock:" & Ctl.Name
         Ctl.Enabled = False
         Ctl.Locked = True
      Else
 '        msgbox "unlock: " & Ctl.Name
         If Ctl.Value <> "M" Then
            Ctl.Enabled = True
            Ctl.Locked = False
         End If
      End If
   End If
Next
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BFanguyAuthor Commented:
I am enabling and unlocking before I set focus  the code works fine on current but does not unlock or enable the fields when the on after update of the field fires
0
 
mbizupCommented:
Did you try the modification I suggested?

Also, are you sure the code is actually running?  Try adding a message box at the beginning of the Option Group's after update event procedure, just as a test to verify that the code is running:

Private Sub LevelOptions_AfterUpdate()
          MsgBox "The code reached the After Update Event"

Select Case LevelOptions
Case 1
   ProtectFields ("01")
   Me.Ctl04_01_PC.SetFocus
Case 2

Open in new window


If the message box is not displayed, verify that the words [Event Procedure] are displayed in the box next to After Update in the option group's property sheet.  If they are not, click the ... next to After Update in the property sheet to 'connect' the event procedure to it's code.
0
 
BFanguyAuthor Commented:
thanks, I added your code to make sure only textboxes were selected.
I uncommented my two message boxes and when the form opens ("on current") I can see which ones it disables and which ones it enables,
same for "afterUpdate", it loops through and shows me the fields it is enabling but it does not actually enable the fields, after looping through the fields.

Ok, I just noticed it ONLY works for "On current" for the first record opened on the form (which is filtered for 1 record), if I remove the filter it does the same thing as the afterupdate, it loops through the fields Supposedly enabling some of them and disabling the rest, but they all are disabled.
0
 
BFanguyAuthor Commented:
I uncommented out the Ctl.Enabled  = true and Ctl.Enabled  = false and it only works when the form is opened for the first "OnCurrent" event.
0
 
BFanguyAuthor Commented:
Evidently there is something wrong with my code for "unlocking" fields using the Control.
i.e.  it lockes but wont unlock the textboxes.

do I need to do something else before the Ctl.Locked = False like setfocus???
0
 
Rey Obrero (Capricorn1)Commented:
<do I need to do something else before the Ctl.Locked = False like setfocus??? >

no.. you only need to set focus to other enabled controls when you are disabling a certain control
0
 
BFanguyAuthor Commented:
looks like it is Null values, I changed the code from comparing .value <> "M" to the following:

Public Sub ProtectFields(ColumnNum As String)
Dim Ctl As Control
For Each Ctl In Forms!Sales_Contract_Review.Controls
   If Ctl.ControlType = acTextBox And Ctl.Parent.Name = "Sales_Contract_Review" And IsNumeric(Mid(Ctl.Name, 1, 2)) And Mid(Ctl.Name, 3, 1) = "-" And IsNumeric(Mid(Ctl.Name, 4, 2)) Then
      If Mid(Ctl.Name, 4, 2) <> ColumnNum Then
'        msgbox Ctl.Name
         Ctl.Enabled = False
         Ctl.Locked = True
      Else
'         msgbox "unlock: " & Ctl.Name
         If Ctl.Value = "M" Then
            Ctl.Enabled = False
            Ctl.Locked = True
         Else
           Ctl.Enabled = True
           Ctl.Locked = False
         End If
      End If
   End If
Next
End Sub

Open in new window

0
 
BFanguyAuthor Commented:
I gave you guys the points, thanks for the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now