How to change control properties programmatically?

[deko]

I'm trying to find a way to set form/control properties programmatically.

In a nut shell:

1. Open a database
2. Open a form in design view
3. Do something like this:
For Each prp In frm.Properties
If prp.Name = rst!PropName Then
prp = rst!ChangeTo
End If
Next

(this actually works for Form properties)

BUT .. I'm having a hard time with the Control properties...

How to access the properties of controls programmatically?

here is more code....

'first, open the database in question

Dim frm As Form
Dim prp as Property
Dim acc As Access.Application
Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase varPath

''''''''''then open the form in design view

acc.DoCmd.OpenForm rst!ObjectName, View:=acDesign, WindowMode:=acHidden

'''''''''''now access the forms collection (I think that's what this
does...)

Set frm = acc.Forms(strFormName)

''''''''''''''''this is where I get lost...

For Each ctl In frm.Controls

'''''''''''''do I need For ... Each if I know what I'm looking for?

If ctl = acCommandButton Then 'error: This property isn't available in
design view
For Each prp In ctl.Properties
Debug.Print prp.Name & " = " & prp.Value
prp = rst!ChangeTo <<== rst has all the ChangeTo values
Debug.Print prp.Name & " = " & prp.Value
Debug.Print ctl.Name & " changed"
Next
End If
Next

[Mike Storr]

Controls are contained in the Controls collection much the same way an open
form is contained in the Forms collection. You'll need to add the statement
Dim ctl as Control in your code if you have Option Explicit turned on. Use
"For Each ctl in frm.Controls" to loop through all controls in the form and
then another nested For...Each to loop through all the properties in
clt.Properties collection.

Be careful to trap for errors!

In some cases, a control does not have some properties or events ascotiated
to it. Looping in this manner will also include form sections, labels,
lines, ect.
Perhaps use Select Case ctl.ControlType to determine if it is the type of
control that you need to set properties on. Some properties may require
others to be set in a specific manner such as WhatsThisButton - this cannot
be set unless MinMaxButtons is set to None.

Remember to close the form saving changes.

Mike Storr
www.veraccess.com

"deko" <dj****@hotmail.com> wrote in message
news:NA*******************@newssvr27.news.prodigy. com...

I'm trying to find a way to set form/control properties programmatically.

In a nut shell:

1. Open a database
2. Open a form in design view
3. Do something like this:
For Each prp In frm.Properties
If prp.Name = rst!PropName Then
prp = rst!ChangeTo
End If
Next

(this actually works for Form properties)

BUT .. I'm having a hard time with the Control properties...

How to access the properties of controls programmatically?

here is more code....

'first, open the database in question

Dim frm As Form
Dim prp as Property
Dim acc As Access.Application
Set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase varPath

''''''''''then open the form in design view

acc.DoCmd.OpenForm rst!ObjectName, View:=acDesign, WindowMode:=acHidden

'''''''''''now access the forms collection (I think that's what this
does...)

Set frm = acc.Forms(strFormName)

''''''''''''''''this is where I get lost...

For Each ctl In frm.Controls

'''''''''''''do I need For ... Each if I know what I'm looking for?

If ctl = acCommandButton Then 'error: This property isn't available in
design view
For Each prp In ctl.Properties
Debug.Print prp.Name & " = " & prp.Value
prp = rst!ChangeTo <<== rst has all the ChangeTo values
Debug.Print prp.Name & " = " & prp.Value
Debug.Print ctl.Name & " changed"
Next
End If
Next

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you know which control you want to change you can explicitly refer
to it:

Set ctl = frm!ControlName

To get the control type use this:

If ctl.ControlType = acCommandButton Then ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAgi7YechKqOuFEgEQJV/wCcC+ZgBj49UpHkid1/ONixywEM0DQAnjvo
PONE59u06FgxyT4N+0Hgqxey
=2/7q
-----END PGP SIGNATURE-----

deko wrote:

< SNIP >

For Each ctl In frm.Controls

'''''''''''''do I need For ... Each if I know what I'm looking for?

If ctl = acCommandButton Then 'error: This property isn't available in
design view
For Each prp In ctl.Properties
Debug.Print prp.Name & " = " & prp.Value
prp = rst!ChangeTo <<== rst has all the ChangeTo values
Debug.Print prp.Name & " = " & prp.Value
Debug.Print ctl.Name & " changed"
Next
End If
Next

[deko]

> To get the control type use this:

If ctl.ControlType = acCommandButton Then ...

I wish that worked... it's returning a number - it does not seem to want to
match the constant "acWhatever"

The reason this is important is because, as you know, the same property name
can be found in different object types. So, the code below might return
results like this in the immediate window:

********** making changes to frmMain '(the parent form)
Name = frmMain
Name ==>> changed to frmPropNew

********** making changes to frmProperties '(the subform)
Name = frmProperties
Name ==>> changed to frmPropNew

Another issue I'm having is this:
Error Number 2135: This property is read-only and can't be set.
Is there a way to get around this?

Here is the code:

'''''' first do form

Do Until rst.EOF
Select Case rst!Container
Case "Forms"
acc.DoCmd.OpenForm rst!Parent, View:=acDesign,
WindowMode:=acHidden
Set frm = acc.Forms(rst!Parent)
Debug.Print "********** making changes to " & frm.Name
For Each prp In frm.Properties 'loop through Form properties
If prp.Name = rst!PropName Then 'if we find the property in
question, change it
Debug.Print prp.Name & " = " & prp.Value
Debug.Print prp.Name & " ==>> changed to " &
prp.Value
'Debug.Print "***** set new value to " &
rst!ChangeTo
prp = rst!ChangeTo
Debug.Print prp.Name & " changed to " & prp.Value
End If
Next

