field properties in queries - "description" <property not found> even though description entered

[Alex Dybenko]

Hi,
when you get property not found error - open locals window and look at
field's Properties collection - perhaps for checkboxes it has different name
then description.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Allen Browne]

I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.) When I
ran the code, the first one outputted correctly, while the next one
generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a Recordset
instead of the QueryDef?

 

[Allen Browne]

If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long field name
and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The maximum
characters allowed for a fieldname is 64. All the fieldnames were
essentially sentences in the table I was working on (don't ask!). All the
descriptions that were printed, had fieldnames with 50 or less characters.
When I changed all the nonprinting fieldnames to 50 or less characters, it
worked. In both the query and the table. All the while, the object
definition report listed each field as having a description. This seems
like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.) When
I ran the code, the first one outputted correctly, while the next one
generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a Recordset
instead of the QueryDef?

 

[JenR]

I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description in the
properties section.

I wrote some code to loop through the fields and debug.print the description
of each field to verify.

I'm getting a property not found error. The first 10 or so fields are text
fields. They debug.print the description. The next 80 fields are checkboxes.
It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions according
to the report. I started with this problem while trying to work with an
older table (which I cannot change - someone else's), so I made a query and
re-did all the field descriptions. The error still comes up.

So maybe the problem is with the checkboxes. I inserted a field before the
checkboxes start (a date field), gave it a description and re-ran the code.
It debug.prints the new fields description as well and then throws an error
at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?

 

[Allen Browne]

Jen, in your original post you said:
For each field in my query, in design view, I added a description
in the properties section.

But your code uses OpenRecordset() on a *string*, i.e. the recordset is not
from this saved query.

Also, it may be that the Resume Next is masking some other problem. Try this
one:

Function ShowFieldProps(strTable As String)
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb()
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)

For Each fld In rs.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

rs.Close
db.Close

Exit_Handler:
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Select Case Err.Number
Case 3270&
Resume Next
Case Else
Stop
End Select
Resume Exit_Handler
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I can reproduce it in Access 2007.

New table, Table1

First 4 fields are checkboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.
Next 4 fields are textboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.

This is my code:

Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim prp As Property
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM Table1", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
Debug.Print rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
Set x = Nothing

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This is my debug.print

8
2 - The fieldname for this checkbox has 55 characters
3 - The fieldname for this checkbox has 50 characters
6 - The fieldname for this textbox has 55 characters
7 - The fieldname for this textbox has 50 characters

If fieldnames are allowed to have 64 characters, this definately seems
like a bug in 2007. I can't explain it otherwise.

If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long field
name and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The maximum
characters allowed for a fieldname is 64. All the fieldnames were
essentially sentences in the table I was working on (don't ask!). All
the descriptions that were printed, had fieldnames with 50 or less
characters. When I changed all the nonprinting fieldnames to 50 or less
characters, it worked. In both the query and the table. All the while,
the object definition report listed each field as having a description.
This seems like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]",
dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing




I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.)
When I ran the code, the first one outputted correctly, while the next
one generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a Recordset
instead of the QueryDef?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description in
the properties section.

I wrote some code to loop through the fields and debug.print the
description of each field to verify.

