By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

Help with SubRoutines please..

P: 6
Hiya guys, im kinda new to posting here even though ive been lurking for awhile..reading up on topics that had helped me in building a database for my school.

But now i need further help.
To tell you guys frankly i suck big time on VB and my coding is in such a dire mess. Im looking for some help in optimizing my code below...perhaps a subroutine or two...i tried to do it on my own, but ive messed up still. Heh.

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkSecOne_AfterUpdate()
  2.     If Me.chkSecOne = True Then
  3.         Me.txtSec11.Enabled = True
  4.         Me.txtSec12.Enabled = True
  5.         Me.txtSec13.Enabled = True
  6.         Me.txtSec14.Enabled = True
  7.         Me.txtSec15.Enabled = True
  8.         Me.txtSec1A.Enabled = True
  9.         Me.txtSec1B.Enabled = True
  10.         Me.txtSec1C.Enabled = True
  11.     Else
  12.         Me.txtSec11.Enabled = False
  13.         Me.txtSec12.Enabled = False
  14.         Me.txtSec13.Enabled = False
  15.         Me.txtSec14.Enabled = False
  16.         Me.txtSec15.Enabled = False
  17.         Me.txtSec1A.Enabled = False
  18.         Me.txtSec1B.Enabled = False
  19.         Me.txtSec1C.Enabled = False
  20.     End If
  21. End Sub
  22. Private Sub chkSecTwo_AfterUpdate()
  23.     If Me.chkSecTwo = True Then
  24.         Me.txtSec21.Enabled = True
  25.         Me.txtSec22.Enabled = True
  26.         Me.txtSec23.Enabled = True
  27.         Me.txtSec24.Enabled = True
  28.         Me.txtSec25.Enabled = True
  29.         Me.txtSec2A.Enabled = True
  30.         Me.txtSec2B.Enabled = True
  31.         Me.txtSec2C.Enabled = True
  32.     Else
  33.         Me.txtSec21.Enabled = False
  34.         Me.txtSec22.Enabled = False
  35.         Me.txtSec23.Enabled = False
  36.         Me.txtSec24.Enabled = False
  37.         Me.txtSec25.Enabled = False
  38.         Me.txtSec2A.Enabled = False
  39.         Me.txtSec2B.Enabled = False
  40.         Me.txtSec2C.Enabled = False
  41.     End If
  42. End Sub
  43. Private Sub chkSecThree_AfterUpdate()
  44.     If Me.chkSecThree = True Then
  45.         Me.txtSec31.Enabled = True
  46.         Me.txtSec32.Enabled = True
  47.         Me.txtSec33.Enabled = True
  48.         Me.txtSec34.Enabled = True
  49.         Me.txtSec35.Enabled = True
  50.         Me.txtSec3A.Enabled = True
  51.         Me.txtSec3B.Enabled = True
  52.         Me.txtSec3C.Enabled = True
  53.     Else
  54.         Me.txtSec31.Enabled = False
  55.         Me.txtSec32.Enabled = False
  56.         Me.txtSec33.Enabled = False
  57.         Me.txtSec34.Enabled = False
  58.         Me.txtSec35.Enabled = False
  59.         Me.txtSec3A.Enabled = False
  60.         Me.txtSec3B.Enabled = False
  61.         Me.txtSec3C.Enabled = False
  62.     End If
  63. End Sub
  64. Private Sub chkSecFour_AfterUpdate()
  65.     If Me.chkSecFour = True Then
  66.         Me.txtSec41.Enabled = True
  67.         Me.txtSec42.Enabled = True
  68.         Me.txtSec43.Enabled = True
  69.         Me.txtSec44.Enabled = True
  70.         Me.txtSec45.Enabled = True
  71.         Me.txtSec4A.Enabled = True
  72.         Me.txtSec4B.Enabled = True
  73.         Me.txtSec4C.Enabled = True
  74.     Else
  75.         Me.txtSec41.Enabled = False
  76.         Me.txtSec42.Enabled = False
  77.         Me.txtSec43.Enabled = False
  78.         Me.txtSec44.Enabled = False
  79.         Me.txtSec45.Enabled = False
  80.         Me.txtSec4A.Enabled = False
  81.         Me.txtSec4B.Enabled = False
  82.         Me.txtSec4C.Enabled = False
  83.     End If
  84. End Sub
  85.  
