Subform navigation systems on a form can get a little tricky at times.

I am currently working on a Subform / Subreport control on a form that lists all the shipment records for a single job in order of the shipping date and time.

Because the customer wanted a numeric indicator on each row counting them up from one, I decided to use a Report object which allows you to use a text box bound to the number one and count up for each row automatically. This was great and much easier than the somewhat more tedious solutions around trying to do the same thing on a form.

However, once I started trying to navigate between records, I encountered some problems, mainly when I had enough shipments for a job to have multiple pages of shipments.

In this case, when clicking on say the last row of the multi-page row set, the main form loads the record, but then resets the subform control which reloads the report which then bounces you back to the top. This is rather inelegant for the user who then no longer sees the selected record (highlighted by using conditional formatting on a row placed behind the other fields). Ultimately, I was able to solve multiple issues with this by:

1. Setting focus on the subform
2. Enabling a control in the detail section of the report
3. Setting focus on a control on the detail section of the report
4. Using a DLookup (actually using my custom NiceDLookup function) to count the number of records we need to advance.
5. Using a loop to run DoCmd.GotoRecord , , acNext that many times
6. Setting focus to the same control as in step 3 again (this was required to get it to reset the actual page)
7. Finally disabling the control again (this was required to deselect the text.

Now this sequence of events I just described I’m working through is specific to reports. Text box controls and the Recordset object on a report have different properties and you cannot do a RecordsetClone on a report because it is read only. So there is a completely different way you would want to interact with a form in the same control. In addition I was unable to use the SelStart or SelLength properties in the Report Text Box. I’m not sure if this was just because it’s a report, or has something additionally to do with the fact that it’s a subreport and in Report view vs Print Preview.

In this case I used a report so that I could use the ongoing sum feature of the text box when in a report (this is not available in a form). Another even better reason to use reports in subform/subreport controls is to be able to do grouping and subtotals for groups. If you need a mini read-only report, this is a great feature!