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 |
#11
|
|||
|
|||
Form/Report Relationship
Thank you so much for your response. I don't want to sound stupid, but where
exactly do I put the "Where Clause"? I assume in the SQL, but where? Beginning, end, etc. Never did this most of this stuff before so I am learning as I go along. Also, I expect to have just one purchase order number for each transaction. I might have three transactions with the same customer in one day, but each would be a seperate purchase order number. And I might have several transactions during the day with several customers but each would be a seperate purchase order number. Having said that, do I need the customer ID included? And, is the Master/Child link correct with the orderID? Or should it be Purchase Order as the Master/Child? Thank youuuuuuu...... "strive4peace" wrote: 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 |
#12
|
|||
|
|||
Form/Report Relationship
Hi Nancy,
I am assuming that you have a command button to launch the report ... this code would go behind the form as an [Event Procedure] for the Click event of the command button In this case, your query does not need a WHERE clause since strWhere is built to filter the data of the OpenReport action the Purchase Order Number ... actually, looking at your structure again, I see that OrderID can be used instead of CustomerID and Purchase Order Number, so the code for the [Event Procedure] of your command button Click would be: '~~~~~~~~~~~~~~~ dim strWhere as string strWhere = "OrderID=" & me.OrderID docmd.OpenReport "Invoice", acViewNormal, ,strWhere '~~~~~~~~~~~~~~~ this simplifies it smile the forms do not contain your data. TABLES are what holds the data. Forms are based on tables and can be used to display, edit and add data that is stored in tables. Whatever table the subform is based on has OrderID as a foreign key, so all the detail lines will be included 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 response. I don't want to sound stupid, but where exactly do I put the "Where Clause"? I assume in the SQL, but where? Beginning, end, etc. Never did this most of this stuff before so I am learning as I go along. Also, I expect to have just one purchase order number for each transaction. I might have three transactions with the same customer in one day, but each would be a seperate purchase order number. And I might have several transactions during the day with several customers but each would be a seperate purchase order number. Having said that, do I need the customer ID included? And, is the Master/Child link correct with the orderID? Or should it be Purchase Order as the Master/Child? Thank youuuuuuu...... "strive4peace" wrote: 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 |
#13
|
|||
|
|||
Form/Report Relationship
Thanks for the Reply. I must be doing something wrong because I cannot get
it to work. I went to design view of my form, highlighted the Preview Report command button to the properties page. On the click event I went to the code page and copy and pasted your response. I changed the name of my report to “Invoice” so it would match your response. I placed the code between, “Private Sub and end sub statements”. Closed the page and made sure everything was saved. I then opened the PO form and hit the Preview report button and nothing at all would happen. I fiddled around for awhile and can’t seem to make the button work at all. What am I doing wrong now? Please help me. Thank you so much. "strive4peace" wrote: Hi Nancy, I am assuming that you have a command button to launch the report ... this code would go behind the form as an [Event Procedure] for the Click event of the command button In this case, your query does not need a WHERE clause since strWhere is built to filter the data of the OpenReport action the Purchase Order Number ... actually, looking at your structure again, I see that OrderID can be used instead of CustomerID and Purchase Order Number, so the code for the [Event Procedure] of your command button Click would be: '~~~~~~~~~~~~~~~ dim strWhere as string strWhere = "OrderID=" & me.OrderID docmd.OpenReport "Invoice", acViewNormal, ,strWhere '~~~~~~~~~~~~~~~ this simplifies it smile the forms do not contain your data. TABLES are what holds the data. Forms are based on tables and can be used to display, edit and add data that is stored in tables. Whatever table the subform is based on has OrderID as a foreign key, so all the detail lines will be included 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 response. I don't want to sound stupid, but where exactly do I put the "Where Clause"? I assume in the SQL, but where? Beginning, end, etc. Never did this most of this stuff before so I am learning as I go along. Also, I expect to have just one purchase order number for each transaction. I might have three transactions with the same customer in one day, but each would be a seperate purchase order number. And I might have several transactions during the day with several customers but each would be a seperate purchase order number. Having said that, do I need the customer ID included? And, is the Master/Child link correct with the orderID? Or should it be Purchase Order as the Master/Child? Thank youuuuuuu...... "strive4peace" wrote: 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) |
#14
|
|||
|
|||
Form/Report Relationship
Hi Nancy,
'~~~~~~~~~ Compile ~~~~~~~~~ Whenever you change code or references, your should always compile before executing. from the menu in a VBE (module) window: Debug, Compile fix any errors on the yellow highlighted lines keep compiling until nothing happens (this is good!) 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 Reply. I must be doing something wrong because I cannot get it to work. I went to design view of my form, highlighted the Preview Report command button to the properties page. On the click event I went to the code page and copy and pasted your response. I changed the name of my report to “Invoice” so it would match your response. I placed the code between, “Private Sub and end sub statements”. Closed the page and made sure everything was saved. I then opened the PO form and hit the Preview report button and nothing at all would happen. I fiddled around for awhile and can’t seem to make the button work at all. What am I doing wrong now? Please help me. Thank you so much. "strive4peace" wrote: Hi Nancy, I am assuming that you have a command button to launch the report ... this code would go behind the form as an [Event Procedure] for the Click event of the command button In this case, your query does not need a WHERE clause since strWhere is built to filter the data of the OpenReport action the Purchase Order Number ... actually, looking at your structure again, I see that OrderID can be used instead of CustomerID and Purchase Order Number, so the code for the [Event Procedure] of your command button Click would be: '~~~~~~~~~~~~~~~ dim strWhere as string strWhere = "OrderID=" & me.OrderID docmd.OpenReport "Invoice", acViewNormal, ,strWhere '~~~~~~~~~~~~~~~ this simplifies it smile the forms do not contain your data. TABLES are what holds the data. Forms are based on tables and can be used to display, edit and add data that is stored in tables. Whatever table the subform is based on has OrderID as a foreign key, so all the detail lines will be included 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 response. I don't want to sound stupid, but where exactly do I put the "Where Clause"? I assume in the SQL, but where? Beginning, end, etc. Never did this most of this stuff before so I am learning as I go along. Also, I expect to have just one purchase order number for each transaction. I might have three transactions with the same customer in one day, but each would be a seperate purchase order number. And I might have several transactions during the day with several customers but each would be a seperate purchase order number. Having said that, do I need the customer ID included? And, is the Master/Child link correct with the orderID? Or should it be Purchase Order as the Master/Child? Thank youuuuuuu...... "strive4peace" wrote: 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) |
#15
|
|||
|
|||
Form/Report Relationship
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? . |
#16
|
|||
|
|||
Form/Report Relationship
I tired the compile and debug and nothing seems to work. After the last
suggestion, I went back and tried changing the Master/Child Link. I tried each suggestion that was offered and they all produced the same results. When I hit the preview report it shows all purchase order detail items under one purchase order number. Any other ideas, Please? "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? . |
#17
|
|||
|
|||
Form/Report Relationship
Any suggestions on what the primary key/foreign key should be for based on
the tables listed above. Also, should this be a relationship probleM? Any suggestions on how they should be linked in relationships? "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? . |
#18
|
|||
|
|||
Form/Report Relationship
I must be doing something wrong. I still cannot get this to work right. Do
you have any suggestions as to the primary key based on the table structure listed above. Could this be a relatiionship issue? Thanks for any help at all "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? . |
#19
|
|||
|
|||
Form/Report Relationship
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? . |
#20
|
|||
|
|||
Form/Report Relationship
Hi Nancy,
email me your database and specify the name of the form. 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? . |
Thread Tools | |
Display Modes | |
|
|