访问表单:如何映射复选框的值 [英] Access form: How to map values for a checkbox

问题描述

我在oracle中有一个表.该表具有一个整数字段，其中包含2个可能的值:null或1.对于我们的应用程序，null表示false，1表示true.

I have a table in oracle. The table has an integer field with 2 possible values: null or 1. For our applications, null means false, and 1 means true.

我正在Access 2010中为该表创建一个表单.我希望将该字段表示为一个复选框(启用1，禁用null).

I'm creating a form in access 2010 for that table. I want the field to be represented as a checkbox (enabled for 1, disabled for null).

如果我直接从该字段创建复选框，则将空值显示为矩形，并正确显示1(已选中).但是，如果我更改复选框的值(矩形->已选中，或已选中->未选中)，并将数据保存到数据库中，则已选中的值将另存为-1(而不是1)，并且未修改的值是保存为0(而不是null).

If I create the checkbox directly from the field, then a null value is shown as a rectangle, and a 1 is shown correctly (as checked). But, if I change the value of the checkbox (rectangle -> checked, or checked -> unchecked), and save data to the database, then a checked value is saved as -1 (instead of 1), and an unckecked value is saved as 0 (instead of null).

如果我通过公式创建复选框:

If I create the checkbox from a formula:

Switch(my_field='1','-1',my_field Is Null,'0')

然后正确显示值，但是我无法更改复选框状态(选中或取消选中).

then the values are shown correctly, but I can't change the checkbox status (check it or unckeck it).

我认为应该有一种简便的方法来进行映射，因为这种情况很常见，但是我找不到解决方法.

I think there should be an easy way to do the mapping, since it is a frequent case, but I can't find the way to do it.

推荐答案

解决此问题的一种方法是在表单上创建一个隐藏的TextBox控件(.Visible = No).对于此示例，我将其称为txtMyIntAsBoolean.它绑定到表中可以包含Null/1值的字段.

One way to work around your issue is to create a hidden TextBox control (.Visible = No) on your form. For this example I'll call it txtMyIntAsBoolean. It is bound to the field in your table that can contain the Null/1 value.

您的(可见)复选框控件应未绑定. .Value将是True或False(真布尔型)，具体取决于是否选中它.

Your (visible) Checkbox control should be unbound. It's .Value will be either True or False (true Boolean) depending on whether or not it is checked.

现在，您所需要的只是表格后面的两行VBA代码.使用类似于以下代码的代码为Form创建一个On Current事件处理程序，并为Checkbox创建一个After Update事件处理程序:

Now all you need are two lines of VBA code behind your form. Create an On Current event handler for the Form, and an After Update event handler for the Checkbox with code similar to this:

Option Compare Database
Option Explicit

Private Sub chkMyCheckbox_AfterUpdate()
    Me.txtMyIntAsBoolean.Value = IIf(Me.chkMyCheckbox.Value, 1, Null)
End Sub

Private Sub Form_Current()
    Me.chkMyCheckbox.Value = (Not IsNull(Me.txtMyIntAsBoolean))
End Sub

当您移至新记录时，On Current事件将更新Checkbox的值，而After Update事件将根据Checkbox的状态将隐藏的文本框的值设置为适当的值.

The On Current event updates the value of the Checkbox when you move to a new record, and the After Update event sets the value of the hidden Text Box to the appropriate value based on the status of the Checkbox.

这篇关于访问表单:如何映射复选框的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！