By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

Blanl Lines

P: n/a
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?

Thanks!

Marie
Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Steve,

Take off the dress and the pantyhose because you're not fooling anyone with
that 5 o’clock shadow. Stop masquerading as a woman so you can have a real
expert write the answer for you so you can charge your customer the
consultation fee for answering the question.

Or maybe your customer "Marie" can purchase a copy of your collection of VBA
code, available on CD for only $125! It's full of solutions like this,
because you copied them from the experts here!

Abe
"PC Datasheet" <no****@nospam.spam> wrote in message
news:BY****************@newsread3.news.pas.earthli nk.net...
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?

Thanks!

Marie

Nov 13 '05 #2

P: n/a
PC Datasheet wrote:
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?


The way I've done this is pretty messy. Try Googling the
archives for previous threads on this problem.

I'd Like to think there's a better way by using the Page
event to execute the Line method. If you can't find
something useful, post back and I'll see what I can work
out.

--
Marsh
MVP [MS Access]
Nov 13 '05 #3

P: n/a
Marsh,

I appreciate your response!

Here's an outline of what I used ---------------
Data for my report comes from a query. The query includes the primary key
and five fields from a table.
1. Create TblBlankLine:
TblBlankLine
ARow
A1
A2
A3
A4
A5
Set ARow data type to long integer and A1 to A5 data types to match the
data types of the fields in the query. ARow is numbered 5000001 to 5000028
2. I needed a total of 28 records and blank lines in my report. Put the
following in a standard module:
Function BlankLines()
Dim Items As Integer
Items = DCount("*", "MyQuery")
BlankLines = 28 - Items
End Function
3. Union MyQuery with TblBlankLine and include the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
4. Put the following in the ReportHeader_Format event:
Call BlankLines

When the report opens, BlankLines is calculated. The Union query returns 28
records; whatever number of records from MyQuery and the remainder as blank
lines. Note that the fields in the report must show their borders.

Steve
"Marshall Barton" <ma*********@wowway.com> wrote in message
news:nk********************************@4ax.com...
PC Datasheet wrote:
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?


The way I've done this is pretty messy. Try Googling the
archives for previous threads on this problem.

I'd Like to think there's a better way by using the Page
event to execute the Line method. If you can't find
something useful, post back and I'll see what I can work
out.

--
Marsh
MVP [MS Access]

Nov 13 '05 #4

P: n/a
Marie wrote:
Here's an outline of what I used ---------------
Data for my report comes from a query. The query includes the primary key
and five fields from a table.
1. Create TblBlankLine:
TblBlankLine
ARow
A1
A2
A3
A4
A5
Set ARow data type to long integer and A1 to A5 data types to match the
data types of the fields in the query. ARow is numbered 5000001 to 5000028
2. I needed a total of 28 records and blank lines in my report. Put the
following in a standard module:
Function BlankLines()
Dim Items As Integer
Items = DCount("*", "MyQuery")
BlankLines = 28 - Items
End Function
3. Union MyQuery with TblBlankLine and include the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
4. Put the following in the ReportHeader_Format event:
Call BlankLines

When the report opens, BlankLines is calculated. The Union query returns 28
records; whatever number of records from MyQuery and the remainder as blank
lines. Note that the fields in the report must show their borders.

Thanks for posting back with your results. I think that's a
better approach than either of the ones I was thinking of.

Two minor questions:
1 ) Why number the blank lines way up in the 5000000 range?
That's a reasonable way if the invoice details are sorted by
their key value (row number).

2) Why do you call the BlankLines function in the Open
event? I don't see where this does anything!?
--
Marsh
MVP [MS Access]

P.S. How about fixing your From and Sig? It's kind of
confusing with all these different names.
Nov 13 '05 #5

P: n/a
See answers to questions below ---

