Solved

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

Posted on 2013-11-01
11
802 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

777 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