Insert OMR marks into Microsoft Access Report

Does anyone know if it's possible to add OMR marks in access when the Report can produce variable page groups? If so, any examples?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
And "OMR marks" would be what? Please remember that you're talking to bunch of database developers, not someone intimately familiar with your industry. I'd assume that you're talking about some sort of Optical Mark Recognition, but it's impossible to say based on your question.

nateday76Author Commented:
Correct. Looking to put in Optical Mark Recognition for feeding the resulting reports through an autostuffer.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are these some sort of font or "barcode"? In other words, is there anything special about them or are they just lines/crosses etc?

Access can certainly insert those items into a report, but positioning them can be troublesome since different sections of the report can grow and shrink as needed, and Access will move things around based on that. Often it's better to have them printed to the page before you print your report, but that would mean you'd have to handle the paper twice.

nateday76Author Commented:
They are lines that show up on the side of the page. It would be fine for them to always show up in the footer, but the number of lines differ based on the page of the report. I.E. if the report is 2 pages long there would be a different number of lines on page 1 than on page 2. Also if the report is only 1 page then the number of lines is different on page 1 than if the report were a multipage report.
I had a problem exactly like this.
The solution wasn't exactly simple, but wasn't too difficult either.

I had already done the first part of the solution, because when I created this report I had to have it show the page number at the top of the actual statement for that client.

So if the client statement had 3 pages, it would go Page 1,2,3 then reset to page 1 for the next client.

The way to accomplish this is to create a Macro in Access.

Entitle the Macro SetPage1.
For this new macro, the Action should be the SetValue Action.
For the Arguments for this Action, set the Item to [Page], and the Expression to 0
(That's a zero, not an O)

If you are using Access 2007 and do not see the SetValue Action as a choice of Actions (I did not at first), in the Design Ribbon Menu, click the "Show All Actions" button. Then it'll appear as a choice.

Save the Macro.

In the first, outermost Group Header, and by this I mean the first, outermost group you created to group this report by client, go into the properties of that group header. On the Format Tab, change the Force New Page property to Before Section.

Can Grow
and Can Shrink should be set to Yes. Auto Height should be set to No.  
Keep Together
should be set to Yes, Repeat Section should be set to No.
Close the outermost Group Header Properties.

Open the outermost Group Footer Properties.
On the Event tab, set the On Print Event to be that macro we created in the first step called SetPage1. This allows the macro to fire every time a new client group begins, and resets the Page Number to 0. Access keeps track of the page number in a  built-in variable called [Page].  

So now, at the beginning of every new grouping, [Page] gets set to zero.

Now if you want some sort of mark to appear at the bottom of the last page of a client group in the same place every time, create the mark. then make it an element in the Page Footer section.

Open the Page Footer Properties.
On the Event tab, set the On Print Event to be a visual basic procedure (choose Code Builder after hitting the ... button).

My code looked like the following, because my "Optical Mark" was just 5 horizontal lines at 2pt width. (Line135 through Line139)

All it is doing is toggling the Visible Property of those five lines depending upon the value of the [Page] built-in variable:

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)Me.Line135.Visible = TrueMe.Line136.Visible = TrueMe.Line137.Visible = TrueMe.Line138.Visible = TrueMe.Line139.Visible = TrueIf [Page] = 0 ThenMe.Line135.Visible = TrueMe.Line136.Visible = TrueMe.Line137.Visible = TrueMe.Line138.Visible = TrueMe.Line139.Visible = TrueElseMe.Line135.Visible = FalseMe.Line136.Visible = FalseMe.Line137.Visible = FalseMe.Line138.Visible = FalseMe.Line139.Visible = FalseEnd IfEnd Sub
This made the mark (just those five boldface lines, basically) appear at the bottom of the last page of every client statement.

You can place a different mark at the beginning/top of each group as well (if necessary) using similar logic:

In the Page Header section, add your mark.
For perhaps a three line mark instead of the five line mark.

In my case, it was Line142, Line 143, and Line144.

Then in the Page Header Properties on the Event tab, use the On Format Event, and add similar code to the above to it, only this time it fires only when [Page] = 1:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Me.Line142.Visible = True
Me.Line143.Visible = True
Me.Line144.Visible = True
If [Page] = 1 Then
Me.Line142.Visible = True
Me.Line143.Visible = True
Me.Line144.Visible = True
Me.Line142.Visible = False
Me.Line143.Visible = False
Me.Line144.Visible = False
End If
End Sub

This should set you on the right track, I hope.

Let me know how it turns out!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.