Re-Apply Sort After New Record

[mjoachim]

I have a form that loads with all data currently in an employee table. I am trying to allow the user to duplicate a record and then have the form re-apply the sort so that the newly created record is in order with the rest rather than being at the bottom of the form.

Here is my current copy/paste code:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste

I have tried adding these to my code to complete the sort, but they haven't worked:

Me.OrderBy "Employee Number"
Me.OrderByOn

DoCmd.SetOrderBy "Employee Number"

I have also tried the usual me.requery and me.refresh. What am I missing?? How can I have the form re-sort the data after adding the new record?

Also, any suggestions on a way to set/keep focus on the newly added record after the sort is complete? I have a unique record ID field available.

[zmbd]

If your sort is already set in the form properties then
try me.Requery
After the user enters the new record. This can either be in your code that handles the new record entry or in the form's Form_AfterUpdate() event.

Note, the me.Refresh may not be enough depending on how your code/query is setup because the form basically takes a snapshot of the associated dataset and the refresh only updates the existing records as they are shown in the form. The requery forces the form to go back to the database and take a new look at the dataset.MS Article: Refresh or requery data

[mjoachim]

Adding me.requery after my new record code doesn't work.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
 
Me.Requery

The new record is still at the bottom of the form.

[zmbd]

Is your sort order set in the form or by VBA

Have you post your entire "new record" code?

Explicitly set the sort order on prior to the requery:
Me.OrderByOn = True

[mjoachim]

The sort order is set in the form properties. I stand partially corrected though as I now have it working using me.requery. Above is the entire code for Copy Record button and it wasn't working initially, but I cleared out a bunch of unnecessary code in other areas of the form that may have been conflicting with it and now it works.

Now, though, regardless of which record I am on when I click my copy button, the top record has the focus afterword. Suggestions on how to bring focus back to the new record? Would using something like DoCmd.GoToRecord work?

[zmbd]

I'm glad that things are working, and yes, it's not uncommon for stray code to cause some unintended effects.

We do normally ask that each thread have a single question; however, we may be able to stretch this a tad :)

When you ask "...bring focus back to the new record?" do you mean to move the record index to the "New Record" portion of the form or do you mean to move the index to the newly created record?

The first is quite straight forward:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
This will move the index to the row that typically has the "(New)" value for a field with "autonumber" as the datatype.


The second will take a bit more code and would need to have a bit more complex code and would use the "...unique record ID..." you mentioned earlier.

[twinnyfo]

@zmbd:
The first is quite straight forward:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
This will move the index to the row that typically has the "(New)" value for a field with "autonumber" as the datatype.

I believe this method will take the form to a "new" record, not just the "newest" record. Since we have requeried the form, the recordset is now set and that record just created is no longer a new record.

The alternate solution, to use the unique record ID, is the way to go. You will have to determine that uniqe record ID and then go to that record after the Form is requeried.

[zmbd]

@twinnyfo:
You are correct.
I was asking OP to clarify if they wanted to go to the add a new record or to the recently created record which mentioned at the end of the post would be most easily found using the Primary Key for the record.

[twinnyfo]

Z,

Yes, I re-read your post more carefully this time. All clear now. Thanks!