Re: Want to enable fields after combox selection

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Klatuu,

I pasted only the HideFormText code in a regular Module (not class
Module) and then called it from the OnCurrent on the form using a
Macro.

The Action in the Macro is 'RunCode' and the Action Argument has a
'Function Name' called HideFormText («frm», «blnShow»,
«avarExceptionList»).

When I open the form, I get an error
"The object doesn't contain the Automation object '<<frm>>." You tried
to run a Visual Basic procedure to set a property or method for an
object. However, the component doesn't make the property or method
availble for Automation operations."

I also tried the HideFormText without the double arrows "<<>>" (i.e.
(frm, blnShow, avarExceptionList) for the 'Function Name' and got the
same error.

Thoughts?

Sargum

Klatuu wrote:
I don't know how you are calling it. I might suggest the Current event of
your form. It will not affect any subforms. You will have to call it form
each form.

Here is an example of hiding the text:
HideFormText(Me, False)


"sargum@xxxxxxxxx" wrote:

Hi Klatuu,

Thank you for your help thus far. This is more than I hoped for and
all very useful. I pasted the following (all three code excerpts) into
a Module but am having trouble calling it from my Form. When I open my
Form, I see no changes. I even tried just havign the 'Hide Form Text'
code to see if that worked by itself alone but no luck. Any ideas? Is
there something special I need to do to "call" the module from my Form
which has many associated subforms?

Public Function LockBoundControls(ByVal frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form
any its subforms.
'Arguments frm = the form to be locked
' bLock = True to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to
lock(variant array of strings).
'Usage: Call LockBoundControls(Me. True)


Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean


'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock


For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup,
acCheckBox, acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Not
ctl.ControlSource Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If


Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0 Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If


Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
acPage, acPageBreak, acImage, acObjectFrame
'Do nothing


Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled " & Now()
End Select
Next


'Set the visual indicators on the form.
On Error Resume Next
frm.cmdLock.Caption = IIf(bLock, "Un&lock", "&Lock")
frm!rctLock.Visible = bLock


Exit_Handler:
Set ctl = Nothing
Exit Function


Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Function


Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function


'----------------------------------------------------------------------------------------
' Procedure : HideFormText
' DateTime : 12/21/2006 10:53
' Author : Klatuu
' Purpose : Hide Text on a form
' Notes : Always call this function the first time in a form with
blnShow = False
' : Otherwise, the text may not reappear.
' : Based on code by Allen Browne
'----------------------------------------------------------------------------------------
'
Public Function HideFormText(ByVal frm As Form, blnShow As Boolean,
ParamArray avarExceptionList())
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean


On Error GoTo HideFormText_Error


For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") And
HasProperty(ctl, "ForeColor") Then
If blnShow Then
ctl.ForeColor = ctl.Tag
Else
ctl.Tag = ctl.ForeColor
ctl.ForeColor = ctl.BackColor
End If
End If
End If


Case acCheckBox, acLabel, acLine, acRectangle, acCommandButton,
acTabCtl, acPage, _
acPageBreak, acImage, acObjectFrame
'Do nothing


Case Else
'Includes acBoundObjectFrame, acCustomControl
'Do Nothing
End Select
Next


HideFormText_Exit:


On Error Resume Next
Exit Function


HideFormText_Error:


MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure HideFormText of Module modFormOperations"
GoTo HideFormText_Exit


End Function



Thx.
Sargum


Klatuu wrote:
Yesterday I said I might try doing the hide the text part of your request.
Well, here it is. The only problem is with Check Boxes. They don't have
forecolor or backcolor properties, so making the text and back color the same
wont work. I thought about Unchecking and rechecking, but that is changing
data in the current record which may not be desireable.

BTW, the code I sent yesterday was originally written by Allen Browne.. If
you paste all 3 of the functions (2 from yesterday, 1 today) into a standard
module, you can call it from any form.

'---------------------------------------------------------------------------------------
' Procedure : HideFormText
' DateTime : 12/21/2006 10:53
' Author : Klatuu
' Purpose : Hide Text on a form
' Notes : Always call this function the first time in a form with
blnShow = False
' : Otherwise, the text may not reappear.
' : Based on code by Allen Browne
'---------------------------------------------------------------------------------------
'
Public Function HideFormText(ByVal frm As Form, blnShow As Boolean,
ParamArray avarExceptionList())
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean

On Error GoTo HideFormText_Error

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup,
acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") And HasProperty(ctl,
"ForeColor") Then
If blnShow Then
ctl.ForeColor = ctl.Tag
Else
ctl.Tag = ctl.ForeColor
ctl.ForeColor = ctl.BackColor
End If
End If
End If

Case acCheckBox, acLabel, acLine, acRectangle, acCommandButton,
acTabCtl, acPage, _
acPageBreak, acImage, acObjectFrame
'Do nothing

Case Else
'Includes acBoundObjectFrame, acCustomControl
'Do Nothing
End Select
Next

HideFormText_Exit:

On Error Resume Next
Exit Function

HideFormText_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure HideFormText of Module modFormOperations"
GoTo HideFormText_Exit

End Function


"sargum@xxxxxxxxx" wrote:

Hello,

I will try this and get back to you. I have a feeling I will have
questions so please stay with...

Thx.

Sargum

NthDegree via AccessMonster.com wrote:
You could do something like this:
The Case statement will specify the various types of controls found on the
form that could contain data.
The control "cboSelectionBox" is the combo box that you want to control the
other fields.
This statement /If Me.Controls(ctl.Name).Disabled then Me.Controls(ctl.Name)
= ""/ could be used to clear the field if it was in a disabled state.


.

Quantcast