Get Column Names from an existing query - Access 2003

E

Erice

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

Rob Oldfield

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
 
S

Scott McDaniel

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
 
E

Erice

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