Steve
"Marshall Barton" <ma*********@wowway.com> wrote in message
news:mj********************************@4ax.com...
Marie wrote:
Here's an outline of what I used ---------------
Data for my report comes from a query. The query includes the primary key
and five fields from a table.
1. Create TblBlankLine:
TblBlankLine
ARow
A1
A2
A3
A4
A5
Set ARow data type to long integer and A1 to A5 data types to match thedata types of the fields in the query. ARow is numbered 5000001 to 50000282. I needed a total of 28 records and blank lines in my report. Put the
following in a standard module:
Function BlankLines()
Dim Items As Integer
Items = DCount("*", "MyQuery")
BlankLines = 28 - Items
End Function
3. Union MyQuery with TblBlankLine and include the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
4. Put the following in the ReportHeader_Format event:
Call BlankLines

When the report opens, BlankLines is calculated. The Union query returns 28records; whatever number of records from MyQuery and the remainder as blanklines. Note that the fields in the report must show their borders.

Thanks for posting back with your results. I think that's a
better approach than either of the ones I was thinking of.

Two minor questions:
1 ) Why number the blank lines way up in the 5000000 range?
That's a reasonable way if the invoice details are sorted by
their key value (row number).


ARow matches up to InvoiceID in the Union query. So the Where clause becomes
a filter both to the InvoiceDetail records that are displayed as well as the
blank records from TblBlank records that are displayed. The 5000000 range is
insurance that no InvoiceDetail records get missed. If for example ARow in
TblBlankLines was numbered 1 to 30 and the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+0)
was used, any InvoiceDetail record with InvoiceID greater than
(BlankLines()+0) would never get printed.

2) Why do you call the BlankLines function in the Open
event? I don't see where this does anything!?
The BlankLines function is called in the ReportHeader_Format event. This
calculates the value of BlankLines() used in the Where clause of the union
query.

--
Marsh
MVP [MS Access]

P.S. How about fixing your From and Sig? It's kind of
confusing with all these different names.

Nov 13 '05 #6

P: n/a
Continuing inline below
--
Marsh
MVP [MS Access]
PC Datasheet wrote:
See answers to questions below ---
Marie wrote:
>Here's an outline of what I used ---------------
>Data for my report comes from a query. The query includes the primary key
>and five fields from a table.
>1. Create TblBlankLine:
> TblBlankLine
> ARow
> A1
> A2
> A3
> A4
> A5
> Set ARow data type to long integer and A1 to A5 data types to matchthe >data types of the fields in the query. ARow is numbered 5000001 to5000028 >2. I needed a total of 28 records and blank lines in my report. Put the
>following in a standard module:
>Function BlankLines()
>Dim Items As Integer
>Items = DCount("*", "MyQuery")
>BlankLines = 28 - Items
>End Function
>3. Union MyQuery with TblBlankLine and include the Where clause:
>WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
>4. Put the following in the ReportHeader_Format event:
>Call BlankLines
>
>When the report opens, BlankLines is calculated. The Union query returns28 >records; whatever number of records from MyQuery and the remainder asblank >lines. Note that the fields in the report must show their borders.


"Marshall Barton" wrote
Thanks for posting back with your results. I think that's a
better approach than either of the ones I was thinking of.

Two minor questions:
1 ) Why number the blank lines way up in the 5000000 range?
That's a reasonable way if the invoice details are sorted by
their key value (row number).


ARow matches up to InvoiceID in the Union query. So the Where clause becomes
a filter both to the InvoiceDetail records that are displayed as well as the
blank records from TblBlank records that are displayed. The 5000000 range is
insurance that no InvoiceDetail records get missed. If for example ARow in
TblBlankLines was numbered 1 to 30 and the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+0)
was used, any InvoiceDetail record with InvoiceID greater than
(BlankLines()+0) would never get printed.


Gotch ya.

2) Why do you call the BlankLines function in the Open
event? I don't see where this does anything!?


The BlankLines function is called in the ReportHeader_Format event. This
calculates the value of BlankLines() used in the Where clause of the union
query.


But you call the function from the query, so calling it
again later doesn't affect the query.

If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:

Items = DCount("*", "MyQuery") Mod 28

You came up with a nice approach here Steve and I'm glad I
stumbled into it.
Nov 13 '05 #7

