在VBA Access中识别3列PK重复项 [英] Identify a 3-column PK duplicate in VBA Access

问题描述

我似乎无法找出使用VBA来确定我的记录表是否已经具有数据的唯一3列组合的最佳方法.

I cannot seem to figure out the best way to identify whether my record table already has the unique 3-column combination of the data using VBA.

我的表格如下:

CREATE TABLE sometable(
col1 NUMERIC,
col2 NUMERIC,
col3 NUMERIC,
col4 NUMERIC,
Primary Key(col1, col2, col3)
);

现在在我的表单中，我正在向表中添加值(即，用户填写3个字段，单击一个add按钮，它将插入到表中).

Now in my form I'm adding values into the table (ie. user fills up 3 fields, clicks an add button and it inserts to the table).

如果已经有一行数据，则按add按钮不会执行任何操作.

If row of data is already there, pressing the add button does nothing.

我要实现的是(使用VBA)检查这样的行是否已经存在(基于col1& col2和col3的唯一性)，对新添加的现有记录的col4求和.

What I want to achieve is to check (using VBA) whether such row already exists (based on the uniqueness of col1 & col2 and col3) SUM up the col4 of an already existing record with the new addition.

例如，如果我已经存在的数据是这样的:

For example if my already existing data is something like:

col1 col2 col3 col4
1    2    3    10
1    3    3    20

并且用户想要添加:

1 1 1 10

它只会在表中添加一个新行. 但是，如果他要添加:

It would simply add a new row to the table. However, if he wants to add:

1 2 3 10

这将导致出现表:

col1 col2 col3 col4
1    2    3    20 'added up col4 here
1    3    3    20
1    1    1    10

我不希望将auto-ID递增为col0的原因是因为无论如何我都必须检查col1，col2和col3中的值，以确定是否应该添加新数据或只是重新计算新数据

The reason why I don't want to have an Auto-ID incrementing as col0 is because I would have to check the values in col1, col2 and col3 anyway to determine whether the new data should be added or simply re-calculated.

推荐答案

我发现了两种通用的处理方法. (1)包括错误处理-我发现这是一种不好的做法. (2)将包含DCount函数以首先查找数据行，然后使用if条件确定应使用UPDATE还是INSERT.

I found 2 general ways of handling this. (1) would include error handling - which I find to be a bad practise. (2) would include DCount function to find the row of data first, then an if condition to determine whether UPDATE or INSERT should be used.

因此，我使用(2)如下:

Hence I used (2) as follows:

If DCount("*", "sometable", "[col1]= " & Me.col1.value & " AND [col2] = " & Me.col2.value & " AND [col3] = " & Me.col3.value) = 0 Then
    CurrentDB.Execute "INSERT ..."
Else
    CurrentDB.Execute "UPDATE ..."
End If

(1)解决方案类似于:

The (1) solution would be something like:

On Error Resume Next
CurrentDb.Execute "INSERT ...", dbFailOnError
If Err.Number = 3022 Then
    Err.Clear        
    CurrentDb.Execute "UPDATE ...", dbFailOnError
End If

这篇关于在VBA Access中识别3列PK重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！