基本VBA代码(dlookup)

Question

我是VBA新手，需要帮助才能让命令工作。我需要有2个值查找，并创建一个新的记录，当一个按钮被按下。我对dlookup有问题。

Private Sub Add_Record_Click()
Dim db As Database
Dim r As Recordset
Dim x As Integer

Set db = CurrentDb
Set r = db.OpenRecordset("Copy Of Employee Work Statistics")

Y = r.Fields("School")
z = r.Fields("School Id")

Y = DLookup(Table![Routes].School, Table![Routes], "Route" = Table![Routes].[Route Name])
z = DLookup("school ID", "Customer Database", "school" = Y)

r.Update

DoCmd.GoToRecord , , acNewRec

End Sub

Answer 1

DLookup("school ID", "Customer Database", "school" = Y)

您的语法错误，您应该在字符串中包含所有条件(第三个参数)，如下所示：

DLookup("[school ID]", "[Customer Database]", "[school] = '" & Y & "'")

其中&用于字符串连接，Y是先前声明的变量。我还鼓励您在引用列或表名时使用方括号([，])，以避免由于保留字造成的任何冲突，或者由于名称中的空格造成的任何有趣的错误。

至于更新部分，y和z仅仅是变量。我猜你的意图是，直接更新你的记录集。因此：

r.Fields("School") = DLookup("[" & Table![Routes].School & "]", _
"[" & Table![Routes] & "]", "[Route] = '" & Table![Routes].[Route Name] & "'" )

r.Fields("School Id") = DLookup("[school ID]", "[Customer Database]", _
"[school] = '" & Y & "'")

r.Update