Solved Not able to edit order form

[hrdpgajjar]

Hi all,
I have created a customer order entry system, I have tried to follow an online tutorial and make the attached access order entry system.

Now I need help on,

1. How to create a new customer and enter order items
2. how to edit items in OrderF (it shows an error)



Thanks

 

[DHookom]

You should consider downloading the new Northwinds Access template. There are two versions. You should probably consider the Starter edition

 

[Pat Hartman]

Do NOT name your autonumbers "ID". it just causes confusion and makes it hard for others to see how the tables relate. Name this field CustomerID and USE it as the PK or do not include an autonumber at all.

Order and Order details must be related on OrderID. REMOVE RegNo from OrderDetails and replace with OrderID. Then fix the master/child links.

 

[mike60smart]

Hi all,
I have created a customer order entry system, I have tried to follow an online tutorial and make the attached access order entry system.

Now I need help on,

1. How to create a new customer and enter order items
2. how to edit items in OrderF (it shows an error)



Thanks

Hi
Your CustomerT table has a field called "ID" and is an Autonumerber but you have not set this as the PK?
The "ID" field should be renamed to CustomerID

You should then add a new field named "CustomerID" to the table "OrderT" and this should be a Number data Type - Long Integer.

You should then link the CustomerID Pk from CustomerT to the CustomerID Fk in the "OrderT" and Enforce Referential Integrity.

You can still keep your field named "RegiNo".

Also you should remove all spaces in field names.

You do not need the field "RegiNo" in both the tables "OrderT" & "OrderdetailT".

You do not need the field named "Productname" in "OrderDetailT".

 

[CJ_London]

should be renamed to CustomerID

Better to call it customerPK

You should then add a new field named "CustomerID" to the table "OrderT" and this should be a Number data Type - Long Integer

Better to call it customerFK

 

[Pat Hartman]

Although I would NOT use RegNo as the PK, I would use an autonumber named CustomerID, there is no logical problem with using RegNo as the PK for Order. The problem lies between Order and OrderDetails. OrderDetails MUST relate to Order on OrderID, NOT on RegNo. As you can see, the same RegNo can place MULTIPLE orders but a single OrderDetail CANNOT point to multiple Orders. This is the logic problem the OP is experiencing.

Naming FK's by replacing ID with FK or suffixing FK is OK but not necessary. I always use the same name because both fields are never selected in the same query. When your query joins two tables, you ALWAYS take the FK value and never take the PK value.

 

[CJ_London]

Naming FK's by replacing ID with FK or suffixing FK is OK but not necessary

Agree not necessary (as indeed using relevant names is not necessary) but these days, most of my queries I write directly in sql so using PK/FK obviates the need to specify the table name (unless there are multiple child tables) so save typing. Further it tells me which end of a join each field is - not so important for inner joins but much more relevant for left/right joins, subqueries and aliased queries.

At the end of the day, particularly within an access environment, it comes down to personal style since usually the only person reading and needing to understanding it is the author. So long as they can make sense of it months down the line, it doesn't really matter

 

[June7]

And if there are multiple child tables, can still customize field names so they are unique.

 

[DHookom]

Lots of great best practices in the replies in this thread. AGAIN, simply download the Northwind template to see these put into a working file that probably meets your requirements. There is also a fair amount of documentation available on-line.

 

[LarryE]

I normalized your tables for you again just like the last time you asked the same questions. Here is a file more like what you are trying to do. Note how the tables are related with PK's and FK's. The Order table is a junction table that brings the customer and product(s) they order together. You can re-do the forms. Please review the relationships window so you can see how a relational system works. I also attach the last file I fixed for you. I would probably combine the two systems into one, but that is up to you.

 

[Pat Hartman]

Queries should only include necessary fields and the PK from the parent table is redundant and so only adds confusion when you include it. The FK is the relevant field and the only one which can be changed or added.

 

[hrdpgajjar]

I normalized your tables for you again just like the last time you asked the same questions. Here is a file more like what you are trying to do. Note how the tables are related with PK's and FK's. The Order table is a junction table that brings the customer and product(s) they order together. You can re-do the forms. Please review the relationships window so you can see how a relational system works. I also attach the last file I fixed for you. I would probably combine the two systems into one, but that is up to you.

Thanks Larry you are a life saver.