仅在Access 2007中不存在时添加记录 [英] Add record only if doesn't exist in Access 2007

问题描述

我有一个表，假设它存储职员姓名(这不是确切的情况，但是概念是相同的)，并且主键设置为自动编号.在添加新的职员时，我想检查该名称在添加之前是否已存在于数据库中，如果该名称已经存在，则可能会给出错误提示.如何从表格的正常添加表单中执行此操作?

I have a table, let's say it stores staff names (this isn't the exact situation, but the concept is the same), and the primary key is set to an autonumber. When adding a a new member of staff I want to check to see if that name exists in the database before it is added, and maybe give an error if it already exists. How can I do this from the normal add form for the table?

我尝试为其创建查询，但由于该表格基于表格并且不能使用查询作为控制源，因此无法正常工作.我在网上看到了一些示例，这些示例说如何使用VB代码执行类似的操作，但由于它不是一个简单的示例，因此我无法使其正常工作，并且遗漏了一些行.

I tried creating a query for it but that won't work because the form is based on the table and can't use a query as the control source. I saw some examples online saying how to do something like this with VB code, but I couldn't get it to work as it wasn't a simple example and some lines were left out.

有没有简单的方法可以做到这一点?

Is there any simple way in which this can be done?

推荐答案

您正在处理将记录插入数据库之前的资格预审问题.您可以永不违反的简单绝对规则(例如永不永不允许记录相同名称的记录)可以通过数据库约束来处理，在这种情况下，请在相关列上创建索引将AllowDuplicates设置为否".

You're dealing with the issue of pre-qualifying records before inserting them into the database. Simple and absolute rules that you will never violate (like never, ever, ever allowing to records with the same name) can be dealt with through database constraints — in this case creating an index on the column in question with AllowDuplicates set to No.

但是，在现实世界中，资格预审通常更为复杂.您可能只需要警告 可能重复项的用户，但无论如何都要允许他们添加记录.并且您可能需要检查其他表的某些条件，或者一次收集多个表的信息.

However, in the real world pre-qualification is generally more complex. You may need to simply warn the user of a possible duplicate, but allow them to add the record anyway. And you may need to check other tables for certain conditions or to collect information for more than one table at a time.

在这些情况下，您需要编写接口，以便它不直接绑定到表(在Access术语中，创建一个记录源为空的表单)，在各种控件中收集信息，在代码中执行检查(通常是使用DCOUNT和DLOOKUP)，然后使用DoCmd.RunSQL在代码中发出一系列INSERT和UPDATE语句.

In these cases you need to write your interface so it is not directly bound to the table (in Access terms, create a form with the record source empty), collect the information in various controls, perform your checks in code (often using DCOUNT and DLOOKUP) and then issue a series of INSERT and UPDATE statements in code using DoCmd.RunSQL.

您有时可以使用一些技巧来避免不得不在前端进行编码，但是您很快就会遇到需要这种编码级别的情况.

You can occasionally use some tricks to get around having to code this in the front end, but sooner rather than later you'll encounter cases that require this level of coding.

这篇关于仅在Access 2007中不存在时添加记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！