Tab order greyed out (1 Viewer)

[Kayleigh]

Hi
Is there any reason that tab order is greyed out for a form in Datasheet format.
My real problem is that I can't get the columns to display in a different order so I thought tab order would help me.
Any thoughts please

 

[theDBguy]

I suppose Tab Order is grayed out in datasheet form because it's probably ignored anyway. However, you can rearrange the columns by dragging them around.

 

[Kayleigh]

Whenever I do that it doesn't save when re-open form.

 

[theDBguy]

Whenever I do that it doesn't save when re-open form.

Yes. There's a technique to it, I think. I'll give it a try when I get in front of a computer and let you know.

 

[bastanu]

I use the following code to set the order and visibility of datasheet forms for each individual copy of the front-end (the settings are stored in the local registry). This is providing users the ability to set up their own preferred order and maintain that during front-end updates.
Add this to a standard module:

Option Compare Database
Option Explicit

Public Sub LoadUserColumnSetup(ByRef frm As Form)
    Dim Ctl As Control
    Dim strBlob As String
    Dim strColumns() As String
    Dim intColumns As Integer
    Dim intColumn As Integer
    Dim strValues() As String
    
    On Error Resume Next
    
    ' Only apply to forms in datasheet view.
    Const cDatasheetView As Long = 2
    If frm.CurrentView <> cDatasheetView Then Exit Sub
    
    strBlob = GetSetting("YourDB_Columns", "Settings", frm.Name, "")

    If strBlob <> "" Then
      Call GetOrderedColumns(strBlob, strColumns)
    
      intColumns = UBound(strColumns) + 1
      If intColumns <> 0 Then
        For intColumn = 0 To intColumns - 1
          If Trim(strColumns(intColumn)) <> "" Then
             strValues = Split(strColumns(intColumn), ":")
             Set Ctl = frm.Controls(strValues(0))
             Ctl.ColumnOrder = CInt(strValues(1))
             Ctl.ColumnHidden = CBool(strValues(2))
             Ctl.ColumnWidth = CLng(strValues(3))
          End If
        Next
      End If
    End If

End Sub

Private Sub GetOrderedColumns(ByVal strData As String, _
                              ByRef strColumns() As String)
    Dim strTemp() As String
    Dim intCols As Integer
    Dim intCol As Integer
    Dim intCurr As Integer
    Dim strValues() As String
    
    On Error Resume Next

    strTemp = Split(strData, vbCrLf)
    intCols = UBound(strTemp) - 1
    
    ReDim strColumns(intCols)
    For intCol = 0 To intCols
        For intCurr = 0 To intCols
            strValues = Split(strTemp(intCurr), ":")
            If CInt(strValues(1)) = intCol + 1 Then
                strColumns(intCol) = strTemp(intCurr)
                Exit For
            End If
        Next
    Next
    
End Sub

Public Sub SaveUserColumnSetup(ByRef frm As Form)
    Dim Ctl As Control
    Dim strBlob As String
    Dim strCtl As String
    
    On Error Resume Next

    ' Only apply to forms in datasheet view.
    Const cDatasheetView As Long = 2
    If frm.CurrentView <> cDatasheetView Then Exit Sub
    
    For Each Ctl In frm.Controls
      Select Case Ctl.ControlType
        Case acLabel, acLine, acSubform, acCommandButton
            ' do nothing for these controls.
        Case Else
          strCtl = Ctl.Name & ":" & _
                   Ctl.ColumnOrder & ":" & _
                   Ctl.ColumnHidden & ":" & _
                   Ctl.ColumnWidth & vbCrLf
          strBlob = strBlob & strCtl
      End Select
    Next
    
    SaveSetting "YourDB_Columns", "Settings", frm.Name, strBlob

End Sub

Now to each datasheet form you want to control with the above code simply add these lines:

Private Sub Form_Load()
    Call LoadUserColumnSetup(Me)
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Call SaveUserColumnSetup(Me)
End Sub

Cheers,