Query Field Names AND Captions Rowsource for ListBox

[Debra Farnham]

Hi All

Access 2K

With the help of a post by Terry Kreft, I am able to get the Captions from a
Query to show up in my listbox as follows:

I also need the the field names.

Any help would be GREATLY appreciated.

Debra

******************************************************************
Function ListCaptions(fld As Control, id As Variant, row As Variant, col As
_
Variant, code As Variant) As Variant
Static strCaptions() As String
Static intCount As Integer
Select Case code
Case acLBInitialize ' Initialize.
Dim loDb As Database
Dim loTab As QueryDef
Dim lofld As Field
Dim intX As Integer
Set loDb = CurrentDb
'Change the table name here
Set loTab = loDb.QueryDefs("qryFieldsForExport")
intCount = loTab.Fields.Count - 1
ReDim strCaptions(intCount)
On Error Resume Next
For intX = 0 To intCount
strCaptions(intX) = loTab.Fields(intX).Properties("Caption")
If Err > 0 Then
strCaptions(intX) = loTab.Fields(intX).Name
Err.Clear
End If
Next
Set loTab = Nothing
Set loDb = Nothing
ListCaptions = True
Case acLBOpen ' Open.
ListCaptions = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListCaptions = intCount + 1
Case acLBGetColumnCount ' Get columns.
ListCaptions = 1
Case acLBGetColumnWidth ' Get column width.
ListCaptions = -1 ' Use default width.
Case acLBGetValue ' Get the data.
ListCaptions = strCaptions(row)
End Select

 

[Douglas J Steele]

The following should do it:


Function ListCaptions(fld As Control, id As Variant, row As Variant, col As
_
Variant, code As Variant) As Variant
Static strCaptions() As String
Static intCount As Integer
Select Case code
Case acLBInitialize ' Initialize.
Dim loDb As Database
Dim loTab As QueryDef
Dim lofld As Field
Dim intX As Integer
Set loDb = CurrentDb
'Change the table name here
Set loTab = loDb.QueryDefs("qryFieldsForExport")
intCount = loTab.Fields.Count - 1
ReDim strCaptions(1, intCount)
On Error Resume Next
For intX = 0 To intCount
strCaptions(0, intX) = loTab.Fields(intX).Name
strCaptions(1, intX) = loTab.Fields(intX).Properties("Caption")
If Err > 0 Then
strCaptions(1, intX) = " No Caption "
Err.Clear
End If
Next
Set loTab = Nothing
Set loDb = Nothing
ListCaptions = True
Case acLBOpen ' Open.
ListCaptions = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListCaptions = intCount + 1
Case acLBGetColumnCount ' Get columns.
ListCaptions = 2
Case acLBGetColumnWidth ' Get column width.
ListCaptions = -1 ' Use default width.
Case acLBGetValue ' Get the data.
ListCaptions = strCaptions(col, row)
End Select

 

[Debra Farnham]

Awesome!

Thanks tons Doug!

Works like a charm.

Debra