VBA Code for Bulk Reports - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

VBA Code for Bulk Reports

Shia G.

2 years ago

Hi everyone,

I've been working on a VBA code for processing a large number of invoices (over 100,000 records) grouped by 'NameID' (using "Grouping and Sorting" in the REPORT design interface). The code includes resetting page numbers across grouping levels and turning off the form header for the first of each group, progress bar, etc.

I have a few questions:

1. Grouping with Recordsets:
Is there a way to implement the "Grouping and Sorting" using recordsets or some different code in VBA, while keeping all groups in a single continuous file (Report)?

2. Status Bar Message:
While the process is running, a status bar message saying "Formatting page, press Ctrl+Break to stop" appears. How can I turn off or suppress this message during execution? (It seems to slow down the process)

3. Avoid "Formatting Page" on Navigation:
After the report page opens and displays the first sheet, navigating to the last page triggers the "Formatting page" message, causing a delay similar to the initial loading time. How can I avoid this delay when moving to the last page?

I would greatly appreciate any suggestions or insights into optimizing the efficiency of this process.

Thanks in advance!

Sami Shamma @Reply

2 years ago

Hi Shia

Let's tackle your questions one by one.

1) please explain what you are doing now (screen shots are helpful) you can do a lot with recordsets and you can control grouping and sorting with VBA code. sample data will also help. then explain what you want to do differently using VBA code.

2) & 3) I have not seen this "Formatting page, press Ctrl+Break to stop" before. I assume it is because I never processed 100,000 records before.
check out
Suppress Warnings

	Alex Hedley @Reply 2 years ago
`expression.Echo (EchoOn, StatusBarText)`

Kevin Yip @Reply

2 years ago

Hi Shia, 100k records is just too much data and you are going to see delays even on a fast PC -- which I estimate can generate 30 pages per second.  So if you have 50 records per page, the report will have 2000 pages total = about 67 seconds of delay before you can see the last page.  You may as well show the "Formatting page..." message on the screen so you won't keep the users in the dark, because they will see the delay anyway.  Or you may find a way to create smaller reports.

Whatever sort order you set for the report's recordsource, it will be ignored (and replaced) by the report's grouping & sorting.  So if you want to create "dynamic" custom sorting and grouping for the report, you need to change those settings directly.  But the report's group & sort settings can only be changed when the report opens.  You can't change them while the report is in the middle of generating pages.

In the report's Open event, you change the GroupLevel property.  Sorting and grouping are basically the same thing (except that groupings have headers and footers), and they are both controlled by the GroupLevel property.  For instance, if your report is set to sort by the FirstName field at design time, you can change it to sort by the LastName field at run time:

Private Sub Report_Open(Cancel As Integer)
    Me.GroupLevel(0).ControlSource = "LastName"
End Sub

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View