Im basically trying to disable the controls (txtSec11, txtSec12, etc..) depending if the user clicks on the checkbox (chkSecOne, chkSecTwo, etc..). This works fine...except that as you guys can see...the code is pretty damn long and repeatable.

So i hope u folks out there can help me with an example of a subroutine or something.
Thankss!!!
Dec 26 '06 #1
Share this Question
Share on Google+
13 Replies


PEB
Expert 100+
P: 1,418
PEB
U can do sth like this:

Me.txtSec21.Enabled = Me.chkSecTwo
Me.txtSec22.Enabled = Me.chkSecTwo
Me.txtSec23.Enabled = Me.chkSecTwo
Me.txtSec24.Enabled = Me.chkSecTwo
Me.txtSec25.Enabled = Me.chkSecTwo
Me.txtSec2A.Enabled = Me.chkSecTwo
Me.txtSec2B.Enabled = Me.chkSecTwo
Me.txtSec2C.Enabled = Me.chkSecTwo

I've got your second section... Instaed writting if, directly put the value to the respective control!

If not for each control u need a separate line... At least one!

If u don't like this... u need to but the data for ur controls in a database and than cycle the database setting the respective values! But this is long and this doesn't matter the efforts!

Marry Cristmas!

Hiya guys, im kinda new to posting here even though ive been lurking for awhile..reading up on topics that had helped me in building a database for my school.

But now i need further help.
To tell you guys frankly i suck big time on VB and my coding is in such a dire mess. Im looking for some help in optimizing my code below...perhaps a subroutine or two...i tried to do it on my own, but ive messed up still. Heh.

