2 column report - printing out ID cards 1

[scroce]

My company uses a prewritten form for an ID card that gets printed out with vehicle information on it.

Each prewritten form comes on an 8.5 x 11 sheet of paper, landscape orientation with 4 ID cards on each, one in each quadrant of the paper. The paper is perforated, so if the customer has 4 vehicles, you print off one page, and tear it in half lengthwise and again widthwise to get your ID cards.

I set up one quadrant of an access report (roughly 4" x 2&quotto match the form utilizing Format>Page Setup>Columns. There are 2 columns, and the layout is set up as "across then down" and seems to be able to comfortably fit 4 ID cards on one page

THE PROBLEM IS: I have an underlying field on the report that specifies how many ID cards the customer needs. When I run the report, it only gives me one id card (since there is only one record for each client) before it goes on to the next ID card for the next client - following that "across then down" format.

BUT..If a client has 4 vehicles for example, I need the report to format 4 identical ID cards on one page before it goes to the next client.

How do I do this? I'm sure it can be done, but I am not sure what properties/methods to manipulate - can someone give me some ideas about where I may begin to look?

I'm thinking the "PrintOut" method combined with some kind of Do..Loop thing, but what properties can I manipulate?

thanks!

Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[KenReay]

Hi

See my answer in thread181-430632 to what is logically a similar question. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scroce]

Hi - first of all - thanks for your reply.

If I am reading your solution correctly,

the issue I see is that I have a database of 283 clients. Each client could have anywhere from 1 to a fleet of sometimes 200+ vehicles If I did what you are suggesting that's a ton a data entry - and then what happens when the client adds or deletes a vehicle - then I'd have an extra step of going into that associated table and making sure the number in "NumOfVehicles" matches up correctly with the number of records in that new associated table.


Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[scroce]

Also, check this question out - anybody know the answer? - it's essentially the same question I'm asking stated in one sentence.

thread181-186834 Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[PWise]

try this before you open the report
dim mydb as database
set mydb=currentdb
selectstr="select...."
sqlstr= selectstr
if dlookup("numofidcards","tablename",critre)>1 then
for co=2 to dlookup("numofidcards","tablename",critre)
sqlstr=sqlstr & " union " & selectstr
next co
end if
sqlstr=sqlstr& ";"
mydb.querydef("reportrecordsourcename&quotsql=sqlstr

set the report recordsource to reportrecordsourcename

 

[KenReay]

Hi scroce

I never anticipated you would ENTER the data, you 'generate' it with an SQL statement, pretty much as outlined by pwise Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RickSpr]

There's really a better way that doesn't involve creating a special table with duplicate data.

In the report's Format event, you can set the NextRecord and PrintSection properties to control printing the same record multiple times, and skipping ID cards to get to the next page. If you set NextRecord to False, the same record will be printed again. If you set both PrintSection and NextRecord to False, an ID card will be left blank.

Here is a module that will do the job. You need to customize the ItemsPerPage constant, and the [NumCards] field at the start of the Detail_Format procedure:

Option Compare Database
Option Explicit

Dim intItemsPerPage As Integer  ' # items to print per page
Dim txtCopiesWanted As TextBox  ' TextBox containing # copies to print

Dim intCopiesWanted As Integer  ' # copies of current record to be printed
Dim intCopiesPrinted As Integer ' # copies already printed for current record
Dim intItemPos As Integer       ' Next position to be printed
' Position 1 is top left, position 2 is either the next column to the
' right or the first column in the next row, depending on whether you
' are printing across then down, or down then across (page setup)

Private Sub Report_Open(Cancel As Integer)
    ' Initialize intItemsPerPage to the number of cards on a page
    intItemsPerPage = 4
    ' Set txtCopiesWanted to the TextBox control giving the copy count
    ' for each record
    Set txtCopiesWanted = [NumCards]
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ' Get # copies of this record to be printed
    intCopiesWanted = Val(txtCopiesWanted)
    
    ' If # copies to print <= 0, don't print anything and skip this record
    If intCopiesWanted <= 0 Then
        MoveLayout = False
        PrintSection = False
        NextRecord = True
        Exit Sub
    End If
    
    ' If this is the first time formatting this record, reset the copy counter
    If FormatCount = 1 Then intCopiesPrinted = 0
    
    ' Track the position where this item will be printed
    intItemPos = intItemPos + 1
    
    ' Access always tries to add too much data to a page. When it finds out
    ' the page is full, it prints it and formats the same record again for
    ' the next page, so if our page is full, ignore this call; we'll decide
    ' what to do when it's formatted for the next page.
    If intItemPos > intItemsPerPage Then Exit Sub
    
    ' Don't print this item if we have already printed enough
    If intCopiesPrinted >= intCopiesWanted Then PrintSection = False
    
    ' Don't move to the next record if we haven't printed enough
    ' copies yet, or if there are more items to skip on the page
    If intCopiesPrinted + 1 < intCopiesWanted _
    Or intItemPos < intItemsPerPage Then
        NextRecord = False
    End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' Keep count of how many copies of this item have been printed
    intCopiesPrinted = intCopiesPrinted + 1
