Continous Form- How to retain sort order and cursor position after edit

[SorboPos]

Hi.

I have a form with continuous forms and a data entry section in the
header of the form. (I.e. all transactions show in the main form area
like a data sheet and the data for the highlighed tran is entered or
edited in the header.)

Record source for the form is a query that is sorted by tran date.

Records are sorted by date of transaction ascending.

MY PROBLEM:

When I enter a new transaction or change the date in an existing
transaction, I cannot get the transactions to resort themselves into
the proper order.

It is important that they be in date order as I have a running total of
the balance of funds on hand in the right most column.

The only way I have been able to get the records to resort is to close
and reopen the form OR requery the form. Problem with requerying the
form is that it moves the cursor off the current record on to an
arbitrary record.

I am sure this is SIMPLE, but I am stuck! What I need is a way to keep
the records sorted correctly while keeping the cursor on the same
record. Using bookmarks won't work because you lose bookmarks when you
requery a recordset.

Keith

[DFS]

So******@gmail.com wrote:

Hi.

I have a form with continuous forms and a data entry section in the
header of the form. (I.e. all transactions show in the main form area
like a data sheet and the data for the highlighed tran is entered or
edited in the header.)
Is there a reason you're not editing the data directly in the main form
area?

Record source for the form is a query that is sorted by tran date.

Records are sorted by date of transaction ascending.

MY PROBLEM:

When I enter a new transaction or change the date in an existing
transaction, I cannot get the transactions to resort themselves into
the proper order.

It is important that they be in date order as I have a running total
of the balance of funds on hand in the right most column.

The only way I have been able to get the records to resort is to close
and reopen the form OR requery the form. Problem with requerying the
form is that it moves the cursor off the current record on to an
arbitrary record.

I am sure this is SIMPLE, but I am stuck! What I need is a way to keep
the records sorted correctly while keeping the cursor on the same
record. Using bookmarks won't work because you lose bookmarks when you
requery a recordset.
It's pretty simple. I've faced it myself with continuous forms, when
inserting or updating, though not exactly as you describe.

One fix I used was to record the ID of the current record, do the insert or
update, issue the requery, then use a little code to find the ID.

Assuming you have button(s) to add new or update existing records:

Public Sub btnAddUpdate()

'GET ID VALUE OF RECORD BEING EDITED
dim recordID as long
recordID = me.subForm.recordID

'... execute the update or insert
'FIND THE RECORD YOU WERE EDITING
Me.subForm.SetFocus
DoCmd.GoToControl "ID_Field"
DoCmd.FindRecord recordID, acEntire, , acSearchAll, , acCurrent
DoCmd.RunCommand acCmdSelectRecord

End Sub
Keith