根据另一个字段的值启用/禁用某些字段的输入 [英] Enabling/disabling input into certain fields based on the value of another field

问题描述

在数据输入表中frmlastweeksactivities.当某行在活动类别"字段/列中的内容为金"或银"或铜"时，请禁用列/字段[5]& [6]

In the data entry form frmlastweeksactivities . When a row has "Gold"or "Silver" or "Bronze" as content in the ["category of activity "] field /column then disable columns/fields[5] & [6]

当一行的[活动类别]字段/列中的内容为木材"或干草"或茬"时，则禁用列/字段[8]& [9]

When a row has "Wood" or "Hay"or "Stubble "as content in the ["category of activity "] field /column then disable columns/fields[8] & [9]

禁用意味着在这种情况下不允许任何数据输入，也将它们也变红

Disable means not allow any data entry in this case turn them red as well would be nice

我正在尝试构建和表达式，以用于表验证规则属性或表单

I am trying to build and expression to use in either the table validation rule properties or in one of the control -txt boxes in the form

推荐答案

使用表级验证的解决方案

表级解决方案不会禁用字段，它要做的就是验证添加的记录.如果验证规则失败(评估为FALSE)，则会向用户显示警告消息，并且更新将被取消(未添加记录).

The table-level solution will not disable fields, all it will do is validate a record as it is added. If the validation rule fails (evaluates to FALSE) then the user will be presented with a warning message and the update will be cancelled (record not added).

为此的表达式应在Validation Rule表属性中输入，如下所示:

The expression for this should be entered in the Validation Rule Table Property as follows:

IIf((([category]="gold" Or [category]="silver" Or [category]="bronze") And (Len([Field5])>0 Or Len([Field6])>0)) 
 Or (([category]="Wood" Or [category]="stubble" Or [category]="hay") And (Len([Field8])>0 Or Len([Field9])>0))
,False,True)

警告:确保根据上面的表达将支架排成一行！！

该表达式比较混乱，但是它会测试您描述的2个失败条件"中的任何一个是否成立，即:

The expression is messy but it tests whether either of 2 "fail conditions" you describe are true, namely:

1)类别=金/银/青铜，并且Field5/Field6中的值不为空

1) Category = gold/silver/bronze AND not null value in Field5/Field6

2)类别=木材/茬/干草，并且Field8/Field9中的值不为空

2) Category = wood/stubble/hay AND not null value in Field8/Field9

然后在表格属性表"的Validation Text属性中输入您希望通知用户的记录失败的文本，需要更正后才能保存该记录.例如:

Then in the Validation Text property of the Table Property Sheet enter the text you wish to notify the user that their record fails and needs correction before the record can be saved. For example:

"If category is gold/silver/bronze then Field5 and Field6 must be blank, if wood/stubble/hay then Field8 and Field9 blank.  Record not saved, please correct before saving."

这是设计视图中表的屏幕快照，其中显示了属性表"和您需要更改的2个属性.

Here is a screen shot of the table in design view showing the Property Sheet and the 2 properties you need to change.

更好:使用事件驱动验证和解决方案的解决方案；控制表单中的锁定

鉴于Access表级和字段级验证将不执行所需的操作，因此该解决方案将在表单上利用事件驱动的验证.假定您有一个窗体，其中包含名为category，Field5，Field6，Field8和Field9的文本框控件.

Given that Access table-level and field-level validation will not do what is required, this solution will utilise event-driven validation on the form. It assumes that you have a form with text box controls called category, Field5, Field6, Field8 and Field9.

将窗体置于设计视图中，右键单击category字段(或同时按ALT和ENTER)以查看控件的属性.在屏幕右侧的属性表中，单击Events选项卡.这将显示所选控件的可能事件.请参见下面的屏幕截图.

With the form in design view, right click on the category field (or press ALT and ENTER simultaneously) to view the properties for the control. In the Property Sheet on the right of the screen click on the Events tab. This displays the possible events for the selected control. See the screenshot below.

您对After Update事件感兴趣，因此请单击它，然后单击右侧...上的按钮.选择Code Builder将为该表单打开一个新的VBA模块，并自动为您创建一个事件.

You are interested in the After Update event so click in it and then the buttton to the right .... Choosing Code Builder will open up a new VBA module for the form and create an event automatically for you.

Private Sub category_AfterUpdate()

    ' by default enable all four fields 5/6/8/9
    Me.Field5.Enabled = True
    Me.Field6.Enabled = True
    Me.Field8.Enabled = True
    Me.Field9.Enabled = True

    ' test the value entered by user in the category field and hide fields as required
    Select Case Me.category
        Case "Gold", "Silver", "Bronze"
            ' if the user has entered Gold, Silver or Bronze lock Fields 5/6
            Me.Field5.Enabled = False
            Me.Field6.Enabled = False
        Case "Wood", "Stubble", "Hay"
            ' if the user has entered Gold, Silver or Bronze lock Fields 8/9
            Me.Field8.Enabled = False
            Me.Field9.Enabled = False
    End Select

End Sub

因此，上面的代码将确保当用户更新category字段时，Sub和End Sub之间的代码将运行，并且此代码根据逻辑设置您提到的字段的Enabled属性.该Enabled属性(如果设置为False)将锁定该字段，使其不被激活(接受光标)，并将其显示为灰色，以使用户知道该字段已被禁用.

So the code above will ensure that when the user updates the category field the code between Sub and End Sub will run and this code sets the Enabled property for the fields you mentioned as per your logic. This Enabled property if set to False will lock the field from becoming active (accepting the cursor) and will grey it out so users are aware it is disabled.

最后，当用户移至新记录时，您需要再添加一段代码，以将4个字段重置为Enabled.因此，请回到设计视图中的表单，通过单击表单的一部分而不单击控件来访问Form对象本身的属性.例如，单击表单的页眉或页脚.当您看到表单的事件属性时，您将看到一个名为Current的事件.这是您创建事件过程所需的过程，其操作方法与上述...按钮基本相同.这将再次带您进入VBA窗口，您需要确保在此处输入以下代码:

Finally, when the user moves onto a new record you need to add one more piece of code, to reset the 4 fields to being Enabled. So back in the form in design view, access the properties for the Form object itself either by clicking on a part of the form but NOT on a control. For example click in the form header or footer. When you see the event properties for the form you will see an event named Current. This is the one you need to create an event procedure for in much the same way as above with the ... button. This will take you into the VBA window again and you need to make sure that the following code is entered there:

Private Sub Form_Current()

    ' by default enable all four fields 5/6/8/9
    Me.Field5.Enabled = True
    Me.Field6.Enabled = True
    Me.Field8.Enabled = True
    Me.Field9.Enabled = True

End Sub

这是VBA屏幕(其中包含所有代码)的外观的屏幕截图.

This is a screenshot of how the VBA screen should look with all of your code in it.

现在保存并运行表单时，它的行为应完全符合您的期望.如有任何疑问或问题，请发表评论，我会尽力提供帮助.

When you now save and run your form it should behave exactly as you wish. Any questions or issues please comment and I'll try to assist.

这篇关于根据另一个字段的值启用/禁用某些字段的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！