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
|
|||
|
|||
duplicate record in forms
I have created a database for a web order company. I have a very simple form
with 2 pages (tabs). Each page contains 1 "sub-form" (which is actually a table). The first page contains Customer Information with a "Notes" sub-form for notes on that particular customer. The second page contains that particular customer's Order History. I am having 2 problems. When I enter the customer's initial order, I am able to enter it directly onto the form. However, when I enter subsequent orders, I am forced to enter them into the sub-form. Secondly, when I do enter more than 1 order for each customer, the form automatically creates another entry for that customer. All the information is correct, however, when I scroll through the form entries, I will have 1 entry for each order the customer has placed. However, if I go to the Customer Information table, there remains only 1 entry per customer (which obviously is what I want). Also, the sub-table (Order History) is accurate. I am convinced this is a very simple problem, however I just can not wrap my head around it to figure it out. Thank you in advance! |
#2
|
|||
|
|||
duplicate record in forms
Hi
I think (may be wrong) that this is a problem with the way you have set up the database. I think you have a customer information table that also contains so order information. If this is this case you need to re-do the table structure. Your customer information table should have "only" etails about the customer not about thier orders. So.... CustomerID Address1stLine Address2ndLine Address3rdLine Telephone etc etc etc Item table should be ItemID ItemDiscription ItemCost etc etc etc Link these 2 tables with an other table OrderID CustomerID ItemID OrderDate etc etc etc Now create a query based on these tables and then a form (use a query rather than the tables so you can do tax, deliery costs, etc calculations). Base your form on this query With custormers as the main form Order details as the 1st tab Linked to Items on the 2nd tab - this is for returns, post date, etc good luck -- Wayne Manchester, England. "Serogen" wrote: I have created a database for a web order company. I have a very simple form with 2 pages (tabs). Each page contains 1 "sub-form" (which is actually a table). The first page contains Customer Information with a "Notes" sub-form for notes on that particular customer. The second page contains that particular customer's Order History. I am having 2 problems. When I enter the customer's initial order, I am able to enter it directly onto the form. However, when I enter subsequent orders, I am forced to enter them into the sub-form. Secondly, when I do enter more than 1 order for each customer, the form automatically creates another entry for that customer. All the information is correct, however, when I scroll through the form entries, I will have 1 entry for each order the customer has placed. However, if I go to the Customer Information table, there remains only 1 entry per customer (which obviously is what I want). Also, the sub-table (Order History) is accurate. I am convinced this is a very simple problem, however I just can not wrap my head around it to figure it out. Thank you in advance! |
#3
|
|||
|
|||
duplicate record in forms
Wayne,
I thank you for your help. I have not responded sooner, because I have been trying everything I can imagine to do what you have suggested. I'm just not getting this. I do not actually have order information in my customer table. The only duplicating information I have is the CustomerID. I have this in both my Customers table and Orders table (and they are linked). However, I do believe you are correct in that there is something wrong with my database design. I discovered I did not "enforce referential integrity". Because I CAN'T!! uh-oh Also, in an attempt to implement your suggestions I have created a simple database with only 3 tables (each table containing 3 fields). I discovered part of my problem is that I had all my information on the tabs (nothing on the "main form"). However, even with this extremely simplified db I still can not get this to work. I have a Customer table with CustomerID, FirstName, and LastName Item table: ItemID, ItemDescription Order table: OrderID, CustomerID, ItemID, OrderDate I have linked the 3 tables and again, the information shows correctly in the tables. Then, I created a query based on these tables, and a form based on the query. There is obviously something I'm missing. No matter what I do, I can not figure this out! Could it be the way I'm creating the command button to create a new record? "Wayne-I-M" wrote: Hi I think (may be wrong) that this is a problem with the way you have set up the database. I think you have a customer information table that also contains so order information. If this is this case you need to re-do the table structure. Your customer information table should have "only" etails about the customer not about thier orders. So.... CustomerID Address1stLine Address2ndLine Address3rdLine Telephone etc etc etc Item table should be ItemID ItemDiscription ItemCost etc etc etc Link these 2 tables with an other table OrderID CustomerID ItemID OrderDate etc etc etc Now create a query based on these tables and then a form (use a query rather than the tables so you can do tax, deliery costs, etc calculations). Base your form on this query With custormers as the main form Order details as the 1st tab Linked to Items on the 2nd tab - this is for returns, post date, etc good luck -- Wayne Manchester, England. |
Thread Tools | |
Display Modes | |
|
|