Solved Command Button Record Navigation in Form Descending (1 Viewer)

[Weekleyba]

When placing command buttons in a form to navigate to the previous or next record, is it best to use the embedded command buttons for the 'go to next' or 'go to previous', when you want the records to appear in descending order?
The reason I ask is, they behave as they should, but to the user they are are in reverse order.
So what I end up doing is setting this up with the 'go to next' as the previous button and the 'go to previous' as the next button? Basically swapping them around.
This works fine but, when I need to add a new record, the user cannot simply click the next record button as it is really not the next record button.
How have you guys dealt with this problem?
I've added another command button to add a new record which works but, I'm curious how the pros do this.

FYI on the form I have the following code which place the records in descending order.

Private Sub Form_AfterUpdate()
'After update,orders desc.  OrderByOn = True, setting is applied when object is open.

Me.OrderBy = "[UpdateID] Desc"
Me.OrderByOn = True
Me.Refresh

End Sub

Private Sub Form_Load()
'When form loads,orders desc.  OrderByOn = True, setting is applied when object is open.

Me.OrderBy = "[UpdateID] Desc"
Me.OrderByOn = True

End Sub

 

[CJ_London]

Not really clear what constitutes a record in your book - your image appears to be showing 4 (or more) records as 1 record So are they adding to the bottom of this 1 record or going to a completely new record?

 

[Weekleyba]

Not really clear what constitutes a record in your book - your image appears to be showing 4 (or more) records as 1 record So are they adding to the bottom of this 1 record or going to a completely new record?

To clarify, the subform SF_Update, has table T_Update as it's record source.
The fields are all part of each record in the table. See below.
The table has 1,573 records.

 

[Gasman]

To me Next Record is the Next Record, the same with Previous?
That is regardless of of which order the records are displayed.

And now I have had a Deja Vu?

 

[Weekleyba]

To me Next Record is the Next Record, the same with Previous?
That is regardless of of which order the records are displayed.

And now I have had a Deja Vu?

That is true, but in this case, since it is in descending order the next record is the previous one.
Example: Say you are on record 12. You click next record, it goes to record 11 and not 13.
Do you see what I'm trying to convey?
When you get to the last record and click next record, it does NOT go to a new record but the previous record.

So I understand why it is doing this but, I would like the subform to default to the last record, even after a new record is created.
This makes sense since the user wants to see the most recent update.
I know what I've set up does not accomplish this and thus the reason from my post.

Have you created a form that shows the records in descending order with buttons to go to the next and previous records, WITH the next button being able to create a new record?

 

[CJ_London]

I'm still confused - your example shows an update number of 11. And yet there are multiple number 1, 2, 3 etc. Would be more helpful to show related data - i.e. the data related to update number 11 - perhaps there is a filter somewhere to filter by projectID.

Not sure why you need an update number since your timestamp field provides the same order

Understand what you are saying about next and previous if sorting asc/desc, just change the image - and would have thought up and down icons would be more appropriate

I also wonder if you have set the append only property for the memo field to yes which will have its own complications

 

[Gasman]

That is true, but in this case, since it is in descending order the next record is the previous one.
Example: Say you are on record 12. You click next record, it goes to record 11 and not 13.
Do you see what I'm trying to convey?
When you get to the last record and click next record, it does NOT go to a new record but the previous record.

So I understand why it is doing this but, I would like the subform to default to the last record, even after a new record is created.
This makes sense since the user wants to see the most recent update.
I know what I've set up does not accomplish this and thus the reason from my post.
th
Have you created a form that shows the records in descending order with buttons to go to the next and previous records, WITH the next button being able to create a new record?

I have always useththe access navigation buttons.

 

[Weekleyba]

I'm still confused - your example shows an update number of 11. And yet there are multiple number 1, 2, 3 etc. Would be more helpful to show related data - i.e. the data related to update number 11 - perhaps there is a filter somewhere to filter by projectID.

Not sure why you need an update number since your timestamp field provides the same order

Understand what you are saying about next and previous if sorting asc/desc, just change the image - and would have thought up and down icons would be more appropriate

I also wonder if you have set to append only property for the memo field to yes which will have its own complications

