Blank Check Report

Posted on 2012-08-16
Last Modified: 2012-08-17
I have a report named VoucherBlanks created in Access 2003 that will create a one-page blank voucher check complete with check no, routing number, etc on the bottom of the check.  I also have a form I created called BlankMenu with fields, NumberChecks and StartingCheck.  I want to use this form to create a report that will generate x number of checks with starting check number y using the VoucherBlanks report.

Basically, given x number of blank checks and starting check number y, I want to create a report that's x pages long and each page has a check number of it that starts at y and increments up to y+x-1.

Page 1  Check No y
Page 2  Check No y+1
Page 3  Check No y+2
Page x  Check No y+x-1

Sorry for the confusing explanation, I think it's an easy concept just not too easy to explain.  Any help would be greatly appreciated.
Question by:jvolz
    LVL 30

    Expert Comment

    A sample database helps.
    LVL 29

    Accepted Solution

    The record source for your report called VoucherBlanks needs to point to a temp table.  Assuming your form has a button to print checks, you need to add code to its OnClick event like this:

        Dim i As Integer
        Dim strSQL As String
        If DCount("*", "MSysObjects", "Name='tblTempChecks'") > 0 Then DoCmd.DeleteObject acTable, "tblTempChecks"
        DoCmd.RunSQL "Create Table tblTempChecks (CheckNumber Text(10))"
        For i = Me.StartingCheck To Me.NumberChecks - 1
            strSQL = "Insert Into tblTempChecks (CheckNumber) Values ('" & i & "')"
            CurrentDb.Execute strSQL, dbFailOnError
        DoCmd.OpenReport "VoucherBlanks"

    Open in new window


    Author Comment

    I see where you're going with this, IrogSinta, but it still isn't working for me.  Basically the table, tblTempChecks was created, but nothing else is happening.  I understand better what I need, I want this table with field CheckNumber to create a record with the StartingCheck number and keep adding a new record, increment the Starting Check number by 1 until the CheckNumber reaches StartingCheck + NumberChecks -1 or NumberChecks times.  Once I can get that table and those records created, I can just set the recordsource to the tblTempChecks and print because the only value that will change in the blank checks is the check number.

    In a nutshell this is what I'm trying to do,

    For i= 1 to NumberChecks do

    Create record tbleTempChecks.CheckNumber and  set value to i
    Repeat loop

    Thanks again.
    LVL 29

    Expert Comment

    Did the code I gave you not create the table with those numbers? If it did, did you change the record source to point to that temp table?

    Author Comment

    I did change the record source on the report, the problem was that the table had no values in it.  Actually, I put a msgbox in the loop to see what would happen but it didn't seem to be getting into the loop because no messages showed up.

    Author Comment

    I figured out where the problem was.  I tried a test with StartingCheck = 5, NumberChecks =5.

    Your line:
    For i = Me.StartingCheck To Me.NumberChecks - 1 --> for i = 5 to 4

    I changed to

    For i = Me.StartingCheck To (Me.StartingCheck + Me.NumberChecks - 1)

    and everything works perfect!  Thanks for the assistance!!
    LVL 30

    Expert Comment

    If you need to print  1 check per page,  sequenced from y, then :

    It is straight forward. with no need for extra tables and unnecessary coding.

    Instead of saying if you run the report from a form, then... and if ...

    We ask for a skeleton database which answers the many ifs..

    Your database will show:
        the setup as if you are going to print one page manually.
        where the initial number and number of checks will be taken (a global variable, a textbox on a form, ...)
    LVL 30

    Expert Comment

    You accepted a solution, but please keep in mind, there is a better solution if you supply information asked for. Understanding the problem is invaluable in giving a better solution.

    Author Comment

    I did see your request for a sample database, but due to the fact that these are checks that we'll be printing, with routing/account numbers involved, I was reluctant to send a sample on the off-chance that I might forget to remove something and didn't want to recreate a new database to send.  I hope you can appreciate my concerns but I do appreciate your consideration of my post.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now