[AccessD] Set the caption property

[Bill Benson]

I used Allan Brownes code which was purdy much like mine just more
versatile and it did not work either. Ended up setting all the captions by
hand. Grrr.

On Mon, May 23, 2016 at 2:08 PM, Bill Benson <bensonforums at gmail.com> wrote:

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