Recall this is a subform that I'm talking about here. Namely, SF_Update. The link between the F_Project and SF_Update is the ProjectID.
But this has nothing to do with problem. It could be any form.
The update number is only for the knowledge of how many updates have been provided.
I have changed the image, so that the it appears to work for the user.
The memo field Append property is set to No.

The only thing my solution does not do is, the next button does not create a new record. Again, I understand why it doesn't. I'm trying to see if anyone has been able to accomplish it. Seems like a reasonable thing many should've done in the past.
But maybe I'll just have to live with the way it is and continue to use the 'Click to Add New' button for new records.
Sorry for the confusion. I really thought this would more easily understood.

 

[Gasman]

Plenty of examples on here about roll your own record navigation.
One example I seem to recall allows you to either cycle to the start/end depending on the direction, or stop and say 'No more records'

You could use that test to goto new record?

 

[CJ_London]

I really thought this would more easily understood.

you know your app, the tables, the fields the forms etc, we don't so you have to be very clear about what you have. It is still not clear whether what you see in your first post is one record or many. So I'm guessing one.

you could use the recordset absolute position to go to a new record

so in your button click event you might have code along these lines - choose 0 or recordcount depending on which button was pressed

if recordset.absoluteposition=0/recordset.absoluteposition=recordset.recordcount-1 then 'go to a new record

 

[Pat Hartman]

Whether the data is sorted ascending or descending, next is next. Next isn't always higher, it may be lower if the data is sorted descending.

Users rarely scroll into the New record. They use the button if you give them one or they use the * if you show the built in navigation control. As you can see, we're all pretty confused about the issue. It looks like the subform is showing concatenated records rather than individual records and that is probably what is causing the confusion.

 

[Weekleyba]

I've tried a bunch of different things and I'm close to what I want but still no sure this even close to the best way of doing this.
I have several forms that will use this set up so I'm trying to develop a solid standard for it.

Attached is a sample of what I have so far.
A form that show records in descending order, with command buttons to go next and previous and create a new record.

Can anyone improve on this? (I confident you can...)
One thing I don't like is, if you click the 'Click to Add New' button and decide you don't want to be in there, you can't simply hit escape to exit. You need to click the Next button. And the Next button then takes you to the record 1 instead of the last record.
I'd prefer to either hit Esc and it brings you to the last update record and to be able to click the 'Previous' button to take you to the last update record.

Maybe this sample database will clarify what I am trying to do.

 

[Pat Hartman]

1. I changed your record level locking to no.
2. If you are going to have Name Auto Correct on all the time, you really need to also show the log table so you can see why it isn't working the way you think it does. It is better to leave it off. You can turn it on if you want to use it but be sure to open every object you think it should change. Then you can turn it off again.
3. I added Option Explicit to the code module
4. I swapped the code for the previous/next**
5. I moved the sort to the RecordSource

**You have the built-in navigation bar showing along with your custom navigation. That was blowing my mind for a while since the two worked differently. That's why I changed the meanings of your previous and next so they would match the built in navigation.

So - if you want to change your navigation back to the way it was working, REMOVE the built in navigation it is simply too confusing to have them working opposite each other.

 

[Weekleyba]

Thanks Pat. I did swap the previous/next back to how I had it, and turned off the navigation at the bottom of the form. I want to this to make sense to the user, so when they click the Previous button, it goes to the previous update. And yes, this does get confusing on the developer end, since it is in descending order, but when did that ever stop us.

But, I'm still trying to correct one last thing:
1. When the user clicks on the 'Click to Add New' button, I want the option to either click the escape key or click the Previous button that will take it back to the latest update. (or both preferably) For example, if you are on update 13 and you click the 'Click to Add New' button, it will show update 14 of 13 since the record is not yet created. At that point, before it is created, I'm looking to either Esc or click the Previous button to take it back to update 13.

Can you help me with that?

 

[Pat Hartman]

Wow, I really hate the way this form works. But it looks like Access hates it also. There seems to be an actual bug unless I'm going crazy.

Normally, when you go to the "new" record but then decide to not enter a record, If you press the forward button, nothing happens. If you press the back button, you go to the last record in the recordset (not back to the record you were positioned on when you pressed the add button if that was what you were expecting).

