Need help creating tricky Access report 1

[ArzonM]

I need to print a serial number on a pre-printed 8.5 by 11 inch form that looks something like this:

X | X
------
X | X
------
O | X
------

The X's represent a spot where the serial number is to be printed and the O represents a spot where nothing is printed.

I have tried a couple a different things and get results that look like this:

1 | 2
------
3 | 4
------
5 | 6
------

or like this:

1 | 1
------
1 | 1
------
1 | 1
------

Thank you in advance for any advice.

ArzonM

 

[KornGeek]

Are you trying to get

1 | 1
------
1 | 1
------
O | 1
------

or

1 | 2
------
3 | 4
------
O | 5
------

If it's the first one, simply create a full-page report that repeats the field in the locations required.

 

[ArzonM]

KornGeek,

Thanks for the response and sorry for the confusion.

I am trying to get output that looks like your second example:

1 | 2
------
3 | 4
------
O | 5
------

 

[KornGeek]

Unfortunately, I'm not sure that Access supports doing that, but maybe somebody else here knows a way that I don't. I've seen some pretty amazing solutions. You may want to try reposting your question in the reports forum.

 

[JoeAtWork]

I'm not sure if I'm following you, is it that you have a SerialNo field that has five digits? And you want each digit on a specific location in the report?

If that's the case, you might try something like the following for your query (assuming SerialNumbers is your table and SerialNo is the field):

SELECT Left([SerialNo],1) AS Digit1, Mid([SerialNo],2,1) AS Digit2, Mid([SerialNo],3,1) AS Digit3, Mid([SerialNo],4,1) AS Digit4, Right([SerialNo],1) AS Digit5
FROM SerialNumbers

Then your report could be layed out as follows:

Digit1 | Digit2
---------------
Digit3 | Digit4
---------------
| Digit5

 

[jsteph]

I'm not sure exactly what you want, but try looking into the .MoveLayout, .NextRecord, and .PrintSection methods of the report.

It looks like you want to force a blank in a spot that would normally have a number. This is where the .movelayout and/or .nextrecord will help control that.
--Jim

 

[ArzonM]

Sorry everyone for the confusion. Sometimes we forget what is clear to ourselves, is clear as mud to everyone else. Let me try to clarify the situation.

The serial number is a field in my table. I have to print a complete and distinct (i.e. from a separate record) serial number in five different locations on a preprinted form. This form is on a standard sized piece of paper and is divided into six equal sized areas. The lower left corner of the form is to be left blank, while the other five areas get a serial number. The actual serial number is 12 digits long, but I will use a five digit number in this example:

00001 | 00002
--------------
00003 | 00004
--------------
BLANK | 00005

Page 2

00006 | 00007
--------------
00008 | 00009
--------------
BLANK | 00010


Thank you everyone for your posts!

 

[jsteph]

Obviously you'll have to set up the positions of the textboxes by trial-and-error, but for the blank you have 2 options.

First is to use the .movelayout in the Detail section to do what you want as far as skipping and making the blank space.

Second is to create a temp table, use a recordset loop to load it and leave every 5th (or whatever) record blank.
--Jim

 

[ArzonM]

jsteph,

Thanks for the great suggestion, you were right on! I did a little research on the movelayout method and was able to piece together the following code:

Option Compare Database

    Dim mintRecordCount           As Integer      'tracks the current recourd
    Dim mblnPrint                 As Boolean      'flag that determines whether or not record will print
    
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    'increments variable
    mintRecordCount = mintRecordCount + 1
    
    If mintRecordCount Mod 5 = 0 Then
    'Evaluates whether or not the record number is evenly                                 divisible by 5.  If record number _
    'is evenly diviible by 5 then the record will not print.

        Me.PrintSection = False
        Me.NextRecord = False
    Else
    'Prints the record if it is evenly divisible by 5
        Me.PrintSection = True
        Me.NextRecord = True
    End If
        
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    'Initialize variables
    
    mintRecordCount = 0
    mblnPrint = False
End Sub

It works perfect

Thank you to everone who helped.