How do I overlay variable items on an imported image used for a re

  • Thread starter Thread starter drewship
  • Start date Start date
D

drewship

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
 
Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!
 
I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
 
Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

Duane Hookom said:
I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


drewship said:
Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!
Click to expand...
Click to expand...
 
Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

Duane Hookom said:
I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


drewship said:
Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

I have tried linking the Master and Child fields but am not clear on " Then
you could possibly use the Link Master/Child with a couple text boxes on the
main report with control sources of
= 1
= 2"

I am using the Order Management Database I downloaded from Microsoft as the
template for my database. The query below will display all records from the
[Order Details] table.


SELECT [Order Details].*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended
Price]
FROM [Order Details];

When I try to modify your code below, popups appear asking for Requisition
number before the main form will even open and the data on the report shows
ERROR.

SELECT IIf([Requisition Number] In (SELECT TOP 5 [Requisition Number] FROM
ORDERS O ORDER BY
[Requisition Number]),1,2) AS GroupNum, Orders.*
FROM Orders;

Can you explain what the different parts of your code are doing and how do
perform the Master/Child links?

Thanks!!
Andrew

Duane Hookom said:
Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

Duane Hookom said:
I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
I assumed you needed to divide your records into two groups. If the query I
suggested doesn't work then you need to fix it. Is there a [Requesition
Number] field in your Orders table?

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I have tried linking the Master and Child fields but am not clear on " Then
you could possibly use the Link Master/Child with a couple text boxes on the
main report with control sources of
= 1
= 2"

I am using the Order Management Database I downloaded from Microsoft as the
template for my database. The query below will display all records from the
[Order Details] table.


SELECT [Order Details].*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended
Price]
FROM [Order Details];

When I try to modify your code below, popups appear asking for Requisition
number before the main form will even open and the data on the report shows
ERROR.

SELECT IIf([Requisition Number] In (SELECT TOP 5 [Requisition Number] FROM
ORDERS O ORDER BY
[Requisition Number]),1,2) AS GroupNum, Orders.*
FROM Orders;

Can you explain what the different parts of your code are doing and how do
perform the Master/Child links?

Thanks!!
Andrew

Duane Hookom said:
Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

:

I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
I do need to divide them into 2 groups. I do not understand all of your code
or how the text boxes with =1 and =2 work. The report image contains both
page 1 and page 2 all contained within the detail section of the main report.
It looks like your code will pick the first 5 records and identify them with
a 1, and all the rest are identified with a 2. I do not know how to use the
text boxes as you indicated to display the subreports on each page of the
main report.

I do have a [Requesition Number] field in the Orders table which is what is
confusing. I thought I understood what the code was doing. I will continue to
work on these issues and hopefully can figure them out.

Thanks for your help!!
Andrew

Duane Hookom said:
I assumed you needed to divide your records into two groups. If the query I
suggested doesn't work then you need to fix it. Is there a [Requesition
Number] field in your Orders table?

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I have tried linking the Master and Child fields but am not clear on " Then
you could possibly use the Link Master/Child with a couple text boxes on the
main report with control sources of
= 1
= 2"

I am using the Order Management Database I downloaded from Microsoft as the
template for my database. The query below will display all records from the
[Order Details] table.


SELECT [Order Details].*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended
Price]
FROM [Order Details];

When I try to modify your code below, popups appear asking for Requisition
number before the main form will even open and the data on the report shows
ERROR.

SELECT IIf([Requisition Number] In (SELECT TOP 5 [Requisition Number] FROM
ORDERS O ORDER BY
[Requisition Number]),1,2) AS GroupNum, Orders.*
FROM Orders;

Can you explain what the different parts of your code are doing and how do
perform the Master/Child links?

Thanks!!
Andrew

Duane Hookom said:
Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

:

I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
The query I suggested should add a 1 to a column in the first 5 records and 2
in following records. You can use a text box on your report with a control
source of =1 as the Link Master to filter a subreport to only those records
with a 1 in the column.

--
Duane Hookom
Microsoft Access MVP


drewship said:
I do need to divide them into 2 groups. I do not understand all of your code
or how the text boxes with =1 and =2 work. The report image contains both
page 1 and page 2 all contained within the detail section of the main report.
It looks like your code will pick the first 5 records and identify them with
a 1, and all the rest are identified with a 2. I do not know how to use the
text boxes as you indicated to display the subreports on each page of the
main report.