By your logic, you have to press the Next button but that brings you back to the first record (if you were sorting ascending) AND it reverts the sort order to ascending. I changed the sort order in the query for one of my forms to mimic your situation. I scrolled "back" a few records, then used the * to go to a New record. Didn't change anything but pressed the back button and that got me to the logical last record respecting the descending sort. I even tried removing the Order by on load setting.

The processing works fine with the built in navigation control if you can get your mind around the forward/backward concept.

I can't fix Access bugs for you. You should report this one (using File/Feedback) and upload a copy of the database so they can see the bug. Explain what happens step by step. Don't forget to mention that the sort order is permanently changed. Don't expect a fix any time soon so you have to move on. Clearly there is different code running when you use the next/previous commands in VBA than what runs in the navigation control.

I would add a button and just requery the form. I added the code. The Cancel only shows when the form is positioned on a new record.

I think if you went with the flow and changed your backwards previous/next buttons, the problem would go away. Doesn't mean you shouldn't report this because I think it is a bug. You're entitled to use backwards logic and Access should NOT be changing the sort order on you;(

I forgot to hide/show the prev/next buttons. Add that code yourself to the current event. When the Cancel is showing, the other two need to be hidden and vice versa.

 

[Weekleyba]

Isn't this a common desire? A form with records in descending order, with command buttons to go next and previous?
I would think it would be, so I'm surprised it's not easier to set up.
I have a database for projects that has subforms (on tabs) that will use this type of form set up for:
1. Project Update
2. Contractor Update
3. Submittals
4. RFI - Request for Information
5. Inspections
6. Meeting Minutes.

In all of these, it is preferred to see to latest entry first.

If there's another way...a better way to do this, I'm all ears.

Until then, attached is the final version...so far.

 

[Pat Hartman]

Isn't this a common desire? A form with records in descending order, with command buttons to go next and previous?

Absolutely, except that the rest of us think of next and previous differently. And remember, the bug only appears when you use custom navigation controls AND when you want them to work backwards The bug does NOT happen when you use built in navigation controls.

If there's another way...a better way to do this, I'm all ears.

Did you not understand the form I fixed for you? Did I waste my time solving your problem even when it was caused by a bug in Access? Did you report the bug to MS? Bugs have no shot at ever getting fixed if people don't report them.

 

[Weekleyba]

Evening Pat. I apologize if I have insulted you by asking the forum if there’s another way to accomplish this. Your help with what I had was great, but I know there are other experts, like yourself, on this forum that sometimes have solutions that others don’t think about. I’m just surprised that I’m the one that found a bug with this forum set up, that to me, should be pretty common to use. So my thinking was, perhaps I’m setting this up all wrong (which does happen to me) and there’s a better way to accomplish it. That is not to say anything negative about the solution you provided, as you were just trying to help me with what I had already started and not trying to recreate it another way.
Hopefully you see where I’m coming from and perhaps you can forgive the lowly Access hack that I am.
Again and always, I appreciate all the help this forum has given me over the years. It is #1 in my book!

 

[Pat Hartman]

You've been around long enough to know that "experts" abound on sites like this and there would be a multitude of responses if there were actually other solutions. I don't know what you are looking for. Did you not like the cancel option? You may still get some alternatives so I will go away and not bother to respond again.

I have personally found several bugs in Access and now this one. Usually I find them as differences between how VBA handles something vs some macro or internal Access method which is what we ran into here. The navigation control is written in whatever Access.exe is written in and the code is optimized and tested extensively and then compiled. The navigation control works correctly. It moves forward and backward through the recordsource AS IT EXISTS. The arrows have exactly the same meaning regardless of the sort order. << moves back and >> moves forward. Usually the source is low to high so left to right moves low to high but it is moving this way (-->>) through the recordset. When the source is high to low it still moves left to right except this time it is high to low because that's the sequence of the data. Regardless of how the data is sorted, think of the movement is front (beginning) to back (end) or back to front. You changed the natural order of things by reversing the meaning of your buttons. Fine. If that makes you and your users happy. Do what you want. The cancel "fix" got you out of the bug issue caused by cancelling a pending insert. To my mind, your solution is akin to being on a one way street. If you want to go back where you came from, most people would turn around and walk in the other direction. You seem to want to walk backward. But it is not dangerous or immoral or illegal so I helped you to get the result you wanted