Does any one could help me to write a VBA code to control a set of fields in the form when a specific check box in selected.For example if payment is already made by a person then a group of field has to be activated, otherwise remain inactive.
Private Sub Form_Current()
Dim IsActive As Boolean
IsActive = Not IsNull(Me.ControlName)
'Activate/Dezactivate necessary controls
Me.Control1.Enabled = IsActive
Me.Control2.Enabled = IsActive
Me.Control3.Enabled = IsActive
.....................
End Sub
Private Sub ControlName_Change()
Dim IsActive As Boolean
IsActive = (Me.ControlName.Text <> "") 'Maybe there are necessary more tests
'Activate/Dezactivate necessary controls
Me.Control1.Enabled = IsActive
Me.Control2.Enabled = IsActive
Me.Control3.Enabled = IsActive
.....................
End Sub
If the number of Controls to be Enabled/Disabled make coding each one separately an onerous task, you can loop through allControls and Enable/Disableselected ones by marking them using their Tag Property.
Start by setting the Tag Property of the Controls you want to loop thru to some arbitrary word. In this sample we'll use CLoop.
In Design View, select all of the Controls you want to loop thru, then goto Properties – Other and in the Tag Property box type in CLoop, without quotation marks. Then use this code, in the AfterUpdate event of your Checkbox. You'll need to replace CheckBoxName, in the code, with the actual name of your Checkbox:
Code:
Private Sub CheckBoxName_AfterUpdate()
Dim ctl As Control
If Me.CheckBoxName = -1 Then
For Each ctl In Me.Controls
If ctl.Tag = "CLoop" Then
ctl.Enabled = True
End If
Next ctl
Else
For Each ctl In Me.Controls
If ctl.Tag = "CLoop" Then
ctl.Enabled = False
End If
Next ctl
End If
End Sub
Now only the Controls with the tag CLoop will be Enabled/Disabled. To selectively loop thru a different groups of Controls, to do different things, you can assign different Tags to different groups and code accordingly.
To maintain Record-appropriate formatting, you'll also need to have this same code in the Form_Current event.
If the number of Controls to be Enabled/Disabled make coding each one separately an onerous task, you can loop through allControls and Enable/Disableselected ones by marking them using their Tag Property.
Private Sub lockToggle()
Dim isLocked as Boolean
isLocked = (Me.chkLocked)
' isLocked = (Len(Me.myTextBox & vbNullString) = 0)
' or some other Boolean test
Me.myField01.Enabled = isLocked
Me.myField02.Enabled = isLocked
...
Me.myFieldnn.Enabled = isLocked
End Sub
Private Sub Form_Current()
lockToggle
End Sub
Private Sub someControl_Click()
lockToggle
End Sub
Naming conventions are also good for other purposes too:
Read only fields xxxx_ro - If (Right(myControl.Name, 3) = "_ro") Then myControl.Locked = true
I also made the background colour light blue to signify a read only field
Mandatory fields xxxx_mn - Check field in _BeforeUpdate routine, if no entry then exit with a message and cancel the update.
I also made the background colour yellow to signify a mandatory field
The first problem with this is that you have to allow for the user making a mistake, i.e. ticking the Checkbox, then unticking it, so you have to be able to use an If...Then...ElseIf...EndIf construct, against the checkboxname value, and if you're already doing that,
ctl.Enabled = Me.checkboxname
isn't really much of a shortcut.
Secondly, you can't use
ctl.Enabled = Me.checkboxname
in every situation; using it where checkboxname is neither True nor False, i.e. when it is Null, will pop an error, as you cannot set a Property to Null. In my dotage, I find that using one bit of code, to accomplish a given task, in all situations, is preferable to using two or three hacks, depending on the situation, assuming this is possible.
Lastly, when I started using it, a while back, I got tired of people following in my footsteps asking what the $%^&* it meant!
It's a clever shortcut, and in the days of 10 mg hard drives, when we counted every character we used, in code, and had contests to see who could accomplish a task using the fewest keystrokes, it would have been popular. But in today's environments, where a single PC has more memory than all of the computers used to put men on the moon, combined, I prefer for code to be as self-explanatory as possible, even when a few extra characters are necessary.
And, yes, you could put it in a separate sub and call it from each event, but see the previous paragraph. If I were calling it from half-a-dozen events, that would be a different story.
As for using Control names like
myControl001
myControl002
myControl003
most of us advise OPs, all the time, about giving Controls names that reflect their use/meaning. Using this type of thing to save a few keystrokes is just counterproductive, in the long run.
Most of the above, excepting the first paragraph, I think, is just my opinion, of course, and your mileage may vary!
most of us advise OPs, all the time, about giving Controls names that reflect their use/meaning. Using this type of thing to save a few keystrokes is just counterproductive, in the long run.
And there are times where this is appropriate. The main times I use this sort of naming is when there are groups of controls with the same function, for instance if I were creating a form to build an SQL string.
It may be a bit more technical than a lot of people use, but a function like this ...
Code:
Private Function buildWhereClause()
Dim intIndex as Integer, intMax as Integer
Dim strWhere as string
strWhere = ""
For intIndex = 1 to n '(where n is a number)
If Len(strWhere & vbNullString) > 0 Then strWhere = strWhere " AND "
strWhere = strWhere & Me.Controls("Field" & Format(intIndex, "00"))
strWhere = strWhere & " " & Me.Controls("Operator" & Format(intIndex, "00"))
strWhere = strWhere & " '" & Me.Controls("Value" & Format(intIndex, "00")) & "'"
Next intIndex
If Len(strWhere & vbNullString) > 0 Then strWhere = " WHERE " & strWhere
buildWhereClause = strWhere
End Function
