Hello, I have a database where there is a listbox (lstFields) whose record source is qryAll. I've attached the code for building this listbox and also the database to this question, but basically the list box is using captions rather than field names. I have a VBA script that exports the selections to word (it used to when the field names were present but now that I've changed the selections to captions I can't seem to get it to work) and I would like to allow users to do the same for an excel spreadsheet.
Could anyone show me an example of how to get the selected fields w/ caption property shown to export?
Option Compare Database
Private Sub Command0_Click()
Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)
For Each myfield In rs.Fields
FinalString = FinalString & Nz(myfield.Properties("Caption"), "no caption") & ";"
Me.lstFields.RowSource = FinalString
If Err.Number = 3270 Then 'no existing caption
FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"