重复输入警告消息框

Question

当输入重复号码时，我正在尝试显示一个消息框。如果两个字段匹配、Source和Voucher_Number，则此表中的副本将为。

源被格式化为文本。

Voucher_Number被格式化为数字

下面是我的代码：

If (IsNull(DLookup("Source", "tblInvoiceLog", "Source ='" & 
Me.Source.Value & "'"))) And _
(IsNull(DLookup("Voucher_Number", "tblInvoiceLog", "Voucher_Number ='" &
Me.Voucher_Number.Value & "'"))) Then

Else
MsgBox "Duplicate Entery!" & Chr(13) & Chr(10) & "Please Use the Next
Available Consecutive Voucher Number", vbInformation, "Required"
End If

End Sub

我得到了：

运行时错误3464

除了解决这个问题之外，我最终想要做的是在消息框中返回原始条目的Vendor_Name中字段的值。

谢谢你的帮助，任何人都可以

Answer 1

您可以尝试将DLookup返回值具体转换为字符串，以确保将苹果与苹果进行比较。如果仍然有错误，请使用F8执行该错误，并在s1stLookup和s2ndLookup上悬停以查看为变量分配了哪些值。

Dim s1stLookup as String
Dim s2ndLookup as String

'Specifically cast the DLookup return values as Strings
s1stLookup = CStr(DLookup("Source", "tblInvoiceLog", "Source ='" & Me.Source.Value & "'"))
s2ndLookup = CStr(DLookup("Voucher_Number", "tblInvoiceLog", "Voucher_Number ='" & Me.Voucher_Number.Value & "'"))

If (IsNull(s1stLookup)) And (IsNull(s2ndLookup)) Then
  '... Presumably some code here
Else
  MsgBox "Duplicate Entery!" & vbCrLF & _
    "Please Use the Next Available Consecutive Voucher Number", _
    vbInformation, "Required"
End If

Answer 2

目前，您正在分别测试这两个值。但

如果两个字段匹配、Source和Voucher_Number，则此表中的副本将为。

因此，您需要测试两个值是否存在于同一个记录中，方法是将这两个条件与DLookup调用放在一起并放入其中。如果[Vendor_Name]是一个副本，那么您可以直接查找这个字段：

Dim sVendor as String
sVendor = Nz(DLookup("Vendor_Name", "tblInvoiceLog", _
    "Source = '" & Me.Source & "' AND Voucher_Number = " & Me.Voucher_Number), "")

If sVendor <> "" Then
    MsgBox "Duplicate entry of vendor '" & sVendor & "'."
Else
    ' ok, no dupe
End If

我发现最好总是在Nz()中使用DLookup()，这样我就可以处理字符串而不是变体。空字符串表示没有结果。