如何才能一组验证规则适用于所有形式的保存到一个特定的表？ [英] How can a set of validation rules be applied for all forms that save to a specific table?

问题描述

我有写同一个表中的多个表单。我想写入该表中的所有数据以相同的规则进行验证。我无法找到一个方法来应用验证在表级别。它看起来像它会被在每个表基础上采用更新前打电话给我的VBA完成。

我正在2013年访问某些领域的确认将基于在同一个记录等字段的内容。我需要做的字符串操作沿着以下伪code线：

 的field.Serial;做
  如果（field.Model ==叉车）;然后
    validation_good_if（（左（field.Serial，2）==FL）及及（长度（field.Serial）== 5）＆安培;及（右（field.Serial，3）ISNUMERIC））
ENDFOR

为field.AssetTag;做
  field.AssetTag =右（0000和放大器; field.AssetTag，8）
ENDFOR

对于field.Model，field.Location;做
  与toUpperCase
ENDFOR
 

什么是要确保所有的插入/更新到我的表进行相同的验证的最佳方法是什么？

解决方案

这个问题听起来像一个很好的候选人数据宏。这就是我想出了：

我也试图通过执行从外部程序的INSERT（C＃，使用ODBC），并在这方面的数据宏的作品，也测试了这一点：

cmd.CommandText =INSERT INTO表1（[产品型号]，[序列]）VALUES（？，？）; cmd.Parameters.AddWithValue（叉车，？）; cmd.Parameters.AddWithValue（FL21？）; 尝试 {     cmd.ExecuteNonQuery（）; } 赶上（例外五） {     Console.WriteLine（e.Message）; }

  

错误[HY000] [微软] [ODBC Microsoft Access驱动程序]验证失败叉车：序列号必须是五（5）个字符长

在验证成功的记录被正确地插入到表中。

I have multiple forms that write to the same table. I want all data written to that table to be validated with the same rules. I can not find a way to apply the validation at the table level. It looks like it will have be done on a per-form basis using BeforeUpdate to call my vba.

I am running Access 2013. The validation of some fields will be based on the contents of other fields in the same record. I will need to do string manipulations along the lines of the following pseudo-code:

for field.Serial; do
  if ( field.Model == "FORKLIFT" ); then
    validation_good_if( (left(field.Serial, 2) == "FL") && (length(field.Serial) == 5) && (right(field.Serial, 3) isNumeric) )
endFor

for field.AssetTag; do
  field.AssetTag = right("0000" & field.AssetTag, 8)
endFor

for field.Model, field.Location; do
  toUpperCase
endFor

What is the best way to make sure all inserts/updates to my table undergo the same validation?

解决方案

This question sounds like a good candidate for a Data Macro. Here's what I came up with:

I also tested this by trying to perform an INSERT from an external program (C#, using ODBC) and the Data Macro works in that context, too:

cmd.CommandText = "INSERT INTO Table1 ([Model], [Serial]) VALUES (?,?)";
cmd.Parameters.AddWithValue("?", "FORKLIFT");
cmd.Parameters.AddWithValue("?", "FL21");
try
{
    cmd.ExecuteNonQuery();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Validation failed for FORKLIFT: Serial Number must be five (5) characters long.

When the validation succeeds the record is correctly inserted into the table.

这篇关于如何才能一组验证规则适用于所有形式的保存到一个特定的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！