End Sub

Private Sub Report_Page()
' When a page has been printed, reset the item position to top left
    intItemPos = 0
End Sub

Note: NumCards, the field in your table that contains the number of cards wanted for each record, must be bound to a control on the report, but you can make it invisible.

Note also that this code handles NumCards < 1 and NumCards > the number of cards per page. If necessary, multiple pages will be printed to create as many cards as needed, but each customer's cards will start on a new page.

This code can be adapted to any report simply by changing the Report_Open event procedure. I'm thinking of turning this into a class that could be used easily in any report. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[scroce]

Wow - you oughta get an award for this. It's great! In addition to what you have written, I was able to come up with another example of how this can be done, but on comparison of the two, I think yours is more concise and slightly easier to understand.

Here's what I have found that also seems to work pretty well. It's actually posted on support.microsoft.com, search on Microsoft Knowledge Base Article - 231851

you'll get a test access database that gives you the following code: (it also shows you how to skip records, but since i wasn't using it, I commented it out]

Option Compare Database
Option Explicit

    'Dim intLabelBlanks As Integer
    Dim intLabelCopies As Integer
    'Dim intBlankCount As Integer
    Dim intCopyCount As Integer

      '==========================================================
         ' The following function will cause an inputbox to
         ' display when the report is run that prompts the user
         ' for the number of used labels to skip and how many
         ' copies of each label should be printed.
      '===========================================================

      Function LabelSetup()
       ' intLabelBlanks = Val(InputBox$("Enter Number of blank labels to skip"))
        'intLabelCopies = Val(InputBox$("Enter Number of Copies to Print"))
       intLabelCopies = 4
       ' If intLabelBlanks < 0 Then intLabelBlanks = 0
        If intLabelCopies < 1 Then intLabelCopies = 1
      End Function

      '===========================================================
         ' The following function sets the variables to a zero
      '===========================================================

      Function LabelInitialize()
       '  intBlankCount = 0
         intCopyCount = 0
      End Function

      '===========================================================
         ' The following function is the main part of this code
         ' that allows the labels to print as the user desires.
      '===========================================================

      Function LabelLayout(R As Report)
         'If intBlankCount < intLabelBlanks Then
         '   R.NextRecord = False
         '   R.PrintSection = False
         '   intBlankCount = intBlankCount + 1
         'Else
            If intCopyCount < (intLabelCopies - 1) Then
               R.NextRecord = False
               intCopyCount = intCopyCount + 1
            Else
               intCopyCount = 0
          '  End If
         End If
      End Function

This works, but as I read and tried your code, I still hadn't figured out how to get the amount of records to print based on the underlying field called NumOfVehicles.

Also, there was one thing that stumped me for a good long while about this code. You must make sure to put the following in the properties of the report you are using:

in the Detail section - Print Event, you must put
=LabelLayout(Reports!rptNameOfYourReport)

in the onOpen event of the Report itself, you must put:
=LabelSetup()

in the onActivate event of the Report itest, you must put:
=LabelInitialize()

After comparing these two, your example is extremely well documented, and it was basically ready to use, like you said.

I think a lot of confusion comes from not understanding how access actually builds and formats a report.

Kudos to you - would you mind if I posted your solution on a different discussion board? - i'm sure they would find this solution very helpful as well.

Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[RickSpr]

Scroce,

Thank you for the praise. I actually thought it could use a little more polishing, but was too tired to continue (I'm home ill today).

Of course you may post it elsewhere, and thank you for asking. I'd be grateful if you mentioned me as author. You never know when a prospective employer or client might recognize your name from such a mention.

BTW, the general approach to this solution (but none of the code) came from Access 2000 Developer's Handbook by Getz, Litwin, and Gilbert. They used this approach to display a graphical form that looks like a sheet of labels. The user can click on a label to designate the first one to print. This is similar to the &quot;skip labels&quot; function of the code you gave.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein