If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Form/Report Relationship
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").IsLoade d 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? "Rockn" wrote: 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" "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? . |
#22
|
|||
|
|||
Form/Report Relationship
Thank you so much. I will work on it today and let you know what happens.
Nancy "strive4peace" wrote: 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").IsLoade d 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? "Rockn" wrote: 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" "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? . |
#23
|
|||
|
|||
Form/Report Relationship
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 * Nancy wrote: Thank you so much. I will work on it today and let you know what happens. Nancy "strive4peace" wrote: 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").IsLoade d 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? "Rockn" wrote: 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" "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? . |
#24
|
|||
|
|||
Form/Report Relationship
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" wrote: 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 * Nancy wrote: Thank you so much. I will work on it today and let you know what happens. Nancy "strive4peace" wrote: 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").IsLoade d 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? "Rockn" wrote: 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" "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? . |
#25
|
|||
|
|||
Form/Report Relationship
you're welcome, Nancy happy to help
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: 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.... |
Thread Tools | |
Display Modes | |
|
|