Merlin-Eng
asked on
Binding recordsets to a form
Access 2010: This is a follow on from a question I asked last week. I was finding linked tabledefs quite slow, when using them as the recordsource for a form. So I was shown how to open an ADO recordset and bind it to the form.
This approach works well. However I have a form which is used as a search screen. It displays a set of records, and re-orders the list according to which column the user moves the focus into. When I was using a linked tabledef as the recordsource, I could use the form's OrderBy property to set a different sort order, then I would toggle the OrderByOn property to false and then true again, in order to apply the new sort order. It seems that I am not allowed to do this when using a bound recordset. It gives me runtime error 2162. Is there a different approach I could use here apart from binding a differently ordered recordset?
Ian
This approach works well. However I have a form which is used as a search screen. It displays a set of records, and re-orders the list according to which column the user moves the focus into. When I was using a linked tabledef as the recordsource, I could use the form's OrderBy property to set a different sort order, then I would toggle the OrderByOn property to false and then true again, in order to apply the new sort order. It seems that I am not allowed to do this when using a bound recordset. It gives me runtime error 2162. Is there a different approach I could use here apart from binding a differently ordered recordset?
Ian
ASKER
To bind the recordset
With MyConnection
.Provider = "Microsoft.ace.OLEDB.12.0"
.ConnectionString = "\\avatar\jobs\Stock.accdb "
.Open
End With
With MyRecordset
.ActiveConnection = MyConnection
.Source = Criteria
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = MyRecordset
Set MyRecordset = nothing
Set MyConnection = Nothing
To apply a sort order:
Me.OrderBy = "Whatever"
Me.OrderByOn = False
Me.OrderByOn = True
With MyConnection
.Provider = "Microsoft.ace.OLEDB.12.0"
.ConnectionString = "\\avatar\jobs\Stock.accdb
.Open
End With
With MyRecordset
.ActiveConnection = MyConnection
.Source = Criteria
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = MyRecordset
Set MyRecordset = nothing
Set MyConnection = Nothing
To apply a sort order:
Me.OrderBy = "Whatever"
Me.OrderByOn = False
Me.OrderByOn = True
Try this:
.CursorLocation = adUseClient
.CursorLocation = adUseClient
ASKER
I changed the CursorLocation to adUseClient. It still gives an error at the Me.OrdbyOn=True
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah ok. In that case I think for a quiet life i'm going to give up and go back to my linked tabledefs. Eventually i'll migrate all the tables into SQL Server. So i'll be able to use DSN-less queries. So the problem will disappear. Thanks for your help.
Ian
Ian
Can you also show the code you're using to set the sort order?