P: n/a
<<But you call the function from the query, so calling it
again later doesn't affect the query.>>

I see your point and I'm going to test it out!

<<If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:

Items = DCount("*", "MyQuery") Mod 28>>

Won't usually work because pages after page 1 usually don't have the report
header so more than 28 total lines will fit on the page.

In my current case I knew there would never be more than one page. However
when there is a possibility of the report being two pages but not more, you
might use something like this in the BlankLines function:
Items = DCount("*", "MyQuery")
Select Case Items
Case <= 28
BlankLines = 28 - Items
Case Else
BlankLines = 70 - Items
End Select

Here's something else I picked up off www.mvps.org/access that you might
want to use:
DoCmd.OpenReport "MyReport", acViewPreview
DoCmd.Maximize
Reports!MyReport.ZoomControl = 90

Your report will open at 90% magnification which is a perfect fit in the
report window.

Marsh,

I have always had a great respect for you from watching your responses to
posts. You have gained another big notch in my respect for you for your
courteous and professional response in this thread! I look forward to
continuing watching your answers to posts.

Steve


"Marshall Barton" <ma*********@wowway.com> wrote in message
news:nc********************************@4ax.com...
Continuing inline below
--
Marsh
MVP [MS Access]
PC Datasheet wrote:
See answers to questions below ---
Marie wrote:
>Here's an outline of what I used ---------------
>Data for my report comes from a query. The query includes the primary key >and five fields from a table.
>1. Create TblBlankLine:
> TblBlankLine
> ARow
> A1
> A2
> A3
> A4
> A5
> Set ARow data type to long integer and A1 to A5 data types to match
the
>data types of the fields in the query. ARow is numbered 5000001 to

5000028
>2. I needed a total of 28 records and blank lines in my report. Put
the >following in a standard module:
>Function BlankLines()
>Dim Items As Integer
>Items = DCount("*", "MyQuery")
>BlankLines = 28 - Items
>End Function
>3. Union MyQuery with TblBlankLine and include the Where clause:
>WHERE TblBlankLine.ARow<=(BlankLines()+5000000)
>4. Put the following in the ReportHeader_Format event:
>Call BlankLines
>
>When the report opens, BlankLines is calculated. The Union query
returns28
>records; whatever number of records from MyQuery and the remainder as

blank
>lines. Note that the fields in the report must show their borders.

"Marshall Barton" wrote
Thanks for posting back with your results. I think that's a
better approach than either of the ones I was thinking of.

Two minor questions:
1 ) Why number the blank lines way up in the 5000000 range?
That's a reasonable way if the invoice details are sorted by
their key value (row number).


ARow matches up to InvoiceID in the Union query. So the Where clause becomesa filter both to the InvoiceDetail records that are displayed as well as theblank records from TblBlank records that are displayed. The 5000000 range isinsurance that no InvoiceDetail records get missed. If for example ARow inTblBlankLines was numbered 1 to 30 and the Where clause:
WHERE TblBlankLine.ARow<=(BlankLines()+0)
was used, any InvoiceDetail record with InvoiceID greater than
(BlankLines()+0) would never get printed.


Gotch ya.

2) Why do you call the BlankLines function in the Open
event? I don't see where this does anything!?


The BlankLines function is called in the ReportHeader_Format event. This
calculates the value of BlankLines() used in the Where clause of the unionquery.


But you call the function from the query, so calling it
again later doesn't affect the query.

If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:

Items = DCount("*", "MyQuery") Mod 28

You came up with a nice approach here Steve and I'm glad I
stumbled into it.

Nov 13 '05 #8

P: n/a
Steve,

See? You’ll get offers to help you when you post a question as Steve the
Access developer, instead of posing as a woman who needs help with her
Access database. You got quality feedback that was targeted to your level of
expertise, not basic instructions on how to proceed based on someone's guess
about your experience level. Marsh didn't have to spend extra time writing
stuff you didn't need, but that he probably would have given to someone
assumed to be less experienced than you are. You should always post as Steve
Santus or PC Datasheet so people know how best to help you without wasting
their time or yours.

