Change Query Field Names on Export (1 Viewer)

alsoascientist · Apr 3, 2012

Hi All,

I have some vba code that creates a temporary query and exports this to Excel. This issue that I now have is that there is now a requirement to make each of the field names / labels etc variable and listed in a master table (tblFieldNames). This is working fine for everything I'm doing apart from when I export the data this shows the StaticName (which is what each field is actually called) and I would like to show the FriendlyName which appears in each of the labels. tblFieldNames allows for a dlookup that I am using for everything else and I am hoping to either use the Caption property or Alilas for the field queries, though I have never used these before.

I have slipped a couple of lines into the code that I am using, however I don't seem to be able to get the right arguements to set the caption value. Has anyone got any experience of this that could see where I am going wrong?

I have this so far:

Code:

Private Sub ExportBtn_Click()
On Error GoTo errHandler
 
'Takes the information to build the recordsource from the previous functions (bulit as SELECT / FROM / WHERE)
rept = BuildRept & " FROM " & "AllQuery" & BuildFilter
 
'Creates the query
DoCmd.DeleteObject acQuery, "qryTemp"
Set qdf = CurrentDb.CreateQueryDef("qryTemp", rept)
    Dim fld As Field
 
'This is the part I hoped would change the names 
For Each fld In qdf.Fields
        fld.Caption = (DLookup("FriendlyName", "tblFieldNames", "SystemFieldName = '" & fld.Name & "'"))
    Next
 
'Exports the query
DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, , True
exitHandler:
Exit Sub
errHandler:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " - " & Err.Description
Resume exitHandler
End If
End Sub

alsoascientist · Apr 4, 2012

So I figured it out and there is nothing more annoying when I've been searching for this than to find my exact issue only to realise that there has been no response to the previous person asking, so here it is:

replace that part I said was supposed to work with this:

Code:

'Sets the field names as the FriendlyNames
For Each fld In qdf.Fields
Set prp = fld.CreateProperty("Caption", dbText, (DLookup("FriendlyName", "FieldNames", "SystemFieldName ='" & fld.Name & "'")))
fld.Properties.Append prp
Next

This creates the property and then sets it from the look up table (I didn't realise that you have to create optional properties before you set them!)

Change Query Field Names on Export (1 Viewer)

alsoascientist

Registered User.

alsoascientist

Registered User.

Similar threads

Users who are viewing this thread