Form/Report Relationship

S

strive4peace

Hi Nancy,

First thing I did was look at you relationship diagram.

Move Order Details to the right of Orders so data flows from left to right

enforce Referential Integrity on the relationship from Shipping Methods
to Orders

don't use lookup fields in your table design -- for instance, in Orders,
change the Display control of EmployeeID to a textbox. forms are where
you should have the combo boxes.

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

Since you are using OrderID to link tour subform to your mainform, you
need to put it ON your subform... you can set Visible = No so it does
not show

~~

In the code behind the Purchase Order Report, you have an error

change
If Not IsLoaded("Purchase Orders") Then
to
If Not CurrentProject.AllForms("Purchase Orders").IsLoaded Then
or, if you rename the form -->
If Not CurrentProject.AllForms("F_PurchaseOrder").IsLoaded Then

anyway, until you get the report working right, comment the lines for
the Report_Open event ... put a single quote ' at the beginning of each
statement so it turns green

you are getting the error about
[Purchase Orders].PurchaseOrderID
because that is how you have specified to group the report --

1. open the Sorting and Grouping window
2. click where [Purchase Orders].PurchaseOrderID is specified
3. drop the combobox and change the group field to --> Orders_OrderID

on F_PurchaseOrder

the reason your Preview Report button isn't doing anything is because
you don't have it running any code.

1. from the design view of the form, turn on the Properties
2. click on the Preview Report command button
3. click on the Event tab of the Properties window
4. click in the property labeled --> On click
5. from the combobox, choose --> [Event Procedure]
6. click the builder button [...] to the right and insert this code
between the procedure declaration Access provides for you and 'End Sub'

'~~~~~~~~~~~~~~~
Dim strWhere As String

strWhere = "Orders_OrderID=" & Me.OrderID

On Error Resume Next
'use this statement if you want the report to print
'DoCmd.OpenReport "Purchase Order", acViewNormal, , strWhere

'use this statement to preview the report
DoCmd.OpenReport "Purchase Order", acViewPreview, , strWhere
'~~~~~~~~~~~~~~~

put Orders_OrderID ON the report! It can have visible = No if you don't
want it to show.

remove the captions on OrderID, OrderDate, etc in your table design.
you should not use Captions in the table design -- when a table is
opened directly, it should be done by a developer and the real
fieldnames should show.


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 :)
*
 
N

Nancy

Thank you so much. I will work on it today and let you know what happens.

Nancy

strive4peace said:
Hi Nancy,

First thing I did was look at you relationship diagram.

Move Order Details to the right of Orders so data flows from left to right

enforce Referential Integrity on the relationship from Shipping Methods
to Orders

don't use lookup fields in your table design -- for instance, in Orders,
change the Display control of EmployeeID to a textbox. forms are where
you should have the combo boxes.

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

Since you are using OrderID to link tour subform to your mainform, you
need to put it ON your subform... you can set Visible = No so it does
not show

~~

In the code behind the Purchase Order Report, you have an error

change
If Not IsLoaded("Purchase Orders") Then
to
If Not CurrentProject.AllForms("Purchase Orders").IsLoaded Then
or, if you rename the form -->
If Not CurrentProject.AllForms("F_PurchaseOrder").IsLoaded Then

anyway, until you get the report working right, comment the lines for
the Report_Open event ... put a single quote ' at the beginning of each
statement so it turns green

you are getting the error about
[Purchase Orders].PurchaseOrderID
because that is how you have specified to group the report --

1. open the Sorting and Grouping window
2. click where [Purchase Orders].PurchaseOrderID is specified
3. drop the combobox and change the group field to --> Orders_OrderID

on F_PurchaseOrder

the reason your Preview Report button isn't doing anything is because
you don't have it running any code.

1. from the design view of the form, turn on the Properties
2. click on the Preview Report command button
3. click on the Event tab of the Properties window
4. click in the property labeled --> On click
5. from the combobox, choose --> [Event Procedure]
6. click the builder button [...] to the right and insert this code
between the procedure declaration Access provides for you and 'End Sub'

'~~~~~~~~~~~~~~~
Dim strWhere As String

strWhere = "Orders_OrderID=" & Me.OrderID

On Error Resume Next
'use this statement if you want the report to print
'DoCmd.OpenReport "Purchase Order", acViewNormal, , strWhere

'use this statement to preview the report
DoCmd.OpenReport "Purchase Order", acViewPreview, , strWhere
'~~~~~~~~~~~~~~~

put Orders_OrderID ON the report! It can have visible = No if you don't
want it to show.

remove the captions on OrderID, OrderDate, etc in your table design.
you should not use Captions in the table design -- when a table is
opened directly, it should be done by a developer and the real
fieldnames should show.


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 :)
*


I must still be doing something wrong. Anymore suggestions? Could this be a
relationship problem? Based on the table structure listed above do you have
any recommendations about how best to link the data from the main form to the
subform?
Click to expand...
Click to expand...
 
S

strive4peace

Hi Nancy,

you're welcome ;)


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 :)
*


Thank you so much. I will work on it today and let you know what happens.

Nancy

strive4peace said:
Hi Nancy,

First thing I did was look at you relationship diagram.

Move Order Details to the right of Orders so data flows from left to right

enforce Referential Integrity on the relationship from Shipping Methods
to Orders

don't use lookup fields in your table design -- for instance, in Orders,
change the Display control of EmployeeID to a textbox. forms are where
you should have the combo boxes.

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

Since you are using OrderID to link tour subform to your mainform, you
need to put it ON your subform... you can set Visible = No so it does
not show

~~

