Sorting does not work in subform

[Ducati]

Hi all,

I have something strange going on here.

I created a continuous form whereby the labels on top have a click-event to sort the data.

SortForm Me, "usrModificationDate", Me.lblModificationDate, Me.imgUp, Me.imgDown

My sort procedure is as follows:

Public Sub SortForm(frm As Form, _
strOrderBy As String, _
ctlHeader As Control, _
imgUp As Control, _
imgDown As Control)
On Error Resume Next

Dim strStyle As String

If Not IsNothing(strOrderBy) > 0 Then
If frm.OrderByOn And (frm.OrderBy = strOrderBy) Then
strOrderBy = strOrderBy & " DESC"
strStyle = "D"
End If
If ctlHeader.TextAlign = 3 Then
imgUp.Left = ctlHeader.Left + 100
imgDown.Left = ctlHeader.Left + 100
Else
imgUp.Left = ctlHeader.Left + ctlHeader.Width - imgUp.Width - 100
imgDown.Left = ctlHeader.Left + ctlHeader.Width - imgDown.Width - 100
End If
If strStyle = "D" Then
imgUp.Visible = False
imgDown.Visible = True
Else
imgUp.Visible = True
imgDown.Visible = False
End If
frm.OrderBy = strOrderBy
frm.OrderByOn = True
End If

End Sub

This works perfect when I open the form and click the labels. It switches nicely between ascending and descending, every label click sorts on the corresponding field.

But now it use this form as a subform in another form. And there, for some reason, it only works for my first click on the (subform) header. Any click afterwards fires the code, but the form does not get resorted.

Anybody any ideas on what I am doing wrong here?

Regards,

Mike

 

[vbaInet]

Welcome to the forum.

To diagnose this kind of problem it might be easier to see the problem forms. Can we see a stripped down version of your db? Removing sensitive data of course and putting some dummy data for testing.

 

[Ducati]

Found it. Don't know why but if you switch off sorting before assigning a new sort field, it works (see below).

Public Sub SortForm(frm As Form, _
strOrderBy As String, _
ctlHeader As Control, _
imgUp As Control, _
imgDown As Control)
On Error Resume Next

Dim strStyle As String

If Not IsNothing(strOrderBy) > 0 Then
If frm.OrderByOn And (frm.OrderBy = strOrderBy) Then
strOrderBy = strOrderBy & " DESC"
strStyle = "D"
End If
If ctlHeader.TextAlign = 3 Then
imgUp.Left = ctlHeader.Left + 100
imgDown.Left = ctlHeader.Left + 100
Else
imgUp.Left = ctlHeader.Left + ctlHeader.Width - imgUp.Width - 100
imgDown.Left = ctlHeader.Left + ctlHeader.Width - imgDown.Width - 100
End If
If strStyle = "D" Then
imgUp.Visible = False
imgDown.Visible = True
Else
imgUp.Visible = True
imgDown.Visible = False
End If
frm.OrderByOn = False
frm.OrderBy = strOrderBy
frm.OrderByOn = True
End If

End Sub

 

[vbaInet]

Alright. Either that or a requery after applying the sort.

 

[Ducati]

Re-querying was not an option as the subform is based upon a disconnected recordset.

 

[vbaInet]

When you say disconnected, are you talking about in terms of ADO disconnected recordsets or do you just mean the record source of the subform is dynamic?

 

[Ducati]

I mean DAO-disconnected.

 

[vbaInet]

Alrighty, I see.