MS Access创建新的订单和订单行 [英] MS Access Creating a New Order And Orderline

问题描述

我有一个Customer表，一个Order表，一个Orderline表和一个Product表.它们都具有Autonumber字段作为其主键，并且Orderline具有对Order表上Order ID的外键引用:

I have a Customer table, an Order table, an Orderline table and a Product table. All of them have an Autonumber field as their primary key, and Orderline has a foreign key reference to Order ID on Order table:

ORDER
-----
Order ID - Autonumber  
Customer ID - Number  
...

ORDERLINE
---------
OrderLine ID - Autonumber  
Order ID - FK to Order  
Product ID - FK  to Product  
Quantity  

PRODUCT
-------
Product ID - Autonumber  
Product details...  

我有一个表单，可以选择一个客户，然后选择Orderline表中的记录列表，还有一个查询，我从该子窗体中引用该查询，该子窗体列出了Order ID，Orderline ID，Product ID，Product details ...

I have a form where I can choose a customer, and then a list of records from the Orderline table, and a query which I reference from this sub-form which lists the Order ID, Orderline ID, Product ID, Product details...

我有2个问题.

1. 所有订单都出现了，我只想要与此订单相关联的订单(在第一次加载表单时应该为空).

1. All the orders appear, and I only want the ones associated with this order, (which should be none when the form first loads).

当我输入要添加到新订单中的Product ID时，我希望出现一个新的Order ID((自动递增))和一个新的订单行ID , (Autoincremented) and the details of the product that I have selected, corresponding to the产品ID`输入，但我收到此错误消息:

When I enter a Product ID that I want to add to a new order, I am expecting a new Order ID to appear, (Autoincremented) AND a new Orderline ID, (Autoincremented) and the details of the product that I have selected, corresponding to theProduct ID` I have entered, but instead I get this error message:

您请求对表进行的更改未成功，因为它们会在索引，主键或关系中创建重复的值.更改一个或多个包含重复数据的字段中的数据，删除索引，或重新定义索引以允许重复的条目，然后重试

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again

问题是，当我尝试创建新记录时，表应该为我创建唯一键，而当我直接进入每个表并输入新记录时，自动编号确实起作用并创建了唯一键-这是正当它试图同时创建Order ID和Orderline ID时似乎都失败了.

The thing is, the tables should create me unique keys when I try to create the new record, and when I go into each table directly and enter a new record, the autonumber does work and does create a unique key - it is just when it is trying to create both the Order ID and the Orderline ID at the same time that it seems to be failing.

我应该说，我已经花了几天时间，搜索了无数搜索引擎，观看了有关创建订单表单的整个YouTube视频系列，但是都无济于事.我相信了解Access的任何人都可以为我提供帮助，因为如果这是SQL中的问题，那么我可以在几分钟内为处于类似情况的任何人提供帮助.

I should say, I have spent days on this, searched countless search engines, watched whole series of YouTube videos on creating Order forms but to no avail. Anyone who understands Access I am sure would be able to help me, as I would be able to help anyone in a similar circumstance in a matter of minutes if this was a problem in SQL.

推荐答案

创建子表单时，必须指定父表单和子表单之间的关系.与您为表创建的关系相同.然后只有Access会为您过滤记录.

When you create a sub-form you have to specify the relationship between the parent and sub-form. the same relationship you have created for your tables. Then only Access will filter the records for you.

关于您的问题.您应该在[订单]表中创建一个新的[订单]记录，您将在其中输入/选择[customer_id，staff_id，订单明细等]

Regarding your question. You should create a new [order] record in [order] table where you will be entering/selecting [customer_id, staff_id, order details ect]

一个订单可以有多个项目，因此您的[order_items]表(我假设订单行是您用于此表的术语)存在

one order can have more than one items so your [order_items] table (i assume orderline is the term you use for this table) exists of

• order_id
• product_id(order_id，product_id复合键)
• 数量
• 价格
• 等.

现在，当您要开始接受订单时，需要创建一个绑定到tbl_order的新表单.在frm_order中，您将有一个子源，该子源已绑定到tbl_oder_items(在您的情况下是订单行)

Now when you want to start taking order you need to create a new form that is bound to tbl_order. In the frm_order you will have a sub-from which is bound to tbl_oder_items (in your case orderline)

frm_order和frm_oder_items应该有关系.通常，当您拖动表以创建子窗体时，ACCESS会要求设置关系.如果您手动创建子表单:

The frm_order and frm_oder_items should be have a relationship. usually when you drag the table to create the subform ACCESS will ask to set the relationship. if you create the subform manually:

• 选择子表单
• 转到属性表
• 选择链接主字段:order_id
• 选择链接子字段:order_id

现在，当您打开frm_order时，它将显示tbl_order_items表中的所有记录(换言之，该订单在其列表中包含的所有产品).

Now when you open the frm_order it will show all the records (in other words all the products the order has in its list) from the tbl_order_items table.

您的tbl_order_item/orderline表还通过product_id字段引用了产品表.

Your tbl_order_item /orderline table also referencing to the product table via the product_id field.

在frm_order_items中插入一个组合框，并将其绑定到product_id.组合框的行来源将是

Insert a combobox in the frm_order_items and bound it to the product_id. The combobox's rowsource would be

select product_id, product_name from tbl_product

将出现此错误消息: '您请求对表进行的更改未成功，因为它们会在索引，主键或关系中创建重复的值.更改包含重复数据的一个或多个字段中的数据，删除索引，或重新定义索引以允许重复输入，然后重试'

当您尝试为同一订单两次添加产品时.相反，您应该增加产品的数量.

when you try to add a product twice for the same order. instead you should increase the quantity for the product.

尝试一下，让我们知道它的进展.

Try this and let us know how it went.

这篇关于MS Access创建新的订单和订单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！