Expand|Select|Wrap|Line Numbers
  1. Private Sub chkSecOne_AfterUpdate()
  2.     If Me.chkSecOne = True Then
  3.         Me.txtSec11.Enabled = True
  4.         Me.txtSec12.Enabled = True
  5.         Me.txtSec13.Enabled = True
  6.         Me.txtSec14.Enabled = True
  7.         Me.txtSec15.Enabled = True
  8.         Me.txtSec1A.Enabled = True
  9.         Me.txtSec1B.Enabled = True
  10.         Me.txtSec1C.Enabled = True
  11.     Else
  12.         Me.txtSec11.Enabled = False
  13.         Me.txtSec12.Enabled = False
  14.         Me.txtSec13.Enabled = False
  15.         Me.txtSec14.Enabled = False
  16.         Me.txtSec15.Enabled = False
  17.         Me.txtSec1A.Enabled = False
  18.         Me.txtSec1B.Enabled = False
  19.         Me.txtSec1C.Enabled = False
  20.     End If
  21. End Sub
  22. Private Sub chkSecTwo_AfterUpdate()
  23.     If Me.chkSecTwo = True Then
  24.         Me.txtSec21.Enabled = True
  25.         Me.txtSec22.Enabled = True
  26.         Me.txtSec23.Enabled = True
  27.         Me.txtSec24.Enabled = True
  28.         Me.txtSec25.Enabled = True
  29.         Me.txtSec2A.Enabled = True
  30.         Me.txtSec2B.Enabled = True
  31.         Me.txtSec2C.Enabled = True
  32.     Else
  33.         Me.txtSec21.Enabled = False
  34.         Me.txtSec22.Enabled = False
  35.         Me.txtSec23.Enabled = False
  36.         Me.txtSec24.Enabled = False
  37.         Me.txtSec25.Enabled = False
  38.         Me.txtSec2A.Enabled = False
  39.         Me.txtSec2B.Enabled = False
  40.         Me.txtSec2C.Enabled = False
  41.     End If
  42. End Sub
  43. Private Sub chkSecThree_AfterUpdate()
  44.     If Me.chkSecThree = True Then
  45.         Me.txtSec31.Enabled = True
  46.         Me.txtSec32.Enabled = True
  47.         Me.txtSec33.Enabled = True
  48.         Me.txtSec34.Enabled = True
  49.         Me.txtSec35.Enabled = True
  50.         Me.txtSec3A.Enabled = True
  51.         Me.txtSec3B.Enabled = True
  52.         Me.txtSec3C.Enabled = True
  53.     Else
  54.         Me.txtSec31.Enabled = False
  55.         Me.txtSec32.Enabled = False
  56.         Me.txtSec33.Enabled = False
  57.         Me.txtSec34.Enabled = False
  58.         Me.txtSec35.Enabled = False
  59.         Me.txtSec3A.Enabled = False
  60.         Me.txtSec3B.Enabled = False
  61.         Me.txtSec3C.Enabled = False
  62.     End If
  63. End Sub
  64. Private Sub chkSecFour_AfterUpdate()
  65.     If Me.chkSecFour = True Then
  66.         Me.txtSec41.Enabled = True
  67.         Me.txtSec42.Enabled = True
  68.         Me.txtSec43.Enabled = True
  69.         Me.txtSec44.Enabled = True
  70.         Me.txtSec45.Enabled = True
  71.         Me.txtSec4A.Enabled = True
  72.         Me.txtSec4B.Enabled = True
  73.         Me.txtSec4C.Enabled = True
  74.     Else
  75.         Me.txtSec41.Enabled = False
  76.         Me.txtSec42.Enabled = False
  77.         Me.txtSec43.Enabled = False
  78.         Me.txtSec44.Enabled = False
  79.         Me.txtSec45.Enabled = False
  80.         Me.txtSec4A.Enabled = False
  81.         Me.txtSec4B.Enabled = False
  82.         Me.txtSec4C.Enabled = False
  83.     End If
  84. End Sub
  85.  
Im basically trying to disable the controls (txtSec11, txtSec12, etc..) depending if the user clicks on the checkbox (chkSecOne, chkSecTwo, etc..). This works fine...except that as you guys can see...the code is pretty damn long and repeatable.

So i hope u folks out there can help me with an example of a subroutine or something.
Thankss!!!
Dec 26 '06 #2

Expert 5K+
P: 8,434
I've only done this sort of thing in VB6, so it may be different. But perhaps you could put each group of controls in an option group, or form, or whatever it's called (I forget). Then just enable/disable that container rather than each of the controls.
Dec 27 '06 #3

P: 6
okie...thanks for the promt replies guys.
i will try it out definitely.

cheers!
Dec 28 '06 #4

Expert 5K+
P: 8,434
I've only done this sort of thing in VB6, so it may be different. But perhaps you could put each group of controls in an option group, or form, or whatever it's called (I forget). Then just enable/disable that container rather than each of the controls.
Sorry, I mean to say frame there, not form.
Dec 29 '06 #5

100+
P: 1,646
This little routine might be fun

Expand|Select|Wrap|Line Numbers
  1.  
  2.    Dim ctTmp As Control
  3.    Dim iText As Integer
  4.  
  5.    For Each ctTmp In Form1.Controls
  6.         If ctTmp.ControlType = acTextBox Then
  7.             iText = CInt(Left(Right(ctTmp.Name, 2), 1))
  8.             Select Case iText
  9.                 Case 1
  10.                     ctTmp.Enabled = chkSecOne
  11.  
  12.                 Case 2
  13.                     ctTmp.Enabled = chkSecTwo
  14.  
  15.                 Case 3
  16.                     ctTmp.Enabled = chkSecThree
  17.  
  18.                 Case 4
  19.                     ctTmp.Enabled = chkSecFour
  20.  
  21.                 Case Else
  22.                     'that is enough for me
  23.  
  24.             End Select
  25.         End If
  26.     Next 'ctTmp
  27.  