I'm getting a property not found error. The first 10 or so fields are
text fields. They debug.print the description. The next 80 fields are
checkboxes. It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions
according to the report. I started with this problem while trying to
work with an older table (which I cannot change - someone else's), so
I made a query and re-did all the field descriptions. The error still
comes up.

So maybe the problem is with the checkboxes. I inserted a field before
the checkboxes start (a date field), gave it a description and re-ran
the code. It debug.prints the new fields description as well and then
throws an error at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?

 

[JenR]

I did the same. It was the fields of a recordset, the source being a query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The maximum
characters allowed for a fieldname is 64. All the fieldnames were
essentially sentences in the table I was working on (don't ask!). All the
descriptions that were printed, had fieldnames with 50 or less characters.
When I changed all the nonprinting fieldnames to 50 or less characters, it
worked. In both the query and the table. All the while, the object
definition report listed each field as having a description. This seems like
a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]", dbOpenDynaset)
With rs
..MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

 

[JenR]

I can reproduce it in Access 2007.

New table, Table1

First 4 fields are checkboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.
Next 4 fields are textboxes, with fieldnames of 64, 60, 55 and 50 characters
respectively.

This is my code:

Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim prp As Property
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM Table1", dbOpenDynaset)
With rs
..MoveFirst
x = rs.Fields.Count
Debug.Print rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
Set x = Nothing

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This is my debug.print

8
2 - The fieldname for this checkbox has 55 characters
3 - The fieldname for this checkbox has 50 characters
6 - The fieldname for this textbox has 55 characters
7 - The fieldname for this textbox has 50 characters

If fieldnames are allowed to have 64 characters, this definately seems like
a bug in 2007. I can't explain it otherwise.

If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long field
name and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The maximum
characters allowed for a fieldname is 64. All the fieldnames were
essentially sentences in the table I was working on (don't ask!). All the
descriptions that were printed, had fieldnames with 50 or less
characters. When I changed all the nonprinting fieldnames to 50 or less
characters, it worked. In both the query and the table. All the while,
the object definition report listed each field as having a description.
This seems like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.) When
I ran the code, the first one outputted correctly, while the next one
generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a Recordset
instead of the QueryDef?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description in
the properties section.

I wrote some code to loop through the fields and debug.print the
description of each field to verify.

I'm getting a property not found error. The first 10 or so fields are
text fields. They debug.print the description. The next 80 fields are
checkboxes. It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions
according to the report. I started with this problem while trying to
work with an older table (which I cannot change - someone else's), so I
made a query and re-did all the field descriptions. The error still
comes up.

So maybe the problem is with the checkboxes. I inserted a field before
the checkboxes start (a date field), gave it a description and re-ran
the code. It debug.prints the new fields description as well and then
throws an error at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?

 

[Allen Browne]

Omit the first and last lines (Function ... and End Function), and put the
rest in your button's Click event,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Jen, in your original post you said:
For each field in my query, in design view, I added a description
in the properties section.

But your code uses OpenRecordset() on a *string*, i.e. the recordset is
not from this saved query.

I did both. Once on a query with a filtered recordset, once on the
original table. They both gave that same error.

Also, it may be that the Resume Next is masking some other problem. Try
this one:

I tried it with and without Resume next. With it, I was able to determine
which field descriptions were printed, then I could compare those ones
with the others. Interestingly, I copied/pasted the immediate window to
word, highlighted the ones that weren't printed in yellow and noticed that
of all the ones that were printed, their fieldnames were shorter than the
shortest nonprinting one.

I'm not sure where to put the code you listed below? <-- not a newbie, but
not a pro!

Function ShowFieldProps(strTable As String)
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb()
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)

For Each fld In rs.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

rs.Close
db.Close

Exit_Handler:
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Select Case Err.Number
Case 3270&
Resume Next
Case Else
Stop
End Select
Resume Exit_Handler
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I can reproduce it in Access 2007.

New table, Table1

First 4 fields are checkboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.
Next 4 fields are textboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.

This is my code:

Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim prp As Property
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM Table1", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
Debug.Print rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
Set x = Nothing

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This is my debug.print

8
2 - The fieldname for this checkbox has 55 characters
3 - The fieldname for this checkbox has 50 characters
6 - The fieldname for this textbox has 55 characters
7 - The fieldname for this textbox has 50 characters

If fieldnames are allowed to have 64 characters, this definately seems
like a bug in 2007. I can't explain it otherwise.



If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long field
name and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The
maximum characters allowed for a fieldname is 64. All the fieldnames
were essentially sentences in the table I was working on (don't ask!).
All the descriptions that were printed, had fieldnames with 50 or less
characters. When I changed all the nonprinting fieldnames to 50 or
less characters, it worked. In both the query and the table. All the
while, the object definition report listed each field as having a
description. This seems like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]",
dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing




I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.)
When I ran the code, the first one outputted correctly, while the
next one generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a
Recordset instead of the QueryDef?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description in
the properties section.

