altering field properties in code

[Donald Grove]

Is it possible to retrieve field properties from a table in access2000
using code?

I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as dao.property

set dbs = CurrentDb
set tbl = dbs.TableDefs(tableName)
set fld = tbl.Fields(fieldName)
set prop = fld.Properties(DataType)

debug.print prop.name
"
and I get Type Mismatch, or other error messages. This is just one of
many ways I have tried, but I haven't found one that works yet.

What I am looking for is actual to retrieve the sql string from
fields where there is a lookup with a combo box display control (so
that I can run code to change the sql)

[Tom van Stiphout]

On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
<do*********@verizon.net> wrote:

The reason for the error is that:
DataType
is not a defined variable.
You might want to try the literal string:
"DataType"

I typically write:
for each prop in fld.Properties
debug.print prop.name, prop.value
next prop

-Tom.

Is it possible to retrieve field properties from a table in access2000
using code?

I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as dao.property

set dbs = CurrentDb
set tbl = dbs.TableDefs(tableName)
set fld = tbl.Fields(fieldName)
set prop = fld.Properties(DataType)

debug.print prop.name
"
and I get Type Mismatch, or other error messages. This is just one of
many ways I have tried, but I haven't found one that works yet.

What I am looking for is actual to retrieve the sql string from
fields where there is a lookup with a combo box display control (so
that I can run code to change the sql)

[Donald Grove]

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"


On Fri, 18 Nov 2005 21:28:45 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
<do*********@verizon.net> wrote:

The reason for the error is that:
DataType
is not a defined variable.
You might want to try the literal string:
"DataType"

I typically write:
for each prop in fld.Properties
debug.print prop.name, prop.value
next prop

-Tom.

Is it possible to retrieve field properties from a table in access2000
using code?

I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as dao.property

set dbs = CurrentDb
set tbl = dbs.TableDefs(tableName)
set fld = tbl.Fields(fieldName)
set prop = fld.Properties(DataType)

debug.print prop.name
"
and I get Type Mismatch, or other error messages. This is just one of
many ways I have tried, but I haven't found one that works yet.

What I am looking for is actual to retrieve the sql string from
fields where there is a lookup with a combo box display control (so
that I can run code to change the sql)

[Allen Browne]

What's the DataType property?

Are you talking about the Type property? If so, you will not be able to
alter the property after the initial CreateField().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Donald Grove" <do*********@verizon.net> wrote in message
news:el********************************@4ax.com...

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"

[Tom van Stiphout]

On Sat, 19 Nov 2005 13:33:21 GMT, Donald Grove
<do*********@verizon.net> wrote:

I see another problem. Your code should be:
Dim prop As DAO.Property
Not the collection, but an individual one.

-Tom.

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"


On Fri, 18 Nov 2005 21:28:45 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
<do*********@verizon.net> wrote:

The reason for the error is that:
DataType
is not a defined variable.
You might want to try the literal string:
"DataType"

I typically write:
for each prop in fld.Properties
debug.print prop.name, prop.value
next prop

-Tom.

Is it possible to retrieve field properties from a table in access2000
using code?

I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as dao.property

set dbs = CurrentDb
set tbl = dbs.TableDefs(tableName)
set fld = tbl.Fields(fieldName)
set prop = fld.Properties(DataType)

debug.print prop.name
"
and I get Type Mismatch, or other error messages. This is just one of
many ways I have tried, but I haven't found one that works yet.

What I am looking for is actual to retrieve the sql string from
fields where there is a lookup with a combo box display control (so
that I can run code to change the sql)

[Donald Grove]

Yes, I finally figured that one out. Thanks to both Allen Browne and
Tom Van Stiphout for your help!

I now can generate documentation for all the fields in my tables. I
am very happy.

But now a new question: I want to do the same thing for forms. I have
created (borrowed) a system for identifying the forms in the AllForms
collection, opening them one by one, and listing all their controls.
But if the control is a combo box, I would like to be able to retrieve
its rowsource sql string and so far I am not having any luck

