Hi Steve,
I’ve spent some (far too much!) more time on this issue & have now got all the additional properties working using VBA EXCEPT for alternate row colours!
For simplicity, I’m using a function SetPropertyDAO from Allen Browne’s website to create or assign a property value as this includes all the error code needed to trap error 3270 where the property doesn’t exist
For example:
If tdf.Name = "tblMessageLog" Then
Select Case fld.Name
Case "Cost"
Call SetPropertyDAO(fld, "DecimalPlaces", dbInteger, 4)
Case "TimeTaken"
Call SetPropertyDAO(fld, "Format", dbText, "Long Time")
End Select
End If
However, alternate row colours has still not been cracked…
I’ve found that setting this using the ribbon adds a total of 23 properties:
Group A – properties used to define the row colours
DatasheetAlternateBackColor
DatasheetBackColor
AlternateBackThemeColorIndex
AlternateBackTint
AlternateBackShade
DatasheetBackColor12
BackThemeColorIndex
BackTint
BackShade
ThemeFontIndex
DatasheetGridlinesThemeColorIndex
DatasheetForeThemeColorIndex
Group B – properties which seem to be unrelated but also created
PublishToWeb
Orientation
OrderByOn
DefaultView
DisplayViewsOnSharePointSite
TotalsRow
FilterOnLoad
OrderByOnLoad
HideNewField
Group C – complex properties (see below)
GUID
NameMap
---------------------------------------------------------------------------------
The code below successfully creates the properties in groups A & B :
Call SetPropertyDAO(tdf, "DatasheetAlternateBackColor", dbText, RGB(205, 220, 175))
Call SetPropertyDAO(tdf, "DatasheetBackColor", dbText, RGB(255, 255, 255))
Call SetPropertyDAO(tdf, "AlternateBackThemeColorIndex", dbInteger, -1) '6
Call SetPropertyDAO(tdf, "AlternateBackTint", dbInteger, 100) '60
Call SetPropertyDAO(tdf, "AlternateBackShade", dbInteger, 100)
Call SetPropertyDAO(tdf, "DatasheetBackColor12", dbLong, -2147483643)
Call SetPropertyDAO(tdf, "BackThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "BackTint", dbInteger, 100)
Call SetPropertyDAO(tdf, "BackShade", dbInteger, 100)
Call SetPropertyDAO(tdf, "ThemeFontIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "DatasheetGridlinesThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "DatasheetForeThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "PublishToWeb", dbInteger, 1)
Call SetPropertyDAO(tdf, "Orientation", dbInteger, 0)
Call SetPropertyDAO(tdf, "OrderByOn", dbBoolean, False)
Call SetPropertyDAO(tdf, "DefaultView", dbInteger, 2)
Call SetPropertyDAO(tdf, "DisplayViewsOnSharePointSite", dbInteger, 1)
Call SetPropertyDAO(tdf, "TotalsRow", dbBoolean, False)
Call SetPropertyDAO(tdf, "FilterOnLoad", dbBoolean, False)
Call SetPropertyDAO(tdf, "OrderByOnLoad", dbBoolean, True)
Call SetPropertyDAO(tdf, "HideNewField", dbBoolean, False)
However, the alternate colours still aren’t displayed
Next I looked at the Group C properties GUID (dbGUID) & NameMap (dbLongBinary).
The problem is knowing what values to enter for each table
This is made even harder by the fact that the GUID value changes every time a table is relinked!
Where they exist, the two complex field values are found in the system table MSysNameMap
I found that assigning many other table properties such as setting the row height = default also populated the GUID & NameMap fields as well as most of the other properties listed above.So I did set this property first using:
Call SetPropertyDAO(tdf, "RowHeight", dbText, "Default")
I then grabbed the output using a query to assign the property values
strName = tdf.Name
strSelection = "{guid " & Nz(DLookup("GUID", "qrySDATablesList", "ObjectName = ‘” & strName & “'"), "") & "}"
Call SetPropertyDAO(tdf, "GUID", dbGUID, strSelection)
strSelection = Nz(DLookup("NameMap", "qrySDATablesList", "ObjectName = ‘” & strName & “'"), "") & "}"
Call SetPropertyDAO(tdf, "NameMap", dbLongBinary, strSelection)
I now had all 23 properties for each table apparently identical to those produced when creating the alternate row colours using the ribbon
And guess what – IT STILL DOESN’T WORK …..
By this point, I’m losing the will to live
I’m passing this on in case it helps you or anyone can solve the problem
Colin