I wrote some code to loop through the fields and debug.print the
description of each field to verify.

I'm getting a property not found error. The first 10 or so fields
are text fields. They debug.print the description. The next 80
fields are checkboxes. It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions
according to the report. I started with this problem while trying to
work with an older table (which I cannot change - someone else's),
so I made a query and re-did all the field descriptions. The error
still comes up.

So maybe the problem is with the checkboxes. I inserted a field
before the checkboxes start (a date field), gave it a description
and re-ran the code. It debug.prints the new fields description as
well and then throws an error at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?

 

[JR]

Jen, in your original post you said:
For each field in my query, in design view, I added a description
in the properties section.

But your code uses OpenRecordset() on a *string*, i.e. the recordset is
not from this saved query.

I did both. Once on a query with a filtered recordset, once on the original
table. They both gave that same error.

Also, it may be that the Resume Next is masking some other problem. Try
this one:

I tried it with and without Resume next. With it, I was able to determine
which field descriptions were printed, then I could compare those ones with
the others. Interestingly, I copied/pasted the immediate window to word,
highlighted the ones that weren't printed in yellow and noticed that of all
the ones that were printed, their fieldnames were shorter than the shortest
nonprinting one.

I'm not sure where to put the code you listed below? <-- not a newbie, but
not a pro!

Function ShowFieldProps(strTable As String)
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb()
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)

For Each fld In rs.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

rs.Close
db.Close

Exit_Handler:
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Select Case Err.Number
Case 3270&
Resume Next
Case Else
Stop
End Select
Resume Exit_Handler
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I can reproduce it in Access 2007.

New table, Table1

First 4 fields are checkboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.
Next 4 fields are textboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.

This is my code:

Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim prp As Property
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM Table1", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
Debug.Print rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
Set x = Nothing

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This is my debug.print

8
2 - The fieldname for this checkbox has 55 characters
3 - The fieldname for this checkbox has 50 characters
6 - The fieldname for this textbox has 55 characters
7 - The fieldname for this textbox has 50 characters

If fieldnames are allowed to have 64 characters, this definately seems
like a bug in 2007. I can't explain it otherwise.

If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long field
name and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The maximum
characters allowed for a fieldname is 64. All the fieldnames were
essentially sentences in the table I was working on (don't ask!). All
the descriptions that were printed, had fieldnames with 50 or less
characters. When I changed all the nonprinting fieldnames to 50 or less
characters, it worked. In both the query and the table. All the while,
the object definition report listed each field as having a description.
This seems like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]",
dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing




I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in query
design. For the next one, I left it blank (no description entered.)
When I ran the code, the first one outputted correctly, while the next
one generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a
Recordset instead of the QueryDef?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description in
the properties section.

I wrote some code to loop through the fields and debug.print the
description of each field to verify.

I'm getting a property not found error. The first 10 or so fields are
text fields. They debug.print the description. The next 80 fields are
checkboxes. It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions
according to the report. I started with this problem while trying to
work with an older table (which I cannot change - someone else's), so
I made a query and re-did all the field descriptions. The error still
comes up.

So maybe the problem is with the checkboxes. I inserted a field
before the checkboxes start (a date field), gave it a description and
re-ran the code. It debug.prints the new fields description as well
and then throws an error at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?

 

[JR]

I did it. I get: Compile error: label not defined -> On Error GoTo
Err_Handler

Also changed strTable to Table1 and get the same error.

Omit the first and last lines (Function ... and End Function), and put the
rest in your button's Click event,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Jen, in your original post you said:
For each field in my query, in design view, I added a description
in the properties section.

But your code uses OpenRecordset() on a *string*, i.e. the recordset is
not from this saved query.

I did both. Once on a query with a filtered recordset, once on the
original table. They both gave that same error.

Also, it may be that the Resume Next is masking some other problem. Try
this one:

I tried it with and without Resume next. With it, I was able to determine
which field descriptions were printed, then I could compare those ones
with the others. Interestingly, I copied/pasted the immediate window to
word, highlighted the ones that weren't printed in yellow and noticed
that of all the ones that were printed, their fieldnames were shorter
than the shortest nonprinting one.

I'm not sure where to put the code you listed below? <-- not a newbie,
but not a pro!

Function ShowFieldProps(strTable As String)
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb()
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)

