[AccessD] Set the caption property

[Bill Benson]

BTW, tried to post this on Access-L and it was rejected by the "content
filter"

Having trouble with some fields in a query that pull against a sharepoint
list (2 actually, there are multiple joins). First, the query name does not
have values like TableX.FieldName, but the column headers always seem to.
This persists even if I alias the field name. So I thought (as I have seen
necessary with form controls) that I ought to use the Caption property of
the field.

As there are so many fields, I have tried to create and use a Caption
property for each field that is the same as the name, or any text to the
right of the name, using the code below. It seems like the Caption property
is read/write within the routine below, but making the new value "stick"..
well that is a problem. I don't see a Save method or the like. Can someone
help me modify the code to be more effective? It is a real pain to try to
do this manually and I think I will have many such queries I wish to do
this for.

Sub SetCaptions()
Dim Q As DAO.QueryDef
Dim fld As DAO.Field
Dim P As DAO.Property
Dim P1 As DAO.Property
Dim i As Long
Set Q = CurrentDb.QueryDefs("qry_Updates_Report")

For Each fld In Q.Fields
    On Error Resume Next
    Set P = Nothing
    Set P = fld.Properties("Caption")
    If P Is Nothing Then
        Set P = fld.CreateProperty("Caption", dbText, "Temp")
        fld.Properties.Append P
        FLD.Properties.Refresh
    End If
    i = 0
    For Each P1 In fld.Properties
        i = i + 1
        If i < 10 And P1.Name = "Name" Then
            If InStr(P1.Value, ".") > 0 Then
                P.Value = Mid(P1.Value, InStr(P1.Value, ".") + 1)
            Else
                P.Value = P1.Value
            End If
            fld.Properties.Refresh
            Exit For
        End If
    Next
Next


End Sub