423,311 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,311 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.
Jul 10 '18 #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,279
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
Jul 10 '18 #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.
Jul 10 '18 #3

zmbd
Expert Mod 5K+
P: 5,279
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
Jul 10 '18 #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?
Jul 10 '18 #5

zmbd
Expert Mod 5K+
P: 5,279
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.
Jul 11 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,532
@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.
Jul 11 '18 #7

zmbd
Expert Mod 5K+
P: 5,279
@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.
Jul 11 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,532
Z,

Yes, I re-read your post more carefully this time. All clear now. Thanks!
Jul 11 '18 #9

Post your reply

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