Insert OMR marks into Microsoft Access Report

Posted on 2010-03-29
Medium Priority
Last Modified: 2013-11-28
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?
Question by:nateday76
  • 2
  • 2
LVL 85
ID: 29005122
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.


Author Comment

ID: 29005604
Correct. Looking to put in Optical Mark Recognition for feeding the resulting reports through an autostuffer.
LVL 85
ID: 29006247
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.


Author Comment

ID: 29006486
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.

Accepted Solution

Uberstein earned 1000 total points
ID: 32919065
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!


Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month3 days, 4 hours left to enroll

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question