Put it in it's own sub and call it from any event you like. It will only work if there are no other textboxes on your form. You could modify it to exclude any other textboxes by name. Over to you
Dec 29 '06 #6

NeoPa
Expert Mod 15k+
P: 31,494
This isn't the most compression of code you can do (See Will's post for that) but it is probably as close as you'll get without getting quite complicated :
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkSecOne_AfterUpdate()
  2.     With Me.chkSecOne
  3.         Me.txtSec11.Enabled = .Value
  4.         Me.txtSec12.Enabled = .Value
  5.         Me.txtSec13.Enabled = .Value
  6.         Me.txtSec14.Enabled = .Value
  7.         Me.txtSec15.Enabled = .Value
  8.         Me.txtSec1A.Enabled = .Value
  9.         Me.txtSec1B.Enabled = .Value
  10.         Me.txtSec1C.Enabled = .Value
  11.     End With
  12. End Sub
  13. Private Sub chkSecTwo_AfterUpdate()
  14.     With Me.chkSecTwo
  15.         Me.txtSec21.Enabled = .Value
  16.         Me.txtSec22.Enabled = .Value
  17.         Me.txtSec23.Enabled = .Value
  18.         Me.txtSec24.Enabled = .Value
  19.         Me.txtSec25.Enabled = .Value
  20.         Me.txtSec2A.Enabled = .Value
  21.         Me.txtSec2B.Enabled = .Value
  22.         Me.txtSec2C.Enabled = .Value
  23.     End With
  24. End Sub
  25. Private Sub chkSecThree_AfterUpdate()
  26.     With Me.chkSecThree
  27.         Me.txtSec31.Enabled = .Value
  28.         Me.txtSec32.Enabled = .Value
  29.         Me.txtSec33.Enabled = .Value
  30.         Me.txtSec34.Enabled = .Value
  31.         Me.txtSec35.Enabled = .Value
  32.         Me.txtSec3A.Enabled = .Value
  33.         Me.txtSec3B.Enabled = .Value
  34.         Me.txtSec3C.Enabled = .Value
  35.     End With
  36. End Sub
  37. Private Sub chkSecFour_AfterUpdate()
  38.     With Me.chkSecFour
  39.         Me.txtSec41.Enabled = .Value
  40.         Me.txtSec42.Enabled = .Value
  41.         Me.txtSec43.Enabled = .Value
  42.         Me.txtSec44.Enabled = .Value
  43.         Me.txtSec45.Enabled = .Value
  44.         Me.txtSec4A.Enabled = .Value
  45.         Me.txtSec4B.Enabled = .Value
  46.         Me.txtSec4C.Enabled = .Value
  47.     End With
  48. End Sub
Dec 30 '06 #7

Expert 5K+
P: 8,434
...
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkSecOne_AfterUpdate()
  2.     With Me.chkSecOne
  3.         Me.txtSec11.Enabled = .Value
  4.         Me.txtSec12.Enabled = .Value
  5. ...
I really wish Access supported control arrays, as they would make things like this one heck of a lot simpler.

Willakawill pointed out a document at Microsoft describing a way of simulating them, but I'm not very happy with it. So I was wondering, couldn't you create an array at form level and set the elements to "point to" (I forget the proper term) the ones on the form?