''''''''''' now do controls

For Each ctl In frm.Controls

'Debug.Print ctl.ControlType 'returns a number, like
"104" rather than "acCommandButton"
'so, it seems I cannot use:
'If ctl.ControlType = rst!ObjectType Then 'to match
ControlType
With ctl
If ctl.Name = rst!ObjectName Then
Debug.Print "********** making changes to " & ctl.Name
For Each prp In ctl.Properties
If prp.Name = rst!PropName Then
Debug.Print prp.Name & " = " & prp.Value
prp = rst!ChangeTo
Debug.Print prp.Name & " ==>> changed to " & prp.Value
End If
Next
End If
End With
Next

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The intrinsic constant acCommandButton has the value 104, therefore
this:

If ctl.ControlType = acCommandButton Then

will evaluate to True when the control is a CommandButton.

Your rst!ObjectType should be a numeric value representing one of the
intrinsic constants for the different control types. My guess is that
rst!ObjectType = "Command Button" - correct? Change it to the
appropriate number (intrinsic constant).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAiZVoechKqOuFEgEQLT1ACdHf68xL2Vq2Wx+UTkBA5e48 uRXx4An2T7
IMRvqSCxEjbwl1+fHdEPN56A
=gYPN
-----END PGP SIGNATURE-----
deko wrote:

To get the control type use this:
If ctl.ControlType = acCommandButton Then ...

I wish that worked... it's returning a number - it does not seem to want to
match the constant "acWhatever"

The reason this is important is because, as you know, the same property name
can be found in different object types. So, the code below might return
results like this in the immediate window:

< SNIP >
For Each ctl In frm.Controls

'Debug.Print ctl.ControlType 'returns a number, like
"104" rather than "acCommandButton"
'so, it seems I cannot use:
'If ctl.ControlType = rst!ObjectType Then 'to match
ControlType
With ctl
If ctl.Name = rst!ObjectName Then
Debug.Print "********** making changes to " & ctl.Name
For Each prp In ctl.Properties
If prp.Name = rst!PropName Then
Debug.Print prp.Name & " = " & prp.Value
prp = rst!ChangeTo
Debug.Print prp.Name & " ==>> changed to " & prp.Value
End If
Next
End If
End With
Next

[deko]

> Your rst!ObjectType should be a numeric value representing one of the

intrinsic constants for the different control types. My guess is that
rst!ObjectType = "Command Button" - correct? Change it to the
appropriate number (intrinsic constant).

Actually, rst!ObjectType is the constant name, e.g. acCommandButton,
acListBox, etc.

it's strange how the constants don't work if I use:

If ctl.ControlType = acCommandButton Then ...

so I've been forced to use:

If ctl.ControlType = Nz(DLookup("PropValue", "qryProperties", _
"ObjectType = " & rst!ObjectType & _
" AND PropName = ContrloType"), 0) Then ...

(does DLookup return a string value?)

The list of constants is derived from the form itself, and saved in a table
like this:

Select Case ctl.ControlType
Case acLabel
strType = acLabel

so I'm saving the constant as a string value - is that why it's not working?

[John Mishefske]

deko wrote:

The list of constants is derived from the form itself, and saved in a table
like this:

Select Case ctl.ControlType
Case acLabel
strType = acLabel

so I'm saving the constant as a string value - is that why it's not working?

These are most likely Long values and this may be the problem. You can use your
Object Browser (F2) to look at Access.AcControlType values (e.g. a Textbox has
a value of 109).

--
'-------------------------------
' John Mishefske
'-------------------------------

[deko]

perhaps I should make an array of all the numeric constant values?? The
Select Case is something of a hack, and it would be nice to use the numbers
if I can keep track of them. Is there another way to work with these
things?

What I am doing is making an inventory of form/report properties, and
tabulating everything in a datasheet which is used to then make changes by
entering the new value...

thx

"John Mishefske" <mi****@execpc.com> wrote in message
news:10*************@corp.supernews.com...

deko wrote:

The list of constants is derived from the form itself, and saved in a table like this:

Select Case ctl.ControlType
Case acLabel
strType = acLabel

so I'm saving the constant as a string value - is that why it's not

working?
These are most likely Long values and this may be the problem. You can use your Object Browser (F2) to look at Access.AcControlType values (e.g. a Textbox has a value of 109).

--
'-------------------------------
' John Mishefske
'-------------------------------

[deko]

> These are most likely Long values and this may be the problem. You can use
your

Object Browser (F2) to look at Access.AcControlType values (e.g. a Textbox has a value of 109).

Thanks for the tip on finding those values in the Object Browser.
Basically, I was mixing up the numeric values with the constants - e.g. 104
vs. acCommandButton. I'll assume those numeric values serve some purpose
other than confusion...

I added the ctl.ControlType (a numeric value) to the recordset (as a Long)
and did this:

For Each ctl In frm.Controls
If ctl.ControlType = rst!Ctl_ID Then 'e.g. 104
With ctl
If ctl.Name = rst!ObjectName Then 'e.g. "cmdClose"
For Each prp In ctl.Properties
If prp.Name = rst!PropName Then 'e.g. "FontName"
prp = rst!ChangeTo 'e.g. "Arial" to "Tahoma"

works great!