Form/Report Relationship

My main form “ Purchase Order� has a subform “Order Details Subform�.

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a “1� on the Order Table
“OrderID� and a “Many� on the Order Details Table “OrderID�.

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine along
with all the subform information. When I complete a new purchuse order, ie
PO #2 and hit the print preview for the report, the report now displays the
new customer information from Purchase Order #2 and all of the data from the
subform on Purchase Order #1 & #2. What am I doing wrong?



..

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Thank you so much...


I am still very new to this so as to the Where clause. I guess I am not
using it because don't know what it is. If you want to add it to the code
and I can copy and paste into the SQL on my form and/ or report that would be
fantastic!

Thank you so much for your help.

Nancy
"strive4peace" wrote:

Hi Nancy,

I formatted your SQL statement to be easier to read:

SELECT Orders.OrderID AS Orders_OrderID
, Orders.[Customer ID] AS [Orders_Customer ID]
, Employees.FirstName
, Orders.OrderDate
, Orders.PurchaseOrderNumber
, Orders.ShipDate
, [Shipping Methods].ShippingMethod
, Orders.Taxes
, Orders.Comment
, Orders.[Core Charge]
, Orders.[Core Charge Amount]
, [Order Details].OrderDetailID
, [Order Details].OrderID AS [Order Details_OrderID]
, [Order Details].Nomenclature
, [Order Details].[Part Number]
, [Order Details].Quantity
, [Order Details].UnitPrice
, [Order Details].[Tail #]
, [Order Details].Condition
, [Order Details].[Serial #]
, Customers.[Customer ID] AS [Customers_Customer ID]
, Customers.CompanyName
, Customers.FirstName
, Customers.LastName
, Customers.BillingAddress
, Customers.City
, Customers.StateOrProvince
, Customers.ZIPCode
, Customers.Email
, Customers.CompanyWebsite
, Customers.PhoneNumber
, Customers.FaxNumber
, Customers.ShipAddress
, Customers.ShipCity
, Customers.ShipStateOrProvince
, Customers.ShipZIPCode
, Customers.ShipPhoneNumber
, Customers.Notes
, [Order Details Extended].Subtotal
FROM ((Employees
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.[Customer ID]=Orders.[Customer ID])
ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN ([Order Details]
INNER JOIN [Order Details Extended]
ON [Order Details].OrderDetailID
=[Order Details Extended].OrderDetailID)
ON Orders.OrderID=[Order Details].OrderID)
INNER JOIN [Shipping Methods]
ON Orders.ShippingMethodID
=[Shipping Methods].ShippingMethodID;


I see there is no WHERE clause limiting the records to a particular
purchase order ... are you using the Where clause of the OpenReport
action in code to do this? If so, what is the code you use to launch the
report?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Hi Nancy,

I formatted your SQL statement to be easier to read:

SELECT Orders.OrderID AS Orders_OrderID
, Orders.[Customer ID] AS [Orders_Customer ID]
, Employees.FirstName
, Orders.OrderDate
, Orders.PurchaseOrderNumber
, Orders.ShipDate
, [Shipping Methods].ShippingMethod
, Orders.Taxes
, Orders.Comment
, Orders.[Core Charge]
, Orders.[Core Charge Amount]
, [Order Details].OrderDetailID
, [Order Details].OrderID AS [Order Details_OrderID]
, [Order Details].Nomenclature
, [Order Details].[Part Number]
, [Order Details].Quantity
, [Order Details].UnitPrice
, [Order Details].[Tail #]
, [Order Details].Condition
, [Order Details].[Serial #]
, Customers.[Customer ID] AS [Customers_Customer ID]
, Customers.CompanyName
, Customers.FirstName
, Customers.LastName
, Customers.BillingAddress
, Customers.City
, Customers.StateOrProvince
, Customers.ZIPCode
, Customers.Email
, Customers.CompanyWebsite
, Customers.PhoneNumber
, Customers.FaxNumber
, Customers.ShipAddress
, Customers.ShipCity
, Customers.ShipStateOrProvince
, Customers.ShipZIPCode
, Customers.ShipPhoneNumber
, Customers.Notes
, [Order Details Extended].Subtotal
FROM ((Employees
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.[Customer ID]=Orders.[Customer ID])
ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN ([Order Details]
INNER JOIN [Order Details Extended]
ON [Order Details].OrderDetailID
=[Order Details Extended].OrderDetailID)
ON Orders.OrderID=[Order Details].OrderID)
INNER JOIN [Shipping Methods]
ON Orders.ShippingMethodID
=[Shipping Methods].ShippingMethodID;


I see there is no WHERE clause limiting the records to a particular
purchase order ... are you using the Where clause of the OpenReport
action in code to do this? If so, what is the code you use to launch the
report?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Hi Nancy,

please specify more information:

1. is PurchaseOrderNumber the field that you need to limit output for?

2. do you have a control on the form to specify which Purchase Order
Number you want?
- If so, what is the Name property of the control?
- what is the name of your form?

3. what is the data type of PurchaseOrderNumber in your Orders table?

4. how are you currently generating the report?

for better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access

Warm Regards,
Crystal


*
(: have an awesome day
*



Nancy wrote:
Thank you so much...


I am still very new to this so as to the Where clause. I guess I am not
using it because don't know what it is. If you want to add it to the code
and I can copy and paste into the SQL on my form and/ or report that would be
fantastic!

Thank you so much for your help.

Nancy
"strive4peace" wrote:

Hi Nancy,

I formatted your SQL statement to be easier to read:

SELECT Orders.OrderID AS Orders_OrderID
, Orders.[Customer ID] AS [Orders_Customer ID]
, Employees.FirstName
, Orders.OrderDate
, Orders.PurchaseOrderNumber
, Orders.ShipDate
, [Shipping Methods].ShippingMethod
, Orders.Taxes
, Orders.Comment
, Orders.[Core Charge]
, Orders.[Core Charge Amount]
, [Order Details].OrderDetailID
, [Order Details].OrderID AS [Order Details_OrderID]
, [Order Details].Nomenclature
, [Order Details].[Part Number]
, [Order Details].Quantity
, [Order Details].UnitPrice
, [Order Details].[Tail #]
, [Order Details].Condition
, [Order Details].[Serial #]
, Customers.[Customer ID] AS [Customers_Customer ID]
, Customers.CompanyName
, Customers.FirstName
, Customers.LastName
, Customers.BillingAddress
, Customers.City
, Customers.StateOrProvince
, Customers.ZIPCode
, Customers.Email
, Customers.CompanyWebsite
, Customers.PhoneNumber
, Customers.FaxNumber
, Customers.ShipAddress
, Customers.ShipCity
, Customers.ShipStateOrProvince
, Customers.ShipZIPCode
, Customers.ShipPhoneNumber
, Customers.Notes
, [Order Details Extended].Subtotal
FROM ((Employees
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.[Customer ID]=Orders.[Customer ID])
ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN ([Order Details]
INNER JOIN [Order Details Extended]
ON [Order Details].OrderDetailID
=[Order Details Extended].OrderDetailID)
ON Orders.OrderID=[Order Details].OrderID)
INNER JOIN [Shipping Methods]
ON Orders.ShippingMethodID
=[Shipping Methods].ShippingMethodID;


I see there is no WHERE clause limiting the records to a particular
purchase order ... are you using the Where clause of the OpenReport
action in code to do this? If so, what is the code you use to launch the
report?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

Thank you so much for your help.
Here is what I have:

Purchase Order Form has: Order ID-Auto number
CustomerID-Number
Employee ID-Number
Order Date-Date/Time
Purchase Order Number-Text
And some other Misc fields

The Purchase Order Details Sub-Form has: Order Detail ID-Auto-number
OrderID-Number NomenclatureID-Number
Nomenclature-Text
Part Number-Number
Quantity-Number
And some misc fields

In design view in the subform property sheet the Master/Child link is:
OrderID/OrderID

For each purchase order number, I may have five or six parts (nomenclature)
that I am ordering from the supplier (customer). I need each purchase order
number to print in a report with just the items purchased under that Purchase
Order Number from that supplier (customer). For Example:

Purchase Order: 7155-4380
Supplier (Customer): Ram Aircraft
Employee: Nancy
Shipping Method: UPS Ground

Purchase Order Details Subform:

Nomenclatu Part Number Quantity Price Line Total
Starter Ms74887-020 1 $3000.00 $3000.00
Starter Clamp MS12345-7 2 $3.50 $7.00
Starter Brush MSabcdef-123 6 $10.00 $60.00

Subtotal $3,067.00
Tax $214.69
Order Total $3281.69

End of Purchase Order 7155-4380

At this point I want to click on Preview Purchase Order Report and then
print. The Purchase Order Report should only print this purchase order
information. When I advance to the next purchase order on the purchase order
form, i.e. 7156-4380 I want to do the same thing, only print the purchase
order report with the details of purchase order 7156-4380. I hope this makes
sense.

1. Purchase Order Number ifield is the one I need to limit, I think.
2. I am not sure I understand the question so here goes. On the Purchase
Order form there is a text box for purchase order number that I manually
input each time. The name of the form is Add an Order and Details. I would
prefer to call it Purchase Order Form, but at this point I am afraid to
change it. If you can get me going in the right direction I am sure I can
figure out how to change the name so as not to screw up any underlying forms,
tables, etc.
3. Data Type of the Purchase Order number is text.
4. I currently have a command button on the form that opens the preview
report. On the properties page of the common button on the click event it
says [Embedded Macro]. If there is a better way, I am all ears…

I started reviewing your site after the last post you did. Very interesting
reading. I will continue to read it. As I said before, I am pretty new at
this stuff so there is a learning curve. I have learned a lot so far and
this site is amazing. And I plan to continue reading.

Thank you so very much for all your help.




"strive4peace" wrote:

Hi Nancy,

please specify more information:

1. is PurchaseOrderNumber the field that you need to limit output for?

2. do you have a control on the form to specify which Purchase Order
Number you want?
- If so, what is the Name property of the control?
- what is the name of your form?

3. what is the data type of PurchaseOrderNumber in your Orders table?

4. how are you currently generating the report?

for better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access

Warm Regards,
Crystal


*
(: have an awesome day
*



Nancy wrote:
Thank you so much...


I am still very new to this so as to the Where clause. I guess I am not
using it because don't know what it is. If you want to add it to the code
and I can copy and paste into the SQL on my form and/ or report that would be
fantastic!

Thank you so much for your help.

Nancy
"strive4peace" wrote:

Hi Nancy,

I formatted your SQL statement to be easier to read:

SELECT Orders.OrderID AS Orders_OrderID
, Orders.[Customer ID] AS [Orders_Customer ID]
, Employees.FirstName
, Orders.OrderDate
, Orders.PurchaseOrderNumber
, Orders.ShipDate
, [Shipping Methods].ShippingMethod
, Orders.Taxes
, Orders.Comment
, Orders.[Core Charge]
, Orders.[Core Charge Amount]
, [Order Details].OrderDetailID
, [Order Details].OrderID AS [Order Details_OrderID]
, [Order Details].Nomenclature
, [Order Details].[Part Number]
, [Order Details].Quantity
, [Order Details].UnitPrice
, [Order Details].[Tail #]
, [Order Details].Condition
, [Order Details].[Serial #]
, Customers.[Customer ID] AS [Customers_Customer ID]
, Customers.CompanyName
, Customers.FirstName
, Customers.LastName
, Customers.BillingAddress
, Customers.City
, Customers.StateOrProvince
, Customers.ZIPCode
, Customers.Email
, Customers.CompanyWebsite
, Customers.PhoneNumber
, Customers.FaxNumber
, Customers.ShipAddress
, Customers.ShipCity
, Customers.ShipStateOrProvince
, Customers.ShipZIPCode
, Customers.ShipPhoneNumber
, Customers.Notes
, [Order Details Extended].Subtotal
FROM ((Employees
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.[Customer ID]=Orders.[Customer ID])
ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN ([Order Details]
INNER JOIN [Order Details Extended]
ON [Order Details].OrderDetailID
=[Order Details Extended].OrderDetailID)
ON Orders.OrderID=[Order Details].OrderID)
INNER JOIN [Shipping Methods]
ON Orders.ShippingMethodID
=[Shipping Methods].ShippingMethodID;


I see there is no WHERE clause limiting the records to a particular
purchase order ... are you using the Where clause of the OpenReport
action in code to do this? If so, what is the code you use to launch the
report?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.

hi Nancy,

on second thought, you may have multiple purchase order numbers for
different customers, so you would need to specify both CustomerID and
Purchase Order Number

I would use code instead of a macro to generate the report

make sure that both CustomerID and Purchase Order Number are ON your
report (CustomerID can be hidden if you do not want it to be visible --
set Visible = No)

'~~~~~~~~~~~~~~~
dim strWhere as string

strWhere = "CustomerID =" & me.CustomerID _
& " AND [Purchase Order Number] = '" _
& nz(me.Purchase_Order_Number,"") & "'"

docmd.OpenReport "Invoice", acViewNormal, ,strWhere

'~~~~~~~~~~~~~~~

"The name of the form is Add an Order and Details"

you can probably change the name of the form without any problem. Make
sure everything is closed when you do this.

It is a good idea to avoid spaces and special characters in names so,
instead of 'Purchase Order Form', I would suggest 'f_PurchaseOrder'

the f_ at the beginning indicates it is a form. Common naming
convention uses 'frm' to preface form objects but I like 'f_' better

when you make a form that will be used as a subform, I like to do this
for the name:

f_Formname_sub

Where Formname includes the main form name it is designed for (if it is
specific) and something about its use

For instance:

'f_PurchaseOrder_Details_sub'

~~~

glad you are enjoying Access Basics!


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thank you so much for your help.
Here is what I have:

Purchase Order Form has: Order ID-Auto number
CustomerID-Number
Employee ID-Number
Order Date-Date/Time
Purchase Order Number-Text
And some other Misc fields

The Purchase Order Details Sub-Form has: Order Detail ID-Auto-number
OrderID-Number NomenclatureID-Number
Nomenclature-Text
Part Number-Number
Quantity-Number
And some misc fields

In design view in the subform property sheet the Master/Child link is:
OrderID/OrderID

For each purchase order number, I may have five or six parts (nomenclature)
that I am ordering from the supplier (customer). I need each purchase order
number to print in a report with just the items purchased under that Purchase
Order Number from that supplier (customer). For Example:

Purchase Order: 7155-4380
Supplier (Customer): Ram Aircraft
Employee: Nancy
Shipping Method: UPS Ground

Purchase Order Details Subform:

Nomenclatu Part Number Quantity Price Line Total
Starter Ms74887-020 1 $3000.00 $3000.00
Starter Clamp MS12345-7 2 $3.50 $7.00
Starter Brush MSabcdef-123 6 $10.00 $60.00

Subtotal $3,067.00
Tax $214.69
Order Total $3281.69

End of Purchase Order 7155-4380

At this point I want to click on Preview Purchase Order Report and then
print. The Purchase Order Report should only print this purchase order
information. When I advance to the next purchase order on the purchase order
form, i.e. 7156-4380 I want to do the same thing, only print the purchase
order report with the details of purchase order 7156-4380. I hope this makes
sense.

1. Purchase Order Number ifield is the one I need to limit, I think.
2. I am not sure I understand the question so here goes. On the Purchase
Order form there is a text box for purchase order number that I manually
input each time. The name of the form is Add an Order and Details. I would
prefer to call it Purchase Order Form, but at this point I am afraid to
change it. If you can get me going in the right direction I am sure I can
figure out how to change the name so as not to screw up any underlying forms,
tables, etc.
3. Data Type of the Purchase Order number is text.
4. I currently have a command button on the form that opens the preview
report. On the properties page of the common button on the click event it
says [Embedded Macro]. If there is a better way, I am all ears…

I started reviewing your site after the last post you did. Very interesting
reading. I will continue to read it. As I said before, I am pretty new at
this stuff so there is a learning curve. I have learned a lot so far and
this site is amazing. And I plan to continue reading.

Thank you so very much for all your help.




"strive4peace" wrote:

Hi Nancy,

please specify more information:

1. is PurchaseOrderNumber the field that you need to limit output for?

2. do you have a control on the form to specify which Purchase Order
Number you want?
- If so, what is the Name property of the control?
- what is the name of your form?

3. what is the data type of PurchaseOrderNumber in your Orders table?

4. how are you currently generating the report?

for better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access

Warm Regards,
Crystal


*
(: have an awesome day
*



Nancy wrote:
Thank you so much...


I am still very new to this so as to the Where clause. I guess I am not
using it because don't know what it is. If you want to add it to the code
and I can copy and paste into the SQL on my form and/ or report that would be
fantastic!

Thank you so much for your help.

Nancy
"strive4peace" wrote:

Hi Nancy,

I formatted your SQL statement to be easier to read:

SELECT Orders.OrderID AS Orders_OrderID
, Orders.[Customer ID] AS [Orders_Customer ID]
, Employees.FirstName
, Orders.OrderDate
, Orders.PurchaseOrderNumber
, Orders.ShipDate
, [Shipping Methods].ShippingMethod
, Orders.Taxes
, Orders.Comment
, Orders.[Core Charge]
, Orders.[Core Charge Amount]
, [Order Details].OrderDetailID
, [Order Details].OrderID AS [Order Details_OrderID]
, [Order Details].Nomenclature
, [Order Details].[Part Number]
, [Order Details].Quantity
, [Order Details].UnitPrice
, [Order Details].[Tail #]
, [Order Details].Condition
, [Order Details].[Serial #]
, Customers.[Customer ID] AS [Customers_Customer ID]
, Customers.CompanyName
, Customers.FirstName
, Customers.LastName
, Customers.BillingAddress
, Customers.City
, Customers.StateOrProvince
, Customers.ZIPCode
, Customers.Email
, Customers.CompanyWebsite
, Customers.PhoneNumber
, Customers.FaxNumber
, Customers.ShipAddress
, Customers.ShipCity
, Customers.ShipStateOrProvince
, Customers.ShipZIPCode
, Customers.ShipPhoneNumber
, Customers.Notes
, [Order Details Extended].Subtotal
FROM ((Employees
INNER JOIN (Customers
INNER JOIN Orders
ON Customers.[Customer ID]=Orders.[Customer ID])
ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN ([Order Details]
INNER JOIN [Order Details Extended]
ON [Order Details].OrderDetailID
=[Order Details Extended].OrderDetailID)
ON Orders.OrderID=[Order Details].OrderID)
INNER JOIN [Shipping Methods]
ON Orders.ShippingMethodID
=[Shipping Methods].ShippingMethodID;


I see there is no WHERE clause limiting the records to a particular
purchase order ... are you using the Where clause of the OpenReport
action in code to do this? If so, what is the code you use to launch the
report?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Here is the SQL for the query. Thanks for any help you can give me.
Everything else on the form/report works perfectly except for the printing
and the ability to print one purchase order report for each purchase order.
Right now it is printing the first purchase order number info on the report
and the purchase order detail information from multiply purchase orders.
Thanks.

SELECT Orders.OrderID AS Orders_OrderID, Orders.[Customer ID] AS
[Orders_Customer ID], Employees.FirstName, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, [Shipping
Methods].ShippingMethod, Orders.Taxes, Orders.Comment, Orders.[Core Charge],
Orders.[Core Charge Amount], [Order Details].OrderDetailID, [Order
Details].OrderID AS [Order Details_OrderID], [Order Details].Nomenclature,
[Order Details].[Part Number], [Order Details].Quantity, [Order
Details].UnitPrice, [Order Details].[Tail #], [Order Details].Condition,
[Order Details].[Serial #], Customers.[Customer ID] AS [Customers_Customer
ID], Customers.CompanyName, Customers.FirstName, Customers.LastName,
Customers.BillingAddress, Customers.City, Customers.StateOrProvince,
Customers.ZIPCode, Customers.Email, Customers.CompanyWebsite,
Customers.PhoneNumber, Customers.FaxNumber, Customers.ShipAddress,
Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipZIPCode,
Customers.ShipPhoneNumber, Customers.Notes, [Order Details Extended].Subtotal
FROM ((Employees INNER JOIN (Customers INNER JOIN Orders ON
Customers.[Customer ID]=Orders.[Customer ID]) ON
Employees.EmployeeID=Orders.EmployeeID) INNER JOIN ([Order Details] INNER
JOIN [Order Details Extended] ON [Order Details].OrderDetailID=[Order Details
Extended].OrderDetailID) ON Orders.OrderID=[Order Details].OrderID) INNER
JOIN [Shipping Methods] ON Orders.ShippingMethodID=[Shipping
Methods].ShippingMethodID;


"strive4peace" wrote:

Hi Nancy,

can you please post the SQL for the query?
from the menu -- View, SQL

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Nancy wrote:
Thanks for the response. I erased all entries at this point as I am still in
the testing stage. I entered PO#1 with all the details. Saved the form and
checked each table. All appears to be working correctly. I then opened the
report and PO#1 shows up fine. I closed out of everything and reopened the
PO Form. Entered PO#2. Check tables and all looks good. I then opened the
Report and the only info showing now is the PO #1 info and no PO#2 Info. The
report is based on a Query called the "Orders Query". I tried opening the
report from the objects menu and from the command button I have installed on
my form and I get the same results each way. Any other ideas? Could my
query form be causing this? I ws thining about deleting the query and making
a new one as the record souce to see if that is the problem. I am still
pretty new to all this so any and all help is very much appricated.

"Carl Rapson" wrote:

Do the purchase orders display correctly in your form? If so, I'd say
there's something wrong with either the RecordSource of the report or the
way you're opening the report from the form. What is the report's
RecordSource? How are you opening the report from your form?

If not, check the logic of your form - are you sure you're not overwriting
PO #1 information with PO #2? Do both POs exist in your tables correctly?

Carl Rapson

"Nancy" wrote in message
...
My main form " Purchase Order" has a subform "Order Details Subform".

The Purchase Order table for the main form has the following fields:
OrderID-Autonumber & Primary Key; Customer ID-number, Employee ID-number,
Order Date-date, PO Number-number, etc.

The subform table has the following fields: (I do not have a primary key
for
Subform)
OrderDetail ID-Autonumber, OrderID-number, Nomenclature-text, Part
Number-number, etc.

My Master/Child link on the subform is OrderID. In the relationships page
my Order Table is linked to my Order Table with a "1" on the Order Table
"OrderID" and a "Many" on the Order Details Table "OrderID".

When I fill out my Purchase Order form and subform and hit the preview
report form for Purchase Order #1, all is fine and the report works great.
The customer name, address, etc that is on the main form comes up fine
along
with all the subform information. When I complete a new purchuse order,
ie
PO #2 and hit the print preview for the report, the report now displays
the
new customer information from Purchase Order #2 and all of the data from
the
subform on Purchase Order #1 & #2. What am I doing wrong?



.