We help IT Professionals succeed at work.

Blank Check Report

Jerry Volz
Jerry Volz asked
on
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.
Comment
Watch Question

Hamed NasrRetired IT Professional
BRONZE EXPERT

Commented:
A sample database helps.
SILVER EXPERT
Commented:
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
    Next

    DoCmd.OpenReport "VoucherBlanks"

Open in new window

Author

Commented:
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
i=i+1
Repeat loop

Thanks again.
SILVER EXPERT

Commented:
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

Commented:
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

Commented:
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!!
Hamed NasrRetired IT Professional
BRONZE EXPERT

Commented:
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..
http:#38300744

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, ...)
Hamed NasrRetired IT Professional
BRONZE EXPERT

Commented:
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

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.