I do have a [Requesition Number] field in the Orders table which is what is
confusing. I thought I understood what the code was doing. I will continue to
work on these issues and hopefully can figure them out.

Thanks for your help!!
Andrew

Duane Hookom said:
I assumed you needed to divide your records into two groups. If the query I
suggested doesn't work then you need to fix it. Is there a [Requesition
Number] field in your Orders table?

--
Duane Hookom
Microsoft Access MVP


drewship said:
Duane,

I have tried linking the Master and Child fields but am not clear on " Then
you could possibly use the Link Master/Child with a couple text boxes on the
main report with control sources of
= 1
= 2"

I am using the Order Management Database I downloaded from Microsoft as the
template for my database. The query below will display all records from the
[Order Details] table.


SELECT [Order Details].*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended
Price]
FROM [Order Details];

When I try to modify your code below, popups appear asking for Requisition
number before the main form will even open and the data on the report shows
ERROR.

SELECT IIf([Requisition Number] In (SELECT TOP 5 [Requisition Number] FROM
ORDERS O ORDER BY
[Requisition Number]),1,2) AS GroupNum, Orders.*
FROM Orders;

Can you explain what the different parts of your code are doing and how do
perform the Master/Child links?

Thanks!!
Andrew

:

Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

:

I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
I would never do any business with $teve who continually is the only person
who advertises for work in a free support site. $teve's behaviour goes
against the standards everyone else seems to be able to follow.
 
Steve said:
Drew,

I can implement what you want. I provide help with Access, Excel and Word
applications for a small fee. My fee to help you would be very modest.
Contact me if you are interested in having me help you and I will quote
you my fee. You will be pleasantly surprised.

Steve
(e-mail address removed)
Click to expand...


You can not tell the difference between specific code and generic code and
you call your self an "expert"?


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
Steve said:
You think you are such a smart-ass bitch, tell
the Op how to do what he wants to do.
Click to expand...

A really professional, business-like response, Steve. I guess you knew you
didn't have any hope of getting business out of this thread, anyway, didn't
you?
 
drewship said:
I have imported the picture of a form we use and
I need to overlay report data on it. I have figured
out how to overlay data with a single value like
the record number or authorized signature listed
in the record. What I need is to have multiple items
overlay from top to bottom in a specific section.
Click to expand...

It is possible to use a picture/image as background or display in an image
control. You don't have capacity to place specific multiple records on the
image in design view. The way Access reports work, they are defined in
bands, the lowest level being the Detail Section. Even since the days of
Access 1.0, the best advice has been to create a report that reproduces the
look of your form (which may, as you were advised, need to include a
subreport for the detail lines you describe). I learned early-on that
scanning a form, placing controls in the proper position with the image
displayed in desig view was clumsy and slow while the image repainted with
every design change. Hardware and imaging are more powerful and more
efficient these days, so doing something similar might be only "a major
irritant" rather than a "drive you up the wall experience".

Larry Linson
Microsoft Office Access MVP
 
Thanks Duane. I have a better understanding now which will hopefully allow me
to work this out.

Thanks again!!
Andrew

Duane Hookom said:
The query I suggested should add a 1 to a column in the first 5 records and 2
in following records. You can use a text box on your report with a control
source of =1 as the Link Master to filter a subreport to only those records
with a 1 in the column.

--
Duane Hookom
Microsoft Access MVP


drewship said:
I do need to divide them into 2 groups. I do not understand all of your code
or how the text boxes with =1 and =2 work. The report image contains both
page 1 and page 2 all contained within the detail section of the main report.
It looks like your code will pick the first 5 records and identify them with
a 1, and all the rest are identified with a 2. I do not know how to use the
text boxes as you indicated to display the subreports on each page of the
main report.

I do have a [Requesition Number] field in the Orders table which is what is
confusing. I thought I understood what the code was doing. I will continue to
work on these issues and hopefully can figure them out.

Thanks for your help!!
Andrew