dim ctl as Control
strRowSource = ctl.Properties("rowsource")

does not return anything. Ideas?

(The idea is to identify combo box controls with SQL that matches
combo box lookup controls in table fields)
On Sat, 19 Nov 2005 10:57:53 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 13:33:21 GMT, Donald Grove
<do*********@verizon.net> wrote:

I see another problem. Your code should be:
Dim prop As DAO.Property
Not the collection, but an individual one.

-Tom.

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"


On Fri, 18 Nov 2005 21:28:45 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
<do*********@verizon.net> wrote:

The reason for the error is that:
DataType
is not a defined variable.
You might want to try the literal string:
"DataType"

I typically write:
for each prop in fld.Properties
debug.print prop.name, prop.value
next prop

-Tom.
Is it possible to retrieve field properties from a table in access2000
using code?

I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as dao.property

set dbs = CurrentDb
set tbl = dbs.TableDefs(tableName)
set fld = tbl.Fields(fieldName)
set prop = fld.Properties(DataType)

debug.print prop.name
"
and I get Type Mismatch, or other error messages. This is just one of
many ways I have tried, but I haven't found one that works yet.

What I am looking for is actual to retrieve the sql string from
fields where there is a lookup with a combo box display control (so
that I can run code to change the sql)

[Tom van Stiphout]

On Sat, 19 Nov 2005 22:57:18 GMT, Donald Grove
<do*********@verizon.net> wrote:

Not sure what the problem is. This works for me in the Northwind
sample application:
?forms("Employees").Controls("ReportsTo").Properti es("RowSource").Value

You do set your control object before trying to read a property,
right? Here is a more complete sample:
Sub ListForms()
Dim o As AccessObject
Dim f As Form
Dim ctl As Control
Dim prop As Property

On Error Resume Next 'Because can't get value of all
properties.
For Each o In Application.CurrentProject.AllForms
DoCmd.OpenForm o.Name, acDesign
Set f = Forms(o.Name)
For Each ctl In f.Controls
For Each prop In ctl.Properties
Debug.Print o.Name, ctl.Name, prop.Name, prop.Value
Next prop
Next ctl
DoCmd.Close acForm, o.Name
Set f = Nothing
Next o
End Sub

Yes, I finally figured that one out. Thanks to both Allen Browne and
Tom Van Stiphout for your help!

I now can generate documentation for all the fields in my tables. I
am very happy.

But now a new question: I want to do the same thing for forms. I have
created (borrowed) a system for identifying the forms in the AllForms
collection, opening them one by one, and listing all their controls.
But if the control is a combo box, I would like to be able to retrieve
its rowsource sql string and so far I am not having any luck

dim ctl as Control
strRowSource = ctl.Properties("rowsource")

does not return anything. Ideas?

(The idea is to identify combo box controls with SQL that matches
combo box lookup controls in table fields)
On Sat, 19 Nov 2005 10:57:53 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 13:33:21 GMT, Donald Grove
<do*********@verizon.net> wrote:

I see another problem. Your code should be:
Dim prop As DAO.Property
Not the collection, but an individual one.

-Tom.

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"


On Fri, 18 Nov 2005 21:28:45 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
<do*********@verizon.net> wrote:

The reason for the error is that:
DataType
is not a defined variable.
You might want to try the literal string:
"DataType"

I typically write:
for each prop in fld.Properties
debug.print prop.name, prop.value
next prop

-Tom.
>Is it possible to retrieve field properties from a table in access2000
>using code?
>
>I have tried:
>"
>dim dbs as dao.database
>dim tbl as dao.tabledef
>dim fld as dao.field
>dim prop as dao.property
>
>set dbs = CurrentDb
>set tbl = dbs.TableDefs(tableName)
>set fld = tbl.Fields(fieldName)
>set prop = fld.Properties(DataType)
>
>debug.print prop.name
>"
>and I get Type Mismatch, or other error messages. This is just one of
>many ways I have tried, but I haven't found one that works yet.
>
> What I am looking for is actual to retrieve the sql string from
>fields where there is a lookup with a combo box display control (so
>that I can run code to change the sql)

