Get Column Names from an existing query - Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I'm trying to find a resource that will tell me how to pull the field/column
names from existing queries to use in a form that will build ad hoc reports.
I'll use the names in a For Each Loop that will assign them to check boxes
to allow the users to select which colums they would like to see.

EX. qryExcelDump has column names: ProjectID, CustomerID, OrderDate, etc.
I would like to query the Query's column names and assign them to Check1,
Check2, etc, similar to what the SwitchBoard editor in Access does to
populate the buttons.
 
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("YourQuery")
Dim fld As Field
For Each fld In qdf.Fields
'do whatever with fld.name
Next
 
This function will list all the Fields in a query. You'll have to have a
reference set to DAO (which you should already), and you'd use it like this:

ListQueryFields "YourQueryName"

Note that it now simply prints the names of the fields to the Debug window;
you could do whatever you wish here, but this shows you basic looping syntax
to retrieve the field names for a particular query.

'/****************** Code Start *******************
Function ListQueryFields(QueryName As String) As Boolean

'/Purpose:
'/Created: 3/22/2005 07:21 PM
'/Created By: Scott

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim fld As DAO.Field

On Error GoTo Err_ListQueryFields
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(QueryName)

For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld

Exit_ListQueryFields:
On Error Resume Next
Set qdf = Nothing
Set dbs = Nothing
Set fld = Nothing
Exit Function
Err_ListQueryFields:
Select Case Err
'case
Case Else
MsgBox Err & ":" & Error$, vbCritical, "Module1" & ": " &
"ListQueryFields"
End Select

Resume Exit_ListQueryFields

End Function
 
Yes, that did it. Thanks!
Eric Easley

Rob Oldfield said:
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("YourQuery")
Dim fld As Field
For Each fld In qdf.Fields
'do whatever with fld.name
Next
Click to expand...
 
Back
Top