Set OrderBy property of a table using VBA?

[MackC]

Is there any way to set the OrderBy property of a table using VBA?

 

[TonyJollans]

Hi MackC,

This should do it ..

Dim db as dao.database
dim tbl as dao.table
dim prop as dao.property

set db = currentdb
set tbl = db.tabledefs("

YourTable

")
set prop = tbl.Properties("OrderBy")

prop.Value = "

YourSortField

"

Enjoy,
Tony

 

[MackC]

When I compile this code, I get the message "user defined type not defined" with this line highlighted:
Dim tbl As DAO.Table

 

[TonyJollans]

Hi MackC,

Sorry, my mistake. It should be DAO.TableDef

Enjoy,
Tony

 

[MackC]

Now when I compile, it gives me the message, "property (Orderby) not found," which is what I had been getting before everytime I tried to change this property. It seems that Access is not making the property available to VBA.

 

[TonyJollans]

Hi MackC,

The OrderBy (and OrderByOn) Properties are created the first time you sort a Table. If you have never done it, they won't exist and you will need to create them before you can give them values. The following routine wil check if they exist and, if not, will create them with default values, after which you can set them as you wish.

Sub CreateProps()

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjProp As DAO.Property

Dim existsOrderBy As Boolean
Dim existsOrderByOn As Boolean

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!

YourTable

For Each tjProp In tjTab.Properties
    If tjProp.Name = "OrderBy" Then existsOrderBy = True
    If tjProp.Name = "OrderByOn" Then existsOrderByOn = True
Next

If Not existsOrderBy Then
    Set tjProp = tjTab.CreateProperty("OrderBy", dbMemo)
    tjProp.Value = tjTab.Fields(1).Name
    tjTab.Properties.Append tjProp
End If

If Not existsOrderByOn Then
    Set tjProp = tjTab.CreateProperty("OrderByOn", dbBoolean)
    tjProp.Value = False
    tjTab.Properties.Append tjProp
End If

tjDb.Close

Set tjProp = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

End Sub

Enjoy,
Tony

 

[MackC]

That worked! Thanks