Duane Hookom said:
I assumed you needed to divide your records into two groups. If the query I
suggested doesn't work then you need to fix it. Is there a [Requesition
Number] field in your Orders table?

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I have tried linking the Master and Child fields but am not clear on " Then
you could possibly use the Link Master/Child with a couple text boxes on the
main report with control sources of
= 1
= 2"

I am using the Order Management Database I downloaded from Microsoft as the
template for my database. The query below will display all records from the
[Order Details] table.


SELECT [Order Details].*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended
Price]
FROM [Order Details];

When I try to modify your code below, popups appear asking for Requisition
number before the main form will even open and the data on the report shows
ERROR.

SELECT IIf([Requisition Number] In (SELECT TOP 5 [Requisition Number] FROM
ORDERS O ORDER BY
[Requisition Number]),1,2) AS GroupNum, Orders.*
FROM Orders;

Can you explain what the different parts of your code are doing and how do
perform the Master/Child links?

Thanks!!
Andrew

:

Creating a subreport from a subform is automated from the database window. It
shouldn't be too difficult to create a subreport.

Did you try to use the link master/child as I suggested? This is used to
filter the records displayed in the subreport based on values from the main
report.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I created a subreport like you recommended. I created it from the Order
Details Extended table which contains the required information...at least I
thought this would work, but it displays every record in the table instead of
only the selected data.

The data that the subreport needs to display is from a subform. I select a
Company or OrderNumber from the main form and the associated records are
displayed on the subform. I then need the subform data to be displayed on the
subreport. I tried to create a subreport from the subform but could not see
how to do this. I also tried to tie the subreport cell recordsources to the
form cells hoping to display the data. This only displayed the first record
multiple times in the subreport.

Any ideas on how to create a subreport based on a subform, or how to link a
subreport to a subform in a similar manner that a subform and mainform are
linked?

Thanks!!

:

I'm not sure how your report is layed out but you could add a calculated
column in the subreport record source that identifies the first 5 records.
For instance with the Orders table in Northwind:

SELECT IIf([OrderID] In (SELECT TOP 5 OrderID FROM ORDERS O ORDER BY
OrderID),1,2) AS GroupNum, Orders.*
FROM Orders

Then you could possibly use the Link Master/Child with a couple text boxes
on the main report with control sources of
= 1
= 2
--
Duane Hookom
Microsoft Access MVP


:

Thanks for replying Duane. So you are saying I should create a regular
subreport of the items and prices, then overlay this on the report image?
That sounds like it would work.

I have another question then. The report may have multiple pages depending
on the number of items listed. The first page of the main report only has
room for 5 items. After that, a continuation page is used which can list 20
or 30 items. If 10 items are ordered, how do I tell the subreport to place
the first 5 items on the main report page and the last 5 items on the
continuation page?

Thanks!!

:

You may need to use a subreport to display the details.
--
Duane Hookom
Microsoft Access MVP


:

Hello all.

I have imported the picture of a form we use and I need to overlay report
data on it. I have figured out how to overlay data with a single value like
the record number or authorized signature listed in the record. What I need
is to have multiple items overlay from top to bottom in a specific section.
For example, if the customer ordered an Apple, Orange, Pear, how do I take
these items from the associated record and display them and their associated
cost like:

Apple $1.00

Orange $.75

Pear $$.50


If I associate the Item to three specific locations on the form image, I can
only select ItemName from the record since that is where the items are
stored. Consequently, the report prints:

Apple $1.00

Apple $1.00

Apple $1.00

How can I sequence through the items in a table to display each item in the
table?
Click to expand...
Click to expand...
Click to expand...
 
Thank you for replying Larry.

I initially thought that creating a report to look like my paper version
would be the way to go, but I do not really understand how they are set up
and have been hesitant to try. It looked like I could do the same thing
overlaying the data on an image which seems easier...but only if the data is
confined to a single entry.

Although I will continue to pursue the overlay and subreports using Duane's
suggestions for the short term, I will probably also try to create the form
using Access reports for flexibility in the long term.

Can you recommend resources on Access report writing?

Thanks again!!
Andrew
 
Steve said:
And you become more of a
Click to expand...

You're a particularly nasty piece of work aren't you? Not only that, you're
too stupid to realise that your potential "clients" (or victims) read such
nastiness and give you the wide berth you deserve. Your feet must be more
or less completely shot away by now.
 
Back
Top