I count 70 messages you've posted in the NGs in the last three months while
you've pretended to be other people, mainly women. I doubt that's anywhere
near the actual number of your covert posts. You masquerade as so many
different people so that you can get experts to unknowingly write the step
by step instructions for you to copy, and then you charge your customers for
these written instructions without compensating the guys who did the actual
work. The next time I see you doing that I will point it out to everybody,
especially if I see you wearing a dress and high heels again so everybody
else can laugh at you too.

Abe

"PC Datasheet" <no****@nospam.spam> wrote in message
news:A_*****************@newsread3.news.pas.earthl ink.net...
<<But you call the function from the query, so calling it
again later doesn't affect the query.>>

I see your point and I'm going to test it out!

<<If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:

Items = DCount("*", "MyQuery") Mod 28>>

Won't usually work because pages after page 1 usually don't have the report header so more than 28 total lines will fit on the page.

-snip-

Marsh,

I have always had a great respect for you from watching your responses to
posts. You have gained another big notch in my respect for you for your
courteous and professional response in this thread! I look forward to
continuing watching your answers to posts.

Steve
-snip-
"PC Datasheet" <no****@nospam.spam> wrote in message
news:BY****************@newsread3.news.pas.earthl ink.net...
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?

Thanks!

Marie

Nov 13 '05 #9

P: n/a
And if you are so honest, why don't you use your real name? Practice what
you preach!
"Honest Abe" <Ne*******************@NeverBox.comREMOVE_THIS> wrote in
message news:F4TNd.169$uc.157@trnddc01...
Steve,

See? You’ll get offers to help you when you post a question as Steve the
Access developer, instead of posing as a woman who needs help with her
Access database. You got quality feedback that was targeted to your level of expertise, not basic instructions on how to proceed based on someone's guess about your experience level. Marsh didn't have to spend extra time writing
stuff you didn't need, but that he probably would have given to someone
assumed to be less experienced than you are. You should always post as Steve Santus or PC Datasheet so people know how best to help you without wasting
their time or yours.

I count 70 messages you've posted in the NGs in the last three months while you've pretended to be other people, mainly women. I doubt that's anywhere
near the actual number of your covert posts. You masquerade as so many
different people so that you can get experts to unknowingly write the step
by step instructions for you to copy, and then you charge your customers for these written instructions without compensating the guys who did the actual work. The next time I see you doing that I will point it out to everybody,
especially if I see you wearing a dress and high heels again so everybody
else can laugh at you too.

Abe

"PC Datasheet" <no****@nospam.spam> wrote in message
news:A_*****************@newsread3.news.pas.earthl ink.net...
<<But you call the function from the query, so calling it
again later doesn't affect the query.>>

I see your point and I'm going to test it out!

<<If your invoice could possible go to more than one page and
you still need to fill out the last page, change the
function to use:

Items = DCount("*", "MyQuery") Mod 28>>

Won't usually work because pages after page 1 usually don't have the

report
header so more than 28 total lines will fit on the page.


-snip-

Marsh,

I have always had a great respect for you from watching your responses to posts. You have gained another big notch in my respect for you for your
courteous and professional response in this thread! I look forward to
continuing watching your answers to posts.

Steve


-snip-
"PC Datasheet" <no****@nospam.spam> wrote in message
news:BY****************@newsread3.news.pas.earthl ink.net...
How do you create blank lines with lines to fill such reports as invoices and purchase orders?

Thanks!

Marie


Nov 13 '05 #10

P: n/a
My name is Abe de los Santos. Does my name have any meaning to you? It
doesn't because you don't know me. People know you though. Your reputation
has spread far and wide because you try to deceive and take advantage of so
many of the very generous people here.

And since I do practice what I preach, I'll be honest with you. You're
shameless. And you look terrible in a dress and pantyhose.

Abe

"PC Datasheet" <no****@nospam.spam> wrote in message
news:Cf**************@newsread3.news.pas.earthlink .net...
And if you are so honest, why don't you use your real name? Practice what
you preach!

Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.