Access.2007.VBA.Bibl..

ErrorHandler:

MsgBox “Error No: “ & err.Number _

& “; Description: “ & err.Description

Resume ErrorHandlerExit

End Sub

Public Sub ListQueryFields(ByVal control As IRibbonControl)

On Error Resume Next

Dim strQueryName As String

Dim qdf As DAO.QueryDef

First, clear data from the old table of query and field names:

strTable = “zstblQueryAndFieldNames”

strReport = “zsrptQueryAndFieldNames”

DoCmd.SetWarnings False

strSQL = “DELETE * FROM “ & strTable

DoCmd.RunSQL strSQL

Fill the table with query and field names, iterating through the database’s QueryDefs collection

(only select queries will have their fields listed):

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strTable, dbOpenTable)

For Each qdf In dbs.QueryDefs

strQueryName = qdf.Name

Debug.Print “Query name: “ & strQueryName

If Left(strQueryName, 4) <> “MSys” Then

Set flds = qdf.Fields

For Each fld In flds

strFieldName = fld.Name

With rst

.AddNew

!QueryName = strQueryName

!FieldName = strFieldName

!DataType = fld.Type

!Required = fld.Required

.Update

End With

Next fld

End If

Next qdf

rst.Close

DoCmd.OpenTable strTable

Part III Adding More Functionality to Office