vba 使用 DLookup (With Multiple Criteria) 检查记录是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10257153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking if record exists using DLookup (With Multiple Criteria)
提问by InvalidSyntax
Im trying to create a function in my VBA where if the record they are trying to insert already exists however it returns a type mismatch.
我试图在我的 VBA 中创建一个函数，如果他们尝试插入的记录已经存在，但是它返回类型不匹配。
EventCombo is a integer MedalCombo is string
EventCombo 是整数 MedalCombo 是字符串
Private Sub MyCombo_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("RacerID", "Medals", "RaceID = " + EventCombo.Value _
+ " AND Medal = '" + MedalCombo.Value + "'" )) Then
MsgBox "Record Exists"
End If.
End Sub
What this does (or is supposed to do) is make sure no one else has the same medal in the same race.
这所做的（或应该做的）是确保没有其他人在同一场比赛中获得相同的奖牌。
What am I doing wrong?
我究竟做错了什么？
采纳答案by Dkellygb
With combo boxes in Access, you need to make sure that .value is really what you want. Often the first column is hidden which is .value while what is visible on the drop down box is not .value. When using a combo box to eliminate confusion I use the .columns property.
使用 Access 中的组合框，您需要确保 .value 确实是您想要的。通常第一列是隐藏的，即 .value 而下拉框中可见的不是 .value。当使用组合框来消除混淆时，我使用 .columns 属性。
Also, to make sure the result from the combo box is a number and not text (as you did not use quotes in your example) I used the val() function to convert the combobox data to a number. If it already is a number, this will have no effect. Otherwise, if it is a digit stored as a string, it will convert it to a number. This might not be strictly necessary but it eliminates another possible problem. If the combobox column has a value which is some text which cannot convert to a number it will return 0 which you can test for in your code.
此外，为了确保组合框的结果是数字而不是文本（因为您在示例中没有使用引号），我使用 val() 函数将组合框数据转换为数字。如果它已经是一个数字，这将不起作用。否则，如果它是一个以字符串形式存储的数字，它会将其转换为数字。这可能不是绝对必要的，但它消除了另一个可能的问题。如果组合框列的值是一些无法转换为数字的文本，它将返回 0，您可以在代码中进行测试。
I cleaned up your code a bit with the following
我用以下内容稍微清理了您的代码
- I replaced the + with & like Remou said
- changed .value to .columns(0). If the column you are looking for is not the first one, change 0 to the appropriate value
- value() function
- removed line continuation _. (Personal preference, feel free to ignore)
- 我像 Remou 说的那样用 & 替换了 +
- 将 .value 更改为 .columns(0)。如果您要查找的列不是第一列，请将 0 更改为适当的值
- 值（）函数
- 删除了续行_。（个人喜好，可无视）
Private Sub MyCombo_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("RacerID", "Medals", "RaceID = " & Val(EventCombo.Columns(0)) & " AND Medal = '" & MedalCombo.Columns(0) & "'")) Then
MsgBox "Record Exists"
End If
End Sub