[Donald Grove]

It may take me a while to understand why my old code didn't work. But
yours works extremely well and I am very grateful to you! Thanks.
On Sat, 19 Nov 2005 17:00:05 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 22:57:18 GMT, Donald Grove
<do*********@verizon.net> wrote:

Not sure what the problem is. This works for me in the Northwind
sample application:
?forms("Employees").Controls("ReportsTo").Propert ies("RowSource").Value

You do set your control object before trying to read a property,
right? Here is a more complete sample:
Sub ListForms()
Dim o As AccessObject
Dim f As Form
Dim ctl As Control
Dim prop As Property

On Error Resume Next 'Because can't get value of all
properties.
For Each o In Application.CurrentProject.AllForms
DoCmd.OpenForm o.Name, acDesign
Set f = Forms(o.Name)
For Each ctl In f.Controls
For Each prop In ctl.Properties
Debug.Print o.Name, ctl.Name, prop.Name, prop.Value
Next prop
Next ctl
DoCmd.Close acForm, o.Name
Set f = Nothing
Next o
End Sub

Yes, I finally figured that one out. Thanks to both Allen Browne and
Tom Van Stiphout for your help!

I now can generate documentation for all the fields in my tables. I
am very happy.

But now a new question: I want to do the same thing for forms. I have
created (borrowed) a system for identifying the forms in the AllForms
collection, opening them one by one, and listing all their controls.
But if the control is a combo box, I would like to be able to retrieve
its rowsource sql string and so far I am not having any luck

dim ctl as Control
strRowSource = ctl.Properties("rowsource")

does not return anything. Ideas?

(The idea is to identify combo box controls with SQL that matches
combo box lookup controls in table fields)
On Sat, 19 Nov 2005 10:57:53 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Sat, 19 Nov 2005 13:33:21 GMT, Donald Grove
<do*********@verizon.net> wrote:

I see another problem. Your code should be:
Dim prop As DAO.Property
Not the collection, but an individual one.

-Tom.

Sorry, but that isn't working, or I am not understanding you. I
suspect I am missing a step.

Here is my code, and I will include the opening declarations, in case
that is where my mistake is:
"
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim prop As DAO.Properties

Set dbs = CurrentDb
Set tbl = dbs.TableDefs(table)
Set fld = tbl.Fields(field)
Set prop = fld.Properties(property)

Debug.Print tbl.Name
Debug.Print fld.Name
debug.print prop.name
"

for this line:

Set prop = fld.Properties(0) --trying an integer where zero is just an
example OR

Set prop = fld.Properties("datatype") --trying a literal text string

I get a "type mismatch"

for this line:

Set prop = fld.Properties(dataype) --text string without quotes

I get "property not found"


On Fri, 18 Nov 2005 21:28:45 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

>On Sat, 19 Nov 2005 03:31:32 GMT, Donald Grove
><do*********@verizon.net> wrote:
>
>The reason for the error is that:
>DataType
>is not a defined variable.
>You might want to try the literal string:
>"DataType"
>
>I typically write:
>for each prop in fld.Properties
> debug.print prop.name, prop.value
>next prop
>
>-Tom.
>
>
>>Is it possible to retrieve field properties from a table in access2000
>>using code?
>>
>>I have tried:
>>"
>>dim dbs as dao.database
>>dim tbl as dao.tabledef
>>dim fld as dao.field
>>dim prop as dao.property
>>
>>set dbs = CurrentDb
>>set tbl = dbs.TableDefs(tableName)
>>set fld = tbl.Fields(fieldName)
>>set prop = fld.Properties(DataType)
>>
>>debug.print prop.name
>>"
>>and I get Type Mismatch, or other error messages. This is just one of
>>many ways I have tried, but I haven't found one that works yet.
>>
>> What I am looking for is actual to retrieve the sql string from
>>fields where there is a lookup with a combo box display control (so
>>that I can run code to change the sql)