Here's a little trial I knocked up, which appeared to work. Unfortunately, trying to test it (in the Click event) I ran into this stupid restriction in Access where you can't seem to do anything with a control unless it has the focus :(. But presumably this would apply no matter how you reference the controls, so is not really relevant here.

If anyone would like to try this, just create a new form, add a couple of unbound text boxes using the default names Text0, Text1 etc. Then get to the code editor for the form and put in this code (also, I think in Access unlike "real" VB, you have to explicitly tell the form to look for these events by attaching [Event Procedure] to the event). This will set up an array and link the elements to them.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private MyTextBox() As TextBox
  5. Private MyTextBoxCount As Long
  6.  
  7. Private Sub Form_Load()
  8.   Dim I As Long
  9.   Dim ctl As Control
  10.   For Each ctl In Me.Controls
  11.     If TypeOf ctl Is TextBox Then
  12.       If Left$(ctl.Name, 4) = "Text" Then
  13.         MyTextBoxCount = MyTextBoxCount + 1
  14.         ReDim Preserve MyTextBox(1 To MyTextBoxCount)
  15.         Set MyTextBox(MyTextBoxCount) = ctl
  16.       End If
  17.     End If
  18.   Next
  19.   Set ctl = Nothing
  20. End Sub
  21.  
  22. Private Sub Text0_Change()
  23.   Dim I As Long
  24.   Debug.Print MyTextBoxCount
  25.   For I = 1 To MyTextBoxCount
  26.     ' This line produces an error due to a silly
  27.     ' limitation of VBA...
  28.     Debug.Print , MyTextBox(I).Text
  29.   Next
  30. End Sub
Dec 31 '06 #8

Expert 5K+
P: 8,434
Expand|Select|Wrap|Line Numbers
  1.         Me.txtSec11.Enabled = .Value
  2.         Me.txtSec12.Enabled = .Value
  3.         Me.txtSec13.Enabled = .Value
That's another thing. How come this works, when Access won't let you change properties of a control that doesn't have the focus? What am I missing here? The rules for this "must have focus" limitation must be documented somewhere - I'd appreciate it if someone could point me toward it.

(Note, in my prior post, the error happened when accessing the second element of the array - the first worked alright, because Text0 did have the focus. So the basic idea seems sound.)
Dec 31 '06 #9

100+
P: 1,646
That's another thing. How come this works, when Access won't let you change properties of a control that doesn't have the focus? What am I missing here? The rules for this "must have focus" limitation must be documented somewhere - I'd appreciate it if someone could point me toward it.

(Note, in my prior post, the error happened when accessing the second element of the array - the first worked alright, because Text0 did have the focus. So the basic idea seems sound.)
The answer to all these problems is to run access as the back end and vb as the front end. I can't stand bound controls so I was never seduced by the 'wizardry' of access. I have found everything so much easier unbound and in code. This means I can port my code design to several languages
Dec 31 '06 #10

Expert 5K+
P: 8,434
The answer to all these problems is to run access as the back end and vb as the front end. I can't stand bound controls so I was never seduced by the 'wizardry' of access. I have found everything so much easier unbound and in code. This means I can port my code design to several languages
The thing is, where I work, everyone has Access (at least runtime) and almost nobody has VB. If I throw together a quick and simple utility using an Access database, it's often simpler to just do it in Access.

For anything serious, I'd also prefer VB as the front-end.

(All the same, I'm fairly proud of having invented another way of simulating control arrays. And in my opinion, a better method, in some ways. :))
Dec 31 '06 #11

NeoPa
Expert Mod 15k+
P: 31,494
If I were doing it for myself, I'd use your way Killer (or something very similar), but posting for an inexperienced coder I think something a little more straightforward is required.
Dec 31 '06 #12

Expert 5K+
P: 8,434
If I were doing it for myself, I'd use your way Killer (or something very similar), but posting for an inexperienced coder I think something a little more straightforward is required.
Yeah, probably true.

I might have a think about my simulation thing and see what I can do to make it simpler to implement.
Dec 31 '06 #13

P: 6
Hi guys...thanks for all the replies....really had been a great help!
Bless u guys....and have a happy new year!!

Cheers!
Jan 3 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.