calling an unbound combobox?

S

sargum

Hi,

I think this is a simple question but I have used up 3 hours trying to
solve it.

Thanks to someone who has been kind to me, I have code that disables
all the fields in a form and more importantly, allows one to specify
which field to exclude from the fate of being disabled if so desired.

Well, I want to exclude Combo31. Unfortunately, it is an "unbound"
control and is having trouble being "noticed" I guess. I tested this
Call with a "bound" control and there were no problem. So, I called
the function (see Module code below) with:

Private Sub Form_Current()
Call LockBoundControls (Me, True, "Combo31")
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
Call LockBoundControls(Me, True, Forms![Wafer Info].Form.Combo31)
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
nm = Forms![Wafer Info].Form.Combo31
Call LockBoundControls(Me, True, nm)
End Sub

I really don't know what else to try. Suggestions.

Sargum

------------------
Module Code:
Public Function LockBoundControls(ByVal frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_LockBoundControls

'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, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton, acComboBox


'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.Enabled = bLock Then
ctl.Enabled = Not 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_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


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
 
B

BruceM

You could probably just unlock the control at the end of the function:
Me.Combo31.Locked = False

You would also need to leave it out of the avarExceptionList by not
including it in the Function call.

Hi,

I think this is a simple question but I have used up 3 hours trying to
solve it.

Thanks to someone who has been kind to me, I have code that disables
all the fields in a form and more importantly, allows one to specify
which field to exclude from the fate of being disabled if so desired.

Well, I want to exclude Combo31. Unfortunately, it is an "unbound"
control and is having trouble being "noticed" I guess. I tested this
Call with a "bound" control and there were no problem. So, I called
the function (see Module code below) with:

Private Sub Form_Current()
Call LockBoundControls (Me, True, "Combo31")
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
Call LockBoundControls(Me, True, Forms![Wafer Info].Form.Combo31)
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
nm = Forms![Wafer Info].Form.Combo31
Call LockBoundControls(Me, True, nm)
End Sub

I really don't know what else to try. Suggestions.

Sargum

------------------
Module Code:
Public Function LockBoundControls(ByVal frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_LockBoundControls

'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, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton, acComboBox


'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.Enabled = bLock Then
ctl.Enabled = Not 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_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


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
Click to expand...
 
K

Klatuu

An unbound control will not be included. Note the name of the function:
LockBoundControls

This is the line that causes it to be skipped:
If HasProperty(ctl, "ControlSource") Then

Only bound controls have a Control Source property.

The code only affects bound controls whether you are locking or unlocking,
so it should not be affected whether you are locking or unlocking. If you
want to lock and unlock the unbound control, you will have to do that
separately.


--
Dave Hargis, Microsoft Access MVP


[email protected] said:
Hi,

I think this is a simple question but I have used up 3 hours trying to
solve it.

Thanks to someone who has been kind to me, I have code that disables
all the fields in a form and more importantly, allows one to specify
which field to exclude from the fate of being disabled if so desired.

Well, I want to exclude Combo31. Unfortunately, it is an "unbound"
control and is having trouble being "noticed" I guess. I tested this
Call with a "bound" control and there were no problem. So, I called
the function (see Module code below) with:

Private Sub Form_Current()
Call LockBoundControls (Me, True, "Combo31")
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
Call LockBoundControls(Me, True, Forms![Wafer Info].Form.Combo31)
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
nm = Forms![Wafer Info].Form.Combo31
Call LockBoundControls(Me, True, nm)
End Sub

I really don't know what else to try. Suggestions.

Sargum

------------------
Module Code:
Public Function LockBoundControls(ByVal frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_LockBoundControls

'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, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton, acComboBox


'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.Enabled = bLock Then
ctl.Enabled = Not 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_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


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
Click to expand...
 
V

Van T. Dinh

Dave
Only bound controls have a Control Source property.
Click to expand...

That statement may not be true. The unbound ComboBox still has the
ControlSource Property but it is an empty String. Thus, the problem is the
next statement where the code check the length of the ControlSource string.

--
Cheers
Van T. Dinh
MVP (Access)



Klatuu said:
An unbound control will not be included. Note the name of the function:
LockBoundControls

This is the line that causes it to be skipped:
If HasProperty(ctl, "ControlSource") Then

Only bound controls have a Control Source property.

The code only affects bound controls whether you are locking or unlocking,
so it should not be affected whether you are locking or unlocking. If you
want to lock and unlock the unbound control, you will have to do that
separately.


--
Dave Hargis, Microsoft Access MVP


[email protected] said:
Hi,

I think this is a simple question but I have used up 3 hours trying to
solve it.

Thanks to someone who has been kind to me, I have code that disables
all the fields in a form and more importantly, allows one to specify
which field to exclude from the fate of being disabled if so desired.

Well, I want to exclude Combo31. Unfortunately, it is an "unbound"
control and is having trouble being "noticed" I guess. I tested this
Call with a "bound" control and there were no problem. So, I called
the function (see Module code below) with:

Private Sub Form_Current()
Call LockBoundControls (Me, True, "Combo31")
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
Call LockBoundControls(Me, True, Forms![Wafer Info].Form.Combo31)
End Sub

then I tried ...

Private Sub Form_Current()
Dim nm As String
nm = Forms![Wafer Info].Form.Combo31
Call LockBoundControls(Me, True, nm)
End Sub

I really don't know what else to try. Suggestions.

Sargum

------------------
Module Code:
Public Function LockBoundControls(ByVal frm As Form, bLock As Boolean,
ParamArray avarExceptionList())
On Error GoTo Err_LockBoundControls

'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, acListBox, acOptionGroup, acCheckBox,
acOptionButton, acToggleButton, acComboBox


'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.Enabled = bLock Then
ctl.Enabled = Not 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_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


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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide Text in Subform Datasheet View 0
Subform not visible when main form is locked 1
Lock 1 record (LockBox) 4
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
carry over 1
Problem locking bound controls 5
WORD VBA Search and switch to advanced ... 1
Controls Lock/UnLock Password To Prompt only when UnLocking 2

Top