For Each fld In rs.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

rs.Close
db.Close

Exit_Handler:
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Select Case Err.Number
Case 3270&
Resume Next
Case Else
Stop
End Select
Resume Exit_Handler
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I can reproduce it in Access 2007.

New table, Table1

First 4 fields are checkboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.
Next 4 fields are textboxes, with fieldnames of 64, 60, 55 and 50
characters respectively.

This is my code:

Private Sub Command2_Click()
Dim db As Database
Dim rs As Recordset
Dim prp As Property
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM Table1", dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
Debug.Print rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
Set x = Nothing

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This is my debug.print

8
2 - The fieldname for this checkbox has 55 characters
3 - The fieldname for this checkbox has 50 characters
6 - The fieldname for this textbox has 55 characters
7 - The fieldname for this textbox has 50 characters

If fieldnames are allowed to have 64 characters, this definately seems
like a bug in 2007. I can't explain it otherwise.



If it's a bug, I couldn't repro it in A2010.

I created a field named:
This is a field with a very long name of at least 63 characters
and gave it a description in the query. The code printed the long
field name and its description okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I did the same. It was the fields of a recordset, the source being a
query.

The same results came from recordset, the source being a table.

After fiddling for a few hours, I believe I have it solved. The
maximum characters allowed for a fieldname is 64. All the fieldnames
were essentially sentences in the table I was working on (don't
ask!). All the descriptions that were printed, had fieldnames with 50
or less characters. When I changed all the nonprinting fieldnames to
50 or less characters, it worked. In both the query and the table.
All the while, the object definition report listed each field as
having a description. This seems like a bug?

Dim db As Database
Dim rs As Recordset
Dim prp As Property
'On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM [qryAuditStats]",
dbOpenDynaset)
With rs
.MoveFirst
x = rs.Fields.Count
For x = 0 To (rs.Fields.Count - 1)
Debug.Print x & " - " & rs.Fields(x).Properties("Description")
Next x
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing




I'm not able to reproduce this problem in Access 2010.

What I did was to set up a table with some yes/no fields (as well as
others.) For the first yes/no field, I entered a Description in
query design. For the next one, I left it blank (no description
entered.) When I ran the code, the first one outputted correctly,
while the next one generated error 3270 as you would expect.

Here's the code I used:

Function ShowQueryDescrip()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsWithDescrip")

For Each fld In qdf.Fields
Debug.Print fld.Name,
Debug.Print fld.Properties("Description");
Debug.Print
Next

Set prp = Nothing
Set fld = Nothing
Set qdf = Nothing
Set db = Nothing
End Function

Wonder what was different. Perhaps you tested the fields of a
Recordset instead of the QueryDef?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am running into problems and have no explanation for it.

For each field in my query, in design view, I added a description
in the properties section.

I wrote some code to loop through the fields and debug.print the
description of each field to verify.

I'm getting a property not found error. The first 10 or so fields
are text fields. They debug.print the description. The next 80
fields are checkboxes. It halts on the first checkbox.

I ran an object documentor report and ALL fields have descriptions
according to the report. I started with this problem while trying
to work with an older table (which I cannot change - someone
else's), so I made a query and re-did all the field descriptions.
The error still comes up.

So maybe the problem is with the checkboxes. I inserted a field
before the checkboxes start (a date field), gave it a description
and re-ran the code. It debug.prints the new fields description as
well and then throws an error at the first checkbox.

Or not.... I added in On Error Resume Next to the code

89 fields, only 13 debug.printed of those 3 were checkbox field
descriptions. Those fields were not together in a row.

So what's up with the field properties of checkboxes?