Solved

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

Posted on 2013-11-01
11
854 Views
Last Modified: 2013-11-05
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
Comment
Question by:BFanguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39617903
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
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 39618613
can you post your access file with this form. the expectation is confusing
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39619148
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:BFanguy
ID: 39619393
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39619405
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
 

Author Comment

by:BFanguy
ID: 39621825
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
 

Author Comment

by:BFanguy
ID: 39621873
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
 

Author Comment

by:BFanguy
ID: 39621893
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39621980
<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
 

Author Comment

by:BFanguy
ID: 39621987
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
 

Author Closing Comment

by:BFanguy
ID: 39624322
I gave you guys the points, thanks for the help.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question