How to sort data bound grid control

pawan-agr
pawan-agr used Ask the Experts™
on
My data bound grid control has data control as the data source.
I have assigned a table name in the record source of the data control.

I have following code in the form activate event
Data1.Recordset.Sort = "Name"

but when the form is loaded, grid doesn't show any changes and the Name column is still in unsorted form.

Now i again entered following codes in the form activate event
Data1.RecordSource = "SELECT * FROM TableName ORDER BY Name"
Data1.Refresh

However it solved my problem a new problem arised.
When I add new record to that table then the data bound grid control doesn't update the grid as it was updating while i selected TableName as the recordsource of data control instead of SQL statement.

The grid can be updated by Data1.Refresh method but this change the bookmark of the recordset. The current cell should be at the same row when it was before adding the data.

Please solve my problem so that the grid contain sorted data and the current row of the grid should not change while new data is added.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dirk HaestProject manager

Commented:
Before you refresh it:
Position = Data1.Recordset.AbsolutePosition
Data1.RecordSource = "SELECT * FROM TableName ORDER BY Name"
Data1.Refresh
data1.recordset.absoluteposition = position

That should do it

Author

Commented:
Absolute positon will give positon of current row. It don't tell where the row is gone after adding or deleting the data from table. For eg. If a name "Tom" is in row 4 before adding new data then it could be shifted to row 5 after adding new data. I want to position the cell to row 5 not 4.

Saving the index of the current row in a variable and then finding for it after adding new row can solve my problem but its an inefficient way. Please let me now if anyone have better idea to do it.

Project manager
Commented:
Please consider this:
You have five choices when it comes to closing your open questions:

Accept an Expert's Comment as the Answer
Accept Comments from more than one Expert (a Split)
Request a refund because you answered your own question (Refund/PAQ)
Request a refund because no one answered your question (Delete)
Abandon your question and leave the mess for someone else to clean up --> against member guidelines.

SO PLEASE CLOSE THIS QUESTION SO THAT THE MODERATERS HAVE MORE TIME TO ANSWER QUESTIONS RATHER THEN CLOSING ABONDANDED QUESTIONS
pawan-agr,
No comment has been added lately (52 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to Dhaest http:#7467766

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

cerebralpc
EE Cleanup Volunteer

http://www.experts-exchange.com/Q_20401919.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial