Hello,

After a few times executing this sub I get the message 'Error 3014: Can't
open any more tables'. Can somebody tell what is wrong with my code? (The
recordset of the data-control is initialized in the Form_load event)

Public Sub Sort(Fld As String)
   Me.DatContact.Recordset.Sort = Fld
   Set Me.DatContact.Recordset = Me.DatContact.Recordset.OpenRecordset()
End Sub

Thanks,

Davy

Davy,

I didn't see in your code where you closed this recordset.  If you don't, that
could be your problem.  Before the sub ends, close the recordset.  I just
finished a large project and didn't bother closing any recordsets that I
opened.  This was a big overlook on my part.  This was my first VB project, and
I just assumed the recordsets would close by themselves.  This isn't so.  You
must release the resources that you allocate (i.e. recordsets, databases, etc.)
or your system resources will be robbed from you and your users will get errors
like this one. (Mine did).  After I closed each database and each recordset
that I opened, the error went away!!  Also, try to keep the number of data
controls on one form to a minimum.  I was running each form with 5 data
controls.  Apparently each data control maintains a separate connection to the
database.  This could also be contributing to your problem.

Hope this helps,
Terry

I had the same problem, my solution is below - although this is more complex
than your requirements you might find it useful as it will accept any data
control and field no (from a grid head click event ) and sort remembering
the direction and reversing it each time.

In essence you need to use the refresh  method on the data control before
using the openrecordset method.

Don't ask me why you need to do this though!

Public Sub SortGrid(DC As Data, ColIndex As Integer)
Dim Direction As String
DC.Refresh 'avoid error 3014
If ColIndex = Val(DC.Tag) Then
   If InStr(DC.Tag, "DESC") Then
        Direction = " ASC"
    Else
        Direction = " DESC"
    End If
Else
    Direction = " ASC"
End If
DC.Recordset.Sort = DC.Recordset.Fields(ColIndex).Name & Direction
Set DC.Recordset = DC.Recordset.OpenRecordset
DC.Tag = ColIndex & Direction 'remember settings
End Sub