Editing object description properties with VBA

[Dennis Ruppert]

I created a routine to read and edit the description properties of
tables, (the one you see in the database window). It works just fine.
This is the basic code behind it, I substituted all my
editing/updating code with the debug.print to keep it simple.

Function EditTableDescriptions()
On Error GoTo Err_EditTableDescriptions

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim TableNameText As String

Set db = CurrentDb

For Each tbl In db.TableDefs
NoDescription = False
Set prp = tbl.Properties("description")
If NoDescription Then
Debug.Print "Table: " & tbl.Name
DescriptionText = "No Description"
TableNameText = tbl.Name

Else
Debug.Print "Table: " & tbl.Name & " - " & prp.Value
DescriptionText = prp.Value
TableNameText = tbl.Name

End If
Next

Exit_EditTableDescriptions:
db.Close
Exit Function

Err_EditTableDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditTableDescriptions
End If
End Function

Now, I am trying to do the same qith the queries:

Function EditQueryDescriptions()
On Error GoTo Err_EditQueryDescriptions

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim QueryNameText As String

Set db = CurrentDb

For Each qry In db.QueryDefs
NoDescription = False
Set prp = qry.Properties("description")
If NoDescription Then
Debug.Print "Query: " & qry.Name
DescriptionText = "No Description"
QueryNameText = qry.Name

Else
Debug.Print "Query: " & qry.Name & " - " & prp.Value
DescriptionText = prp.Value
QueryNameText = qry.Name

End If
Next

Exit_EditQueryDescriptions:
db.Close
Exit Function

Err_EditQueryDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditQueryDescriptions
End If
End Function

My problem is with the second procedure. If there is no existing table
descriptions, it grabs the correct query descriptions. If there are
existing query descriptions AND table descriptions, it grabs the
correct query descriptions. But, if there is no existing query
description , it grabs the first existing table description.

I obviously have something in the wrong sequence, or a wrong variable,
or something. I am relatively new to this, so I could use some help
here.

I promise to learn something from this, not just copy and paste a code
fix!

Thanks in advance, Dennis

[Allen Browne]

Hi Dennis

I think your observation is correct, that if a query has not description,
but it is based on a table that does have a description, Access lies and
returns the Description of the table as the Description of the query.

Makes no sense to me. There are actually many cases where Access tries to be
too helpful and just muddies the waters.

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

"Dennis Ruppert" <dr******@ruppertweb.com> wrote in message
news:f7**************************@posting.google.c om...

I created a routine to read and edit the description properties of
tables, (the one you see in the database window). It works just fine.
This is the basic code behind it, I substituted all my
editing/updating code with the debug.print to keep it simple.

Function EditTableDescriptions()
On Error GoTo Err_EditTableDescriptions

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim TableNameText As String

Set db = CurrentDb

For Each tbl In db.TableDefs
NoDescription = False
Set prp = tbl.Properties("description")
If NoDescription Then
Debug.Print "Table: " & tbl.Name
DescriptionText = "No Description"
TableNameText = tbl.Name

Else
Debug.Print "Table: " & tbl.Name & " - " & prp.Value
DescriptionText = prp.Value
TableNameText = tbl.Name

End If
Next

Exit_EditTableDescriptions:
db.Close
Exit Function

Err_EditTableDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditTableDescriptions
End If
End Function

Now, I am trying to do the same qith the queries:

Function EditQueryDescriptions()
On Error GoTo Err_EditQueryDescriptions

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim QueryNameText As String

Set db = CurrentDb

For Each qry In db.QueryDefs
NoDescription = False
Set prp = qry.Properties("description")
If NoDescription Then
Debug.Print "Query: " & qry.Name
DescriptionText = "No Description"
QueryNameText = qry.Name

Else
Debug.Print "Query: " & qry.Name & " - " & prp.Value
DescriptionText = prp.Value
QueryNameText = qry.Name

End If
Next

Exit_EditQueryDescriptions:
db.Close
Exit Function

Err_EditQueryDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditQueryDescriptions
End If
End Function

My problem is with the second procedure. If there is no existing table
descriptions, it grabs the correct query descriptions. If there are
existing query descriptions AND table descriptions, it grabs the
correct query descriptions. But, if there is no existing query
description , it grabs the first existing table description.

I obviously have something in the wrong sequence, or a wrong variable,
or something. I am relatively new to this, so I could use some help
here.

I promise to learn something from this, not just copy and paste a code
fix!

Thanks in advance, Dennis

[Dennis Ruppert]

Allen

Interesting. Can I assume that you do not see anything incorrect in my
code, and that it is just a "Microsoft thing"?

Thanks,

Dennis

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!