Sticky Form.OrderBy value

[mlesniak001]

Hi everyone,

Here is a confusing one:

I have a form, which is based (indirectly) on a crosstab query. This means that the resulting number of columns and thier names are different for each load of the form. I have worked thru almost all the issues of handling the dynamic columns (upto a limit). But now I am stuck on a problem with the OrderBy.

my basic process is:
- on load of the form:
-- get the current set of columns,
-- re-assign the data source to a set of column place holders
-- build a string with the desired OrderBy
-- set OrderByOn = true

- on close of the form:
-- set OrderByOn = False
-- set OrderBy = ""

The problem is that the value of SortBy is not being cleared, so that on the next load of the form, it is trying to use the last SortBy (often resulting in a error, when the column names happen to not be the same as last time).

Any ideas on why the VBA in Close is not working?

FYI, here is part of the code:

Private Sub Form_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim strName As String
    Dim strSQL As String
    Dim Length As Integer
    Dim Position As Integer
    Dim OK As String

    Dim temp_order_by As String
    temp_order_by = ""
    
    Dim Exclude(1 To 2) As String
    Exclude(1) = "SetID"
    Exclude(2) = "Instance"

    Dim db As Database
    Dim rs As Recordset
    Dim q1 As QueryDef
    
    Set q1 = db.QueryDefs("q_set_instances_Crosstab")
    
    q1.Parameters("_A_") = _A_ 'removed object names for brevity
    Set rs = q1.OpenRecordset
    intColCount = rs.Fields.Count
    intControlCount = Me.Detail.Controls.Count
    ' Fill in information for the necessary controls.
    k = 1
    For i = 0 To intColCount - 1
        'check if column should be excluded
        OK = "Yes"
        For j = LBound(Exclude) To UBound(Exclude)
            If Exclude(j) = rs.Fields(i).Name Then
                OK = "No"
            End If
        Next j
        
        Debug.Print OK, rs.Fields(i).Name
        
                
        If OK = "Yes" Then

            strName = rs.Fields(i).Name
'           MsgBox strName
            Me.Controls("Dim" & k).ControlSource = strName
            Me.Controls("Txt" & k).Caption = strName
            Me.Controls("Dim" & k).ColumnHidden = False
        
            If temp_order_by <> "" Then
                temp_order_by = temp_order_by & ", " & Me.RecordSource & ".[" & strName & "]"
            Else
                temp_order_by = Me.RecordSource & ".[" & strName & "]"
            End If
        
            k = k + 1
        End If
    Next i
    
    While k <= 8
        Me.Controls("Dim" & k).ControlSource = ""
        Me.Controls("Txt" & k).Caption = ""
        Me.Controls("Dim" & k).ColumnHidden = True
        k = k + 1
    Wend
    
    Debug.Print temp_order_by
    Me.OrderBy = temp_order_by
    Me.OrderByOn = True
End Sub

Private Sub Form_Close()
    Me.OrderByOn = False
    Me.OrderBy = ""
    Debug.Print "cleared order_by"
    Debug.Print Me.OrderBy, Me.OrderByOn
    
End Sub

the funny thing is:
- if I remove the action Me.OrderByOn = True, then the value of OrderBy is properly set to "" in Close. but of course, the OrderBy then never occurs on the form...

any ideas on why OrderBy is being 'sticky'? By 'sticky', I mean that, when the form is opened in design view, the Order By value of the last run is still there, when it should be empty. agian, this does not happen when I do not set OrderByOn = True.

 

[Flippertje]

I'm not sure but did you try the form_unload in stead of close?

 

[mlesniak001]

yep, I did try it with the code in "On Unload", but it still didn't work (the code ran, but Access still saved the old value).

however, I did come-up with a work around. before I open the form, I edit to clear out the OrderBy value. the code, which is in a button that opens the form, is as follows:

Private Sub View_Instances_Click()
    '---- edit the form to clear the sticky OrderBy value ----
    DoCmd.OpenForm "subform_set_instances_Crosstab", acDesign, , , , acHidden
("subform_set_instances_Crosstab", acDesign)
    Forms![subform_set_instances_Crosstab].OrderBy = ""
    Forms![subform_set_instances_Crosstab].OrderByOn = False
    DoCmd.Close acForm, "subform_set_instances_Crosstab", acSaveYes
    
    '----- actually open the form for user viewing -----
    DoCmd.OpenForm "subform_set_instances_Crosstab", acFormDS, , , acFormEdit, acWindowNormal
    
End Sub

this is a brute-force method, and I do not recommend it. but it does work.

 

[mlesniak001]

oh, by the way, although the forms name is "subform_...." THIS IS NOT USED AS A SUBFORM.

That was my orginal intent, but MS does NOT ALLOW SUBFORMS TO BE BASED ON A CROSSTAB QUERY. so instead, I use it as a pop-up. not ideal.