P
PC Datasheet
How do you create blank lines with lines to fill such reports as invoices
and purchase orders?
Thanks!
Marie
and purchase orders?
Thanks!
Marie
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Honest Abe
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
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
M
Marshall Barton
PC said: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.
M
Marie
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
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
M
Marshall Barton
Marie said: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.
P
PC Datasheet
See answers to questions below ---
Steve
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.
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.
Steve
Marshall Barton said: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.
M
Marshall Barton
Continuing inline below
--
Marsh
MVP [MS Access]
Gotch ya.
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.
--
Marsh
MVP [MS Access]
PC said:See answers to questions below ---
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.
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.
P
PC Datasheet
<<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
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 said:Continuing inline below
--
Marsh
MVP [MS Access]
PC said:See answers to questions below ---
match
the returns
28
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.
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.
H
Honest Abe
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
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
P
PC Datasheet
And if you are so honest, why don't you use your real name? Practice what
you preach!
you preach!
H
Honest Abe
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
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