Want to enable fields after combox selection

**[email protected]** · January 4th, 2007, 03:09 PM posted to microsoft.public.access.forms

**Klatuu** · January 4th, 2007, 03:17 PM posted to microsoft.public.access.forms

The OnCurrent property of a form identifies what happens when the Current
Event fires. If you are using code in the form's module, The code in the
Current Event will execute.

Here is the step by step:
(We can ignore putting the code in a module, you have already done that)
Open the form in design view.
Open the Properties Dialog.
Be sure you have the form's properties selected.
Select the Events tab.
Select the OnCurrent event.
Click the small button to the right of the OnCurrent text box.
Select Code builder.
The VBA editor will open with the cursor positioned in the current event sub.
Enter the following code:

HideFormText(Me, False)

--
Dave Hargis, Microsoft Access MVP

" wrote:

Hi,

To clarify, the standard module is not named the same as the function.

I have created the standard modules (called it Module1) in Modules
under Objects in the Database window. I don't understand how to call
it from the form unless I put it in the OnCurrent event box on this
form. Since I am having trouble with this, can someone lay out, step
by step, what I need to do to call Module1 from my form? What is the
current event if it is not the ONCurrent box found in the form's
property??

S

Klatuu wrote:
There should be no macro involved.
A standard module is the correct place to put the code, but be sure the name
of the module is not the same as the function.
Call the function directly from the current event. It needs to be coded in
the form's module, not directly in the OnCurrent event box.
--
Dave Hargis, Microsoft Access MVP

" wrote:

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)

" 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

**[email protected]** · January 4th, 2007, 04:57 PM posted to microsoft.public.access.forms

Hi,

Thank you for your prompt response and your patience! I did as you
advised.

I get teh following error:
Compile error: Syntax Error

and it opens up teh code and highlights (in red) HideFormText(Me,False)
What may be wrong with the syntax?

Sargum

Klatuu wrote:
The OnCurrent property of a form identifies what happens when the Current
Event fires. If you are using code in the form's module, The code in the
Current Event will execute.

Here is the step by step:
(We can ignore putting the code in a module, you have already done that)
Open the form in design view.
Open the Properties Dialog.
Be sure you have the form's properties selected.
Select the Events tab.
Select the OnCurrent event.
Click the small button to the right of the OnCurrent text box.
Select Code builder.
The VBA editor will open with the cursor positioned in the current event sub.
Enter the following code:

HideFormText(Me, False)

--
Dave Hargis, Microsoft Access MVP

" wrote:

Hi,

To clarify, the standard module is not named the same as the function.

I have created the standard modules (called it Module1) in Modules
under Objects in the Database window. I don't understand how to call
it from the form unless I put it in the OnCurrent event box on this
form. Since I am having trouble with this, can someone lay out, step
by step, what I need to do to call Module1 from my form? What is the
current event if it is not the ONCurrent box found in the form's
property??

S

Klatuu wrote:
There should be no macro involved.
A standard module is the correct place to put the code, but be sure the name
of the module is not the same as the function.
Call the function directly from the current event. It needs to be coded in
the form's module, not directly in the OnCurrent event box.
--
Dave Hargis, Microsoft Access MVP

" wrote:

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)

" 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

**Klatuu** · January 4th, 2007, 05:31 PM posted to microsoft.public.access.forms

Try it like this:
Call HideFormText(Me, False)
--
Dave Hargis, Microsoft Access MVP

"Klatuu" wrote:

The OnCurrent property of a form identifies what happens when the Current
Event fires. If you are using code in the form's module, The code in the
Current Event will execute.

Here is the step by step:
(We can ignore putting the code in a module, you have already done that)
Open the form in design view.
Open the Properties Dialog.
Be sure you have the form's properties selected.
Select the Events tab.
Select the OnCurrent event.
Click the small button to the right of the OnCurrent text box.
Select Code builder.
The VBA editor will open with the cursor positioned in the current event sub.
Enter the following code:

HideFormText(Me, False)

--
Dave Hargis, Microsoft Access MVP

" wrote:

Hi,

To clarify, the standard module is not named the same as the function.

I have created the standard modules (called it Module1) in Modules
under Objects in the Database window. I don't understand how to call
it from the form unless I put it in the OnCurrent event box on this
form. Since I am having trouble with this, can someone lay out, step
by step, what I need to do to call Module1 from my form? What is the
current event if it is not the ONCurrent box found in the form's
property??

S

Klatuu wrote:
There should be no macro involved.
A standard module is the correct place to put the code, but be sure the name
of the module is not the same as the function.
Call the function directly from the current event. It needs to be coded in
the form's module, not directly in the OnCurrent event box.
--
Dave Hargis, Microsoft Access MVP

" wrote:

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)

" 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.

'---------------------------------------------------------------------------------------

