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 |
#1
|
|||
|
|||
data not flowing properly
My data is not populating related tables. For instance, the sales order
number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#2
|
|||
|
|||
data not flowing properly
What are you doing that makes you think the Order Number should be going to
the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#3
|
|||
|
|||
data not flowing properly
Northwind? I don't know what you mean.
The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#4
|
|||
|
|||
data not flowing properly
Northwind.mdb is a sample Access application that generally gets installed
when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#5
|
|||
|
|||
data not flowing properly
Ok....let me see if I've got this. If I have seven tables then I have to set
up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#6
|
|||
|
|||
data not flowing properly
Whoa! I am way to slow to comprehend too much information at one time. I
mentioned a main form and subform with the Link Master and Child properties and you come back with a full schema, a 4-column list box, and a whole lot more. You didn't provide the very basics of what I thought I had suggested. You should search the samples in the Office installation directory in your Program Files to see if you have the Northwind.mdb. This sample has much of the information set up the way you need. If you can't find the Northwind, set up a single view form based on the Orders table. Create a continuous subform based on the Order Details. Add the subform to the Orders form and set the Link Master and Link Child properties to [Sales Order Number]. BTW: You should avoid using spaces and symbols in field and other object names ie: -%... Also controls such as combo boxes and list boxes can be bound to a single field, not multiple fields. If you are attempting to store values from more than one column of these controls, it generally suggests an un-normalized table structure. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Ok....let me see if I've got this. If I have seven tables then I have to set up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#7
|
|||
|
|||
data not flowing properly
Sorry. I didn't mean to confuse the issue. I had already set up forms and
was trying to keep them w/o making too many changes. I had a meeting with the conversion team earlier this week and presented those forms so I was hoping to keep what I had but we're all learning here. I'll look to see if we have the Northwind. It looks, though, like I will have to start from scratch and set up several forms instead of two. We have a massive amount of data to input as the excel spreadsheet file is corrupted. They're not going to like having to open several forms to enter data from just one line in excel...access is hardly simple. "Duane Hookom" wrote: Whoa! I am way to slow to comprehend too much information at one time. I mentioned a main form and subform with the Link Master and Child properties and you come back with a full schema, a 4-column list box, and a whole lot more. You didn't provide the very basics of what I thought I had suggested. You should search the samples in the Office installation directory in your Program Files to see if you have the Northwind.mdb. This sample has much of the information set up the way you need. If you can't find the Northwind, set up a single view form based on the Orders table. Create a continuous subform based on the Order Details. Add the subform to the Orders form and set the Link Master and Link Child properties to [Sales Order Number]. BTW: You should avoid using spaces and symbols in field and other object names ie: -%... Also controls such as combo boxes and list boxes can be bound to a single field, not multiple fields. If you are attempting to store values from more than one column of these controls, it generally suggests an un-normalized table structure. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Ok....let me see if I've got this. If I have seven tables then I have to set up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#8
|
|||
|
|||
data not flowing properly
You can generally use forms with multiple subforms. Access isn't a
spreadsheet and allows much greater flexibility and functionality for storing and reporting related data. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Sorry. I didn't mean to confuse the issue. I had already set up forms and was trying to keep them w/o making too many changes. I had a meeting with the conversion team earlier this week and presented those forms so I was hoping to keep what I had but we're all learning here. I'll look to see if we have the Northwind. It looks, though, like I will have to start from scratch and set up several forms instead of two. We have a massive amount of data to input as the excel spreadsheet file is corrupted. They're not going to like having to open several forms to enter data from just one line in excel...access is hardly simple. "Duane Hookom" wrote: Whoa! I am way to slow to comprehend too much information at one time. I mentioned a main form and subform with the Link Master and Child properties and you come back with a full schema, a 4-column list box, and a whole lot more. You didn't provide the very basics of what I thought I had suggested. You should search the samples in the Office installation directory in your Program Files to see if you have the Northwind.mdb. This sample has much of the information set up the way you need. If you can't find the Northwind, set up a single view form based on the Orders table. Create a continuous subform based on the Order Details. Add the subform to the Orders form and set the Link Master and Link Child properties to [Sales Order Number]. BTW: You should avoid using spaces and symbols in field and other object names ie: -%... Also controls such as combo boxes and list boxes can be bound to a single field, not multiple fields. If you are attempting to store values from more than one column of these controls, it generally suggests an un-normalized table structure. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Ok....let me see if I've got this. If I have seven tables then I have to set up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#9
|
|||
|
|||
data not flowing properly
I think I screwed up....I have input the Customer list which has populated
the Customer table. When I go to enter the customer information for an order, I get an error message that it can't be accepted because it would create a duplicate. The Customer ID is a primary key and I can't change it to allow for duplicates. So how do I enter customer information for an order???? "Duane Hookom" wrote: You can generally use forms with multiple subforms. Access isn't a spreadsheet and allows much greater flexibility and functionality for storing and reporting related data. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Sorry. I didn't mean to confuse the issue. I had already set up forms and was trying to keep them w/o making too many changes. I had a meeting with the conversion team earlier this week and presented those forms so I was hoping to keep what I had but we're all learning here. I'll look to see if we have the Northwind. It looks, though, like I will have to start from scratch and set up several forms instead of two. We have a massive amount of data to input as the excel spreadsheet file is corrupted. They're not going to like having to open several forms to enter data from just one line in excel...access is hardly simple. "Duane Hookom" wrote: Whoa! I am way to slow to comprehend too much information at one time. I mentioned a main form and subform with the Link Master and Child properties and you come back with a full schema, a 4-column list box, and a whole lot more. You didn't provide the very basics of what I thought I had suggested. You should search the samples in the Office installation directory in your Program Files to see if you have the Northwind.mdb. This sample has much of the information set up the way you need. If you can't find the Northwind, set up a single view form based on the Orders table. Create a continuous subform based on the Order Details. Add the subform to the Orders form and set the Link Master and Link Child properties to [Sales Order Number]. BTW: You should avoid using spaces and symbols in field and other object names ie: -%... Also controls such as combo boxes and list boxes can be bound to a single field, not multiple fields. If you are attempting to store values from more than one column of these controls, it generally suggests an un-normalized table structure. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Ok....let me see if I've got this. If I have seven tables then I have to set up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
#10
|
|||
|
|||
data not flowing properly
Can a form and a subform be linked by more than one control? For instance, in
my Order table there are two controls - sales order number and skids/parts number and the same two are part of the Order Detail subform. The two are related via the sales order number but I also need the skids/parts number control in the subform to be populated from the order table entry. Is that possible? "Duane Hookom" wrote: You can generally use forms with multiple subforms. Access isn't a spreadsheet and allows much greater flexibility and functionality for storing and reporting related data. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Sorry. I didn't mean to confuse the issue. I had already set up forms and was trying to keep them w/o making too many changes. I had a meeting with the conversion team earlier this week and presented those forms so I was hoping to keep what I had but we're all learning here. I'll look to see if we have the Northwind. It looks, though, like I will have to start from scratch and set up several forms instead of two. We have a massive amount of data to input as the excel spreadsheet file is corrupted. They're not going to like having to open several forms to enter data from just one line in excel...access is hardly simple. "Duane Hookom" wrote: Whoa! I am way to slow to comprehend too much information at one time. I mentioned a main form and subform with the Link Master and Child properties and you come back with a full schema, a 4-column list box, and a whole lot more. You didn't provide the very basics of what I thought I had suggested. You should search the samples in the Office installation directory in your Program Files to see if you have the Northwind.mdb. This sample has much of the information set up the way you need. If you can't find the Northwind, set up a single view form based on the Orders table. Create a continuous subform based on the Order Details. Add the subform to the Orders form and set the Link Master and Link Child properties to [Sales Order Number]. BTW: You should avoid using spaces and symbols in field and other object names ie: -%... Also controls such as combo boxes and list boxes can be bound to a single field, not multiple fields. If you are attempting to store values from more than one column of these controls, it generally suggests an un-normalized table structure. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Ok....let me see if I've got this. If I have seven tables then I have to set up an input form for each table? I have seven tables: Customer - Orders - Shipping - Shipping Detail / l \ Order Detail l Monthly Open Orders Profit I know this is a lot of information but I'm dealing with some seriously short time constraints. This is the field list description for each table: Customer: Customer ID, Customer Name, Customer Name Detail, Customer Unit Orders: Customer ID, Sales Order Number, Skids/Parts Number, Credit Number, Specialist Code Order Detail: Sales Order Number, Skids/Parts Number, Skid Quantity, Parts Quantity, Quote Number, Subsequent Quote Number Monthly Open Orders: Specialist Code, Customer ID, Customer Name, Customer Name Detail, Credit Number, Skid Quantity, Parts Quantity, Skid/Parts Number, Order Date, Sales Order Number, Promise Date, Explanation Profit: Sales Order Number, Cost,Sales Price, Cost %, % Profit Shipping: Invoice Number, Sales Order Number, Ship Date, Final Destination Shipping Detail: Invoice Number, Sales Order Number, Order Date, Promised Date, Shipped Date, Shipment:Partial or Complete? I set up Order Input Form Part I and Part II. Part I has text boxes for the information from the customer table, a 4-column list box that I thought would populate the customer table controls but it won't, a subform for the orders table and another subform for the orders detail. The orders detail though did not populate completely. The Sales Order Number and the Skids/Parts Number didn't flow over from the Orders Table. Is that what you're talking about with regard to link master/child fields? Part II contains controls for Sales Order Number, Invoice Number, Order Date, Promised Date, Shipped Date, Partial or Complete?, Final Destination, Cost and Sales Price. Do I need to completely revamp the input forms? Should I add the fields from the Profit table to the Order Detail table and delete the Profit table? I was trying to get all the input on two forms but in reality should I have three - customer input form, order input and lastly shipping input? The Monthly Open Orders table may seem redundant but the warehouse can look at this information in datasheet view until I put together the reports. How do I make sure that this table is populated w/o having to enter data twice? I have a conversion team waiting on me to finish the data input testing so they can begin entering the actual data. Can you give me some feedback on this information and my questions? "Duane Hookom" wrote: Northwind.mdb is a sample Access application that generally gets installed when you install Office/Access. In order to populate the detail records with a value from the Orders table you should create a main form based on the Orders table and a subform based on the Order Details table. Set the Link Master/Child properties of the subform control to the link fields. -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... Northwind? I don't know what you mean. The Order and Order Details table are related with the Sales Order Number being the primary and foreign keys respectively. It is my understanding that data flows "downstream" so to speak thru the tables via the relationships. Is that not right? and if not, what do I do to make that happen? otherwise you'd be repeatedly entering the same data. "Duane Hookom" wrote: What are you doing that makes you think the Order Number should be going to the Order Details table? Are you using a form with a subform like the Northwind. mdb? -- Duane Hookom MS Access MVP "TechyTemp" wrote in message ... My data is not populating related tables. For instance, the sales order number in the Orders table is not populating the sales order field in the Order Detail table. The order sequence of my tables is Customer - Orders - Shipping - Shipping Detail with trunks off of the Orders table going to Order Detail and Profit. The sales order number is not going past the Orders table. I am also getting Error 3101 (?) messages when I try to input data via my forms. Apparently I am entering data into the many side rather than the one side? What am I doing wrong? |
|
Thread Tools | |
Display Modes | |
|
|