422,952 Members | 2,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,952 IT Pros & Developers. It's quick & easy.

Re-Apply Sort After New Record

mjoachim
P: 33
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSelectRecord
  2. DoCmd.RunCommand acCmdCopy
  3. DoCmd.GoToRecord , , acNewRec
  4. DoCmd.RunCommand acCmdPaste
I have tried adding these to my code to complete the sort, but they haven't worked:

Expand|Select|Wrap|Line Numbers
  1. Me.OrderBy "Employee Number"
  2. Me.OrderByOn
Expand|Select|Wrap|Line Numbers
  1. 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.
1 Week Ago #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,105
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
1 Week Ago #2

mjoachim
P: 33
Adding me.requery after my new record code doesn't work.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSelectRecord
  2. DoCmd.RunCommand acCmdCopy
  3. DoCmd.GoToRecord , , acNewRec
  4. DoCmd.RunCommand acCmdPaste
  5.  
  6. Me.Requery
The new record is still at the bottom of the form.
1 Week Ago #3

zmbd
Expert Mod 5K+
P: 5,105
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
1 Week Ago #4

mjoachim
P: 33
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?
1 Week Ago #5

zmbd
Expert Mod 5K+
P: 5,105
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.
1 Week Ago #6

twinnyfo
Expert Mod 100+
P: 2,443
@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.
1 Week Ago #7

zmbd
Expert Mod 5K+
P: 5,105
@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.
1 Week Ago #8

twinnyfo
Expert Mod 100+
P: 2,443
Z,

Yes, I re-read your post more carefully this time. All clear now. Thanks!
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.