Next/Previous Record Buttons

[ahuffman24]

Hi,

I am using Access 2016. I have a form that lists company names, order numbers for the company, item numbers for each order, and other details about the specified item. It looks like this:



When you click the "View Details" button next to a specified order, it will bring you to a screen called "Chosen Order" that shows all the details for that particular order and item number. It looks like this:




You can see at the bottom of the picture that the order is "filtered". This is because. in the macros of the "View Details" button, I have told the button to pull up the "Chosen Order" screen based on the order number and item number that the user wants to view the details for. The two buttons in the bottom right of the picture are what I am having issues with. I want those buttons to allow the user to scroll through all of the orders and display the "Chosen Order" screen for each order. So, when the left arrow button is clicked, it will bring up the previous order details, and when the right arrow button is clicked, it will bring up the next order details; referring to the very first picture I included, if I choose to view the details of the Shell 58012 item 5 order, if I click the left arrow button, I should see the details of the Shell 58012 item 4 order, and from there, if I click the right arrow button, it should bring me back to the Shell 58012 item 5 order. I should be able to scroll through all of the order details in this manner.

However, the buttons are not working correctly. Firstly, whichever button is clicked first needs to unfilter the order details form (as shown in the second picture, the Chosen Order form is filtered upon clicking the "View Details" button to show only the details of the order/item that the user wants to view) so that if the user wants to view the details of the next or previous order, it will allow them to do so. So, here are my macros for each button:

Next Record Button:



Previous Record Button:




The issue I am having is first with the previous record button. If I click the Previous Record Button FIRST, the button does unfilter the Chosen Order screen, however, the button will not actually bring me to the previous order when clicked. It always brings me to the Shell 58012 item 4 screen, regardless of which order I am viewing. However, if I click the Next Record Button first and THEN the Previous Record Button, it will allow me to scroll through all of the orders. So my first question is how can I fix this?

The second issue I am having is with the Next Record Button. Referring again to the first picture I posted, if I choose to view the very first order ("test") and then click the Next Record Button, it always skips the Shell 58012 item 4 order and goes straight to the Shell 58012 item 5 order. Why would this be, and how can I fix that?


I know this is a very long post, I apologize! I just wanted to give as much detail as possible. Let me know if I need to clarify anything else. Thanks!

[ahuffman24]

Please ignore the last picture that was posted, I did not mean to post that

[rpeare]

I don't use macros I use VBA to so I'm not entirely sure how to do what you want with the limited nature of macros. Is there any chance you could submit a dummy database with some garbage data in it to look at?

[davegri]

Best solution is to not filter at all, but find first record meeting criteria when the "View Details" button is chosen.
See if you can modify the macro behind that button to use the "SearchForRecord", "FIRST" macro action.
Hopefully it will find the proper record without setting the filter.

[ahuffman24]

@davegri, are you saying to use the SearchForRecord macro on the View Details button?

[davegri]

See if you can modify the macro behind that button to use the "SearchForRecord", "FIRST" macro action.

The method I described is how I would do it in VBA. The code terminology is a bit different, but I think the Macro action you need is as described. Yes, it's for the ON_CLICK event for the View Details button.

[ahuffman24]

Hi,

thank you for the reply @davegri. I have changed my form slightly but am still having a similar issue. I tried to add a SeachForRecord action in the macros with the FIRST action selected, and I did not have any luck with that (I could be doing it wrong, however).
I originally made a new thread with my updated form on it to eliminate confusion between the issue described in this thread and my new issue, but I will copy what I posted in the new thread and paste it here:

Hi,

I am using Access 2016. I have a database that keeps track of orders and info about those orders. One of the sections of the database allows the user to select a specific order to view the details of. However, each order may have more than 1 item on it. For example, the picture below shows order number 55555 has both item 4.00 and 5.00:

The user can then choose which item to inspect for the order, and it brings them to this page:

As you can see in the above picture, I have two buttons with a left and right arrow on them. I want those arrows to scroll between each of the items on the chosen order. Keeping with the previous example, if the user chooses to view the details of item 4.00 on order 55555, then if they click the right arrow button, it should display the information for item 5.00 of order 55555. Right now, I have the buttons set up in macros to scroll to the "next" record for the right arrow button and the "previous" record for the left arrow button, but they only scroll through the serial numbers of the current order instead of scrolling order to order. To clarify, if I am on order 55555 of item 4.00 and I click the right arrow button, the next serial number will become highlighted in the subform, but what I want the arrow to do is to take me to the details of order 55555 of item 5.00. Is there a way I can make this happen?

[davegri]

Can you upload your DB to the forum? There are too many variables to go thru via the back and forth messages here.

[ahuffman24]

@davegri Sample Database.zip

[davegri]

Huff-Sample Database-davegri-v01.zip
See attached. Added 2 new forms, a main form based on the customer orders and a subform based on the inspections.
Bare bones; you'll need to adapt filters, custom nav buttons, etc.
As you scroll thru the main form, it scrolls thru orders, automatically showing inspections for the orders in the subform.
I think you can work with this.

[ahuffman24]

Sample Database1 (2).zip
@davegri, thanks for the sample. Only issue is, with my database, I am not using a subform for the inspection information because I needed to shrink the headers as much as possible (this database will be used on a tablet), and I was restricted by the subform headers. So, I made a continuous form with all of the fields from the inspection table. I have included another zipped copy of my database. If you look at the "View All Orders" section or even the Inspect section and choose an order to view from either of those you will see what I mean. So basically I need the buttons to scroll between order numbers without using the subform. Is this possible?

[davegri]

I don't see how. For example on details form, there is no subform, so when you scroll, you scroll thru the recordsource and you see what's in the records. There's no way to just show the header data without the detail data changing also. It's the same record. Perhaps you could make the detail section visible=no, but that makes little sense. Why have the detail section there at all if you don't want to see it?

[ahuffman24]

Maybe I was not clear enough. Everything on the details form I want to see. I just want to add two buttons that would allow the user to scroll between orders instead of scrolling through each serial number of the order they're currently viewing. Right now, when I create a Next Button and a Previous Record Button through the macros, it sets the GoTo function to go to "Next" record, but it assumes that the next record is the next serial number on the order the user is currently viewing. I don't want that, I want the two buttons to scroll to the next order number and display all of the serial numbers/other info for that order.

[davegri]

I made a named query from your recordsource SQL for the Detail form, shown above.
There's only one order for one customer for one item. When you scroll the form, this is what you scroll.
You need more data to display. That might make it evident if your relationships are wrong.

[ahuffman24]

I think my relationships are good, here is a screenshot of them:

Since I have one order number with many items, and one item with many serial numbers, I should have 2 one-to-many relationships as shown above. I can add more data if you'd like, but really my question just boils down to whether or not I can scroll between orders in my View All Form or Inspect Form (instead of scrolling through serial numbers) without using a subform for the serial numbers.