vba 如何使用表单上的复选框将记录添加到 Microsoft Office Access 的子表单中？

How do I use a Checkbox on a form to add a record into a subform in Microsoft Office Access?

提问by oli.burgess

I have a database for a carpet company. I have a form which enables the user to make a quote, including choosing the customer, etc. There is a also subform dealing with the products and extras, with quantities and unit prices. To state whether the user requires fitting there is a checkbox. When ticked I would like fitting to be added in the subform as one of the products. Products are normally manually chosen using a dropdown.

我有一个地毯公司的数据库。我有一个表单，使用户能够进行报价，包括选择客户等。还有一个子表单处理产品和附加产品，包括数量和单价。为了说明用户是否需要拟合，有一个复选框。勾选后，我希望将配件作为产品之一添加到子表单中。通常使用下拉菜单手动选择产品。

Important properties are:
Form name: Orders
Subform name: Order Details Subform
Checkbox name: Fitting
Field name for products: Product ID
Subform linked to table: Order Details
Form linked to table: Orders

重要属性有：
表单名称：订单
子表单名称：订单详细信息子表单
复选框名称：产品的匹配
字段名称：产品 ID
链接到表格的子表格：链接到表格的订单详细信息
表格：订单

I'm assuming VBA is needed, or the macro builder.

我假设需要 VBA 或宏构建器。

Anyway thanks in advance!!

无论如何，提前致谢！！

采纳答案by BIBD

On the check box control, you are going to add an [Event Procedure] on the After Update event for the checkbox (see the properties window). This will stub out the VBA code, click on the ellipsis ("...") to get to the VBA code.

在复选框控件上，您将在复选框的更新后事件上添加一个 [事件过程]（请参阅属性窗口）。这将删除 VBA 代码，单击省略号（“...”）以访问 VBA 代码。

You can use Remou's code (after a fashion) to insert the new Product:

您可以使用 Remou 的代码（在时尚之后）插入新产品：

If Me.Fitting Then
    strSQL="INSERT INTO [Order Details] (ProductID,OtherTextField) " & _
        "Values (" & _
        Me.ProductID & ",'" & Me.OtherTextField & "')"
    CurrentDB.Execute strSQL, dbFailOnError

    Me.[Subform control name here].Form.Requery
End If
'[code not tested]

However, you probably also want to check that the Product doesn't already exist in for the Order (does your business rules allow for multiple fittings to be scheduled), and you might also want to allow the Fitting product to be removed when it is unchecked - you could do that with an else condition on the if before the "end if":

但是，您可能还想检查订单中是否不存在该产品（您的业务规则是否允许安排多个配件），并且您可能还希望允许在该产品出现时将其移除未选中 - 您可以在“结束 if”之前的 if 上使用 else 条件来做到这一点：

if 'blah
     'blah blah
else
    strSQL="Delete [Order Details] " & _
        "where ProductID = " & Me.ProductID & " " & _
            "and OtherTextField = 'fitting' " 
    CurrentDB.Execute strSQL, dbFailOnError

    Me.[Subform control name here].Form.Requery
end if
'[code not tested]

You will also have to hack your Products sub form so that if you delete the Fitting product from it, you update the check box (or so that you can't delete Fittings that way).

您还必须修改您的产品子表单，以便如果您从中删除配件产品，您更新复选框（或这样您就不能以这种方式删除配件）。

Then again maybe you don't want to use a check box, and just have the fitting as being another drop down option, as the other products are. You could always make sure it's auto-populated into [Order Details] whenever a new Order is created so it can't be forgotten. This would be more consistent with the rest of the product selection user interface.

再说一次，也许您不想使用复选框，而只是将配件作为另一个下拉选项，就像其他产品一样。每当创建新订单时，您始终可以确保它自动填充到 [订单详细信息] 中，以免被遗忘。这将与产品选择用户界面的其余部分更加一致。

回答by Fionnuala

I think the easiest way is to use an append query.

我认为最简单的方法是使用附加查询。

If Me.Fitting Then
   strSQL="INSERT INTO [Order Details] (ProductID,OtherTextField) Values ("
         & Me.ProductID & ",'" & Me.OtherTextField & "')"
   CurrentDB.Execute strSQL, dbFailOnError

   Me.[Subform control name here].Form.Requery
End If

回答by JeffO

This is the no code solution:

这是无代码解决方案：

Get rid of the Fitting check box. Make fitting a product and add it like all the rest. If you want to know if an order requires a fitting (I'm guessing that's why you have the checkbox.), you can create a query to see if fitting is one of the products on your reports/invoices.

去掉拟合复选框。制作适合的产品并像其他所有产品一样添加它。如果您想知道订单是否需要配件（我猜这就是您有复选框的原因。），您可以创建一个查询以查看配件是否是您的报告/发票上的产品之一。