Editing object description properties with VBA

[dr1256dr]

I created a routine to read and edit the description properties of tables, (the one you see in the database window). It seems to work 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 with 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 descriptions , it grabs the first existing table description. And so forth....

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.

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

Thanks in advance,
Dennis

 

[TheRealDeal]

I'm trying to follow your coding and I am not sure where you're renaming the table Description. Would you mind explaining to me where it is the the table DESC is being renamed? I am curious though if you were able to get the query Description to be renamed?