In the code behind the Purchase Order Report, you have an error

change
If Not IsLoaded("Purchase Orders") Then
to
If Not CurrentProject.AllForms("Purchase Orders").IsLoaded Then
or, if you rename the form -->
If Not CurrentProject.AllForms("F_PurchaseOrder").IsLoaded Then

anyway, until you get the report working right, comment the lines for
the Report_Open event ... put a single quote ' at the beginning of each
statement so it turns green

you are getting the error about
[Purchase Orders].PurchaseOrderID
because that is how you have specified to group the report --

1. open the Sorting and Grouping window
2. click where [Purchase Orders].PurchaseOrderID is specified
3. drop the combobox and change the group field to --> Orders_OrderID

on F_PurchaseOrder

the reason your Preview Report button isn't doing anything is because
you don't have it running any code.

1. from the design view of the form, turn on the Properties
2. click on the Preview Report command button
3. click on the Event tab of the Properties window
4. click in the property labeled --> On click
5. from the combobox, choose --> [Event Procedure]
6. click the builder button [...] to the right and insert this code
between the procedure declaration Access provides for you and 'End Sub'

'~~~~~~~~~~~~~~~
Dim strWhere As String

strWhere = "Orders_OrderID=" & Me.OrderID

On Error Resume Next
'use this statement if you want the report to print
'DoCmd.OpenReport "Purchase Order", acViewNormal, , strWhere

'use this statement to preview the report
DoCmd.OpenReport "Purchase Order", acViewPreview, , strWhere
'~~~~~~~~~~~~~~~

put Orders_OrderID ON the report! It can have visible = No if you don't
want it to show.

remove the captions on OrderID, OrderDate, etc in your table design.
you should not use Captions in the table design -- when a table is
opened directly, it should be done by a developer and the real
fieldnames should show.


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 :)
*


I must still be doing something wrong. Anymore suggestions? Could this be a
relationship problem? Based on the table structure listed above do you have
any recommendations about how best to link the data from the main form to the
subform?

:

You need some way to link the data in the first sub-form to the purchase
order you are working on. I think you answered your own question with "I do
not have a primary key for Subform"

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?



.
Click to expand...
Click to expand...
Click to expand...
 
N

Nancy

Hi there. I made all the changes you suggested. I went step by step and
saved each time a made a change and then checked to make sure everythingwas
working. WOW! Thank you so much. The only thing I could not get to work
was the preview report button. I did as you said and it just does not seem
to work for me. I then deleted the button and used the command button
wizard and it seems to work fine. As long as I first save the purchase order
form and then hit the preview button it works great. I can live with this.
Thank you oh so much....

strive4peace said:
Hi Nancy,

you're welcome ;)


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 :)
*


Thank you so much. I will work on it today and let you know what happens.

Nancy

strive4peace said:
Hi Nancy,

First thing I did was look at you relationship diagram.

Move Order Details to the right of Orders so data flows from left to right

enforce Referential Integrity on the relationship from Shipping Methods
to Orders

don't use lookup fields in your table design -- for instance, in Orders,
change the Display control of EmployeeID to a textbox. forms are where
you should have the combo boxes.

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

Since you are using OrderID to link tour subform to your mainform, you
need to put it ON your subform... you can set Visible = No so it does
not show

~~

In the code behind the Purchase Order Report, you have an error

change
If Not IsLoaded("Purchase Orders") Then
to
If Not CurrentProject.AllForms("Purchase Orders").IsLoaded Then
or, if you rename the form -->
If Not CurrentProject.AllForms("F_PurchaseOrder").IsLoaded Then

anyway, until you get the report working right, comment the lines for
the Report_Open event ... put a single quote ' at the beginning of each
statement so it turns green

you are getting the error about
[Purchase Orders].PurchaseOrderID
because that is how you have specified to group the report --

1. open the Sorting and Grouping window
2. click where [Purchase Orders].PurchaseOrderID is specified
3. drop the combobox and change the group field to --> Orders_OrderID

on F_PurchaseOrder

the reason your Preview Report button isn't doing anything is because
you don't have it running any code.

1. from the design view of the form, turn on the Properties
2. click on the Preview Report command button
3. click on the Event tab of the Properties window
4. click in the property labeled --> On click
5. from the combobox, choose --> [Event Procedure]
6. click the builder button [...] to the right and insert this code
between the procedure declaration Access provides for you and 'End Sub'

'~~~~~~~~~~~~~~~
Dim strWhere As String

strWhere = "Orders_OrderID=" & Me.OrderID

On Error Resume Next
'use this statement if you want the report to print
'DoCmd.OpenReport "Purchase Order", acViewNormal, , strWhere

'use this statement to preview the report
DoCmd.OpenReport "Purchase Order", acViewPreview, , strWhere
'~~~~~~~~~~~~~~~

put Orders_OrderID ON the report! It can have visible = No if you don't
want it to show.

remove the captions on OrderID, OrderDate, etc in your table design.
you should not use Captions in the table design -- when a table is
opened directly, it should be done by a developer and the real
fieldnames should show.


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:
I must still be doing something wrong. Anymore suggestions? Could this be a
relationship problem? Based on the table structure listed above do you have
any recommendations about how best to link the data from the main form to the
subform?

:

You need some way to link the data in the first sub-form to the purchase
order you are working on. I think you answered your own question with "I do
not have a primary key for Subform"

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?



.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form Problem 1
Form Question 5
Filter combo box in subform based on field in main form 1
Link for sub form on form to sub form in same form 2
Filter 2
copy a record and add a revision number 5
Click a subform field to open relevant form 5
Form Error Msg 1

Top