**[email protected]** · January 4th, 2007, 05:59 PM posted to microsoft.public.access.forms

Yeah, that worked. Thank you! ......

HOWEVER, I now get an error about the code in the standard module:

It highlights HasProperty in blue in the following line of code:
If HasProperty(ctl, "ControlSource") And
HasProperty(ctl, "ForeColor")

and says Compile Error: Sub or Function not defined.

Sargum

Klatuu wrote:
Try it like this:
Call HideFormText(Me, False)
--
Dave Hargis, Microsoft Access MVP

"Klatuu" wrote:

The OnCurrent property of a form identifies what happens when the Current
Event fires. If you are using code in the form's module, The code in the
Current Event will execute.

Here is the step by step:
(We can ignore putting the code in a module, you have already done that)
Open the form in design view.
Open the Properties Dialog.
Be sure you have the form's properties selected.
Select the Events tab.
Select the OnCurrent event.
Click the small button to the right of the OnCurrent text box.
Select Code builder.
The VBA editor will open with the cursor positioned in the current event sub.
Enter the following code:

HideFormText(Me, False)

--
Dave Hargis, Microsoft Access MVP

" wrote:

Hi,

To clarify, the standard module is not named the same as the function.

I have created the standard modules (called it Module1) in Modules
under Objects in the Database window. I don't understand how to call
it from the form unless I put it in the OnCurrent event box on this
form. Since I am having trouble with this, can someone lay out, step
by step, what I need to do to call Module1 from my form? What is the
current event if it is not the ONCurrent box found in the form's
property??

S

Klatuu wrote:
There should be no macro involved.
A standard module is the correct place to put the code, but be sure the name
of the module is not the same as the function.
Call the function directly from the current event. It needs to be coded in
the form's module, not directly in the OnCurrent event box.
--
Dave Hargis, Microsoft Access MVP

" wrote:

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)

" 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.

'---------------------------------------------------------------------------------------

**[email protected]** · January 4th, 2007, 09:10 PM posted to microsoft.public.access.forms

**[email protected]** · January 4th, 2007, 09:27 PM posted to microsoft.public.access.forms

Hi,

As I said, I pasted all three codes into one standard module. I have
two more questions.

(1) I am trying to implement the LockBoundControls once I am in my
form:

Private Sub Form_Current()
Call LockBoundControls(Me, False)
End Sub

but get the following error:
Error 2455 - you entered an expression that has an invalid reference to
the property Form/Report.

(2) To recap, my overall goal:
What I would like to do is have the person open the form, have one
combo box enabled that asks them to choose a Wafer Name. Once they
choose it, all the controls that were previously disabled are enabled.
(And yes, by using the HideFormText, there will be no values showing in
these controls). But I am thinking that once I get your
LockBoundControls code to work, the combobox which has a list of values
will not be enabled in order to allow for the person to select the
Wafer Name in the first place. How can one enable only this select
combobox and still apply your code to the rest of the controls?

Thx.
S

wrote:
Hi again,

Nevermind the latest question...I see, I had to paste the HasProperty
Function and then it worked. Now that I know how to call Modules from
a form, I need to work out the little quirks. I may have more
questions so stay tuned. Thanks for your efforts!

S

wrote:
Yeah, that worked. Thank you! ......

HOWEVER, I now get an error about the code in the standard module:

It highlights HasProperty in blue in the following line of code:
If HasProperty(ctl, "ControlSource") And
HasProperty(ctl, "ForeColor")

and says Compile Error: Sub or Function not defined.

Sargum

Klatuu wrote:
Try it like this:
Call HideFormText(Me, False)
--
Dave Hargis, Microsoft Access MVP

"Klatuu" wrote:

The OnCurrent property of a form identifies what happens when the Current
Event fires. If you are using code in the form's module, The code in the
Current Event will execute.

Here is the step by step:
(We can ignore putting the code in a module, you have already done that)
Open the form in design view.
Open the Properties Dialog.
Be sure you have the form's properties selected.
Select the Events tab.
Select the OnCurrent event.
Click the small button to the right of the OnCurrent text box.
Select Code builder.
The VBA editor will open with the cursor positioned in the current event sub.
Enter the following code:

HideFormText(Me, False)

--
Dave Hargis, Microsoft Access MVP

" wrote:

Hi,

To clarify, the standard module is not named the same as the function.

I have created the standard modules (called it Module1) in Modules
under Objects in the Database window. I don't understand how to call
it from the form unless I put it in the OnCurrent event box on this
form. Since I am having trouble with this, can someone lay out, step
by step, what I need to do to call Module1 from my form? What is the
current event if it is not the ONCurrent box found in the form's
property??

S

Klatuu wrote:
There should be no macro involved.
A standard module is the correct place to put the code, but be sure the name
of the module is not the same as the function.
Call the function directly from the current event. It needs to be coded in
the form's module, not directly in the OnCurrent event box.
--
Dave Hargis, Microsoft Access MVP

" wrote:

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)

" 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.

'---------------------------------------------------------------------------------------

**[email protected]** · January 5th, 2007, 02:15 PM posted to microsoft.public.access.forms

Hello,

I re-read through your notes...

I don't understand how to incorporate the exclusion of my combo in the
code you provided.

"Here is a function that either locks or unlocks the controls on a
form. You
pass the form as a form object, True to Lock or False to Unlock, and
the name
of any controls you want to exclude (Like your combo)."

Also, there is the issue of the error when I run the code as is
Private Sub Form_Current()
Call LockBoundControls(Me, True)
End Sub
:
Error 2455 - you entered an expression that has an invalid reference to

the property Form/Report.

I will keep workin' at it.
Thx.
Sargum

Klatuu wrote:
In one quick swoop!
Here is a function that either locks or unlocks the controls on a form. You
pass the form as a form object, True to Lock or False to Unlock, and the name
of any controls you want to exclude (Like your combo).

I don't know where I got this code, I didn't write it.

Put the code below in a standard module so you can use it from any form.
There are two functions. The first calls the second.

As to making the controls that contain values appear to be blank, it can be
done. As you know, Access always show the data for some record. The only
time you see the controls blank is if you are on a new record. But, being the
sneaky devil I am, here is a way to do it. I haven't written the code, but
your post made me think about it.

Write a function (or you could add it to this one) that loops through the
controls and saves the Forecolor for each control so you know what color the
text originally was and sets the Forecolor to the same color as the
Backcolor. Now, it appears to be blank

Then to turn the text back on, you would have to go through the controls and
set the forecolor to what it used to be.

and one more thing

TERMINOLOGY ALERT!!!!

"Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL the
fields in a particular form."

Regardless of what a lot of Microsoft documentation says, in true database
terminology, there are no field on a form. The form's recordset has fields,
but the form itself only has controls. A text box is a control, a combo box
is a control, etc.

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

" wrote:

Hi,

Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL
the fields in a particular form. (It would be bonus if anyone could
tell me how to get rid of data showing up in disabled fields. I want
disabled fields to show no data until they "come to life" after a value
is chosen from the combobox)

I have played around with setting each individ field to visible=false
and then visible = true with "After Update" in combo box and this works
fine but I want to know if there is a quick way to set it such that in
one quick swoop, you can enable "disabled" fields after a combobox
selection...there has to be some code out there...like
Forms![Form_Name].fields.all.enabled=true...I tried this, didn't work.

I want to avoid having to code for each individ. field. every time one
has to be added at a later date.

Thx.
S

**[email protected]** · January 5th, 2007, 02:50 PM posted to microsoft.public.access.forms

Hi,

Okay...sorry for all the emails...I just figured out that the error
appears when there is more than one subform...If I just have one
subform, the locking function works. However, if my subform has a
subform, I get the error 2455. Well, truth be told, I have to have all
my subforms so I could not just have one parent form and one subform.
Any ideas?

Also, I would like to exclude Combo31 in the parent form and Combo45 in
a subform. Hopefully, this will be my last commentary for the day.

S

wrote:
Hello,

I re-read through your notes...

I don't understand how to incorporate the exclusion of my combo in the
code you provided.

"Here is a function that either locks or unlocks the controls on a
form. You
pass the form as a form object, True to Lock or False to Unlock, and
the name
of any controls you want to exclude (Like your combo)."

Also, there is the issue of the error when I run the code as is
Private Sub Form_Current()
Call LockBoundControls(Me, True)
End Sub
:
Error 2455 - you entered an expression that has an invalid reference to

the property Form/Report.

I will keep workin' at it.
Thx.
Sargum

Klatuu wrote:
In one quick swoop!
Here is a function that either locks or unlocks the controls on a form. You
pass the form as a form object, True to Lock or False to Unlock, and the name
of any controls you want to exclude (Like your combo).

I don't know where I got this code, I didn't write it.

Put the code below in a standard module so you can use it from any form.
There are two functions. The first calls the second.

As to making the controls that contain values appear to be blank, it can be
done. As you know, Access always show the data for some record. The only
time you see the controls blank is if you are on a new record. But, being the
sneaky devil I am, here is a way to do it. I haven't written the code, but
your post made me think about it.

Write a function (or you could add it to this one) that loops through the
controls and saves the Forecolor for each control so you know what color the
text originally was and sets the Forecolor to the same color as the
Backcolor. Now, it appears to be blank

Then to turn the text back on, you would have to go through the controls and
set the forecolor to what it used to be.

and one more thing

TERMINOLOGY ALERT!!!!

"Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL the
fields in a particular form."

Regardless of what a lot of Microsoft documentation says, in true database
terminology, there are no field on a form. The form's recordset has fields,
but the form itself only has controls. A text box is a control, a combo box
is a control, etc.

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

" wrote:

Hi,

Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL
the fields in a particular form. (It would be bonus if anyone could
tell me how to get rid of data showing up in disabled fields. I want
disabled fields to show no data until they "come to life" after a value
is chosen from the combobox)

I have played around with setting each individ field to visible=false
and then visible = true with "After Update" in combo box and this works
fine but I want to know if there is a quick way to set it such that in
one quick swoop, you can enable "disabled" fields after a combobox
selection...there has to be some code out there...like
Forms![Form_Name].fields.all.enabled=true...I tried this, didn't work.

I want to avoid having to code for each individ. field. every time one
has to be added at a later date.

Thx.
S

**[email protected]** · January 5th, 2007, 04:07 PM posted to microsoft.public.access.forms

Hello,

I got somewhere with one out of two issues so I am writing another post
to save others any work.

(1) I researched a lot of the responses for Error 2455 and it seems
that the only way to deal with it is to trap the error so I found
something useful (see code below) and that problem is solved:

Exit_LockBoundControls:
Set ctl = Nothing
Exit Function

Err_LockBoundControls:
Select Case Err.Number
Case 2455 ' ControlSource property doesn't apply to control in
option group.
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_LockBoundControls
End Select

(2) I guess that one last thing that needs to be figured out is once
the user selects a Wafer Name (from my combobox), that all the fields
that were previously locked become unlocked. With the way the code has
been sent to me, the records remain locked after I select my Wafer Name
but additions and deletions are allowed to the data. I would like
everything to become unlocked for editing only after a selection is
made. Is there a way to incorporate this feature into the Funtion
LockBoundControls?

S

wrote:
Hi,

Okay...sorry for all the emails...I just figured out that the error
appears when there is more than one subform...If I just have one
subform, the locking function works. However, if my subform has a
subform, I get the error 2455. Well, truth be told, I have to have all
my subforms so I could not just have one parent form and one subform.
Any ideas?

Also, I would like to exclude Combo31 in the parent form and Combo45 in
a subform. Hopefully, this will be my last commentary for the day.

S

wrote:
Hello,

I re-read through your notes...

I don't understand how to incorporate the exclusion of my combo in the
code you provided.

"Here is a function that either locks or unlocks the controls on a
form. You
pass the form as a form object, True to Lock or False to Unlock, and
the name
of any controls you want to exclude (Like your combo)."

Also, there is the issue of the error when I run the code as is
Private Sub Form_Current()
Call LockBoundControls(Me, True)
End Sub
:
Error 2455 - you entered an expression that has an invalid reference to

the property Form/Report.

I will keep workin' at it.
Thx.
Sargum

Klatuu wrote:
In one quick swoop!
Here is a function that either locks or unlocks the controls on a form. You
pass the form as a form object, True to Lock or False to Unlock, and the name
of any controls you want to exclude (Like your combo).

I don't know where I got this code, I didn't write it.

Put the code below in a standard module so you can use it from any form.
There are two functions. The first calls the second.

As to making the controls that contain values appear to be blank, it can be
done. As you know, Access always show the data for some record. The only
time you see the controls blank is if you are on a new record. But, being the
sneaky devil I am, here is a way to do it. I haven't written the code, but
your post made me think about it.

Write a function (or you could add it to this one) that loops through the
controls and saves the Forecolor for each control so you know what color the
text originally was and sets the Forecolor to the same color as the
Backcolor. Now, it appears to be blank

Then to turn the text back on, you would have to go through the controls and
set the forecolor to what it used to be.

and one more thing

TERMINOLOGY ALERT!!!!

"Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL the
fields in a particular form."

Regardless of what a lot of Microsoft documentation says, in true database
terminology, there are no field on a form. The form's recordset has fields,
but the form itself only has controls. A text box is a control, a combo box
is a control, etc.

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

" wrote:

Hi,

Is there a way to enable ALL fields in a form only after a combo box
selection? I am not worried about enabling/diabling controls, only ALL
the fields in a particular form. (It would be bonus if anyone could
tell me how to get rid of data showing up in disabled fields. I want
disabled fields to show no data until they "come to life" after a value
is chosen from the combobox)

I have played around with setting each individ field to visible=false
and then visible = true with "After Update" in combo box and this works
fine but I want to know if there is a quick way to set it such that in
one quick swoop, you can enable "disabled" fields after a combobox
selection...there has to be some code out there...like
Forms![Form_Name].fields.all.enabled=true...I tried this, didn't work.

I want to avoid having to code for each individ. field. every time one
has to be added at a later date.

Thx.
S

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode