Solved

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

Posted on 2013-11-01
11
751 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 119

Expert Comment

by:Rey Obrero
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now