SQL参数提示符

Question

我试图在VBA中运行SQL命令从表中删除某些记录。当我运行它时，会提示输入参数。是什么导致了这种情况?我已经包含了包含SQL.

的子程序。

Public Sub AddCon(newCont, svID)
Dim daDb As DAO.Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim selContract As String
Set daDb = CurrentDb
Set rst1 = daDb.OpenRecordset("tblContracts")
Set rst2 = daDb.OpenRecordset("tblContractList")

rst2.AddNew
rst2!Contract = newCont
rst2!ID = svID
rst2.Update
rst2.Close
Set rst2 = Nothing
DoCmd.Close
Dim strSQL As String
strSQL = "DELETE * FROM [tblContractList] " _
& "WHERE rst1.Contract <> newCont"

DoCmd.RunSQL strSQL

DoCmd.OpenForm "frmContracts"
End Sub

Answer 1

查询时，

DELETE * FROM [tblContractList] WHERE rst1.Contract <> newCont;

Microsoft Access引擎将通过提示符询问您两个未知数:rst1。Contract和newCont.

所以你应该用已知的值替换它们:

Public Sub AddCon(newCont, svID)
Dim daDb As DAO.Database
'Dim rst1 As Recordset
Dim rst2 As Recordset

' Dim selContract As String

Set daDb = CurrentDb
'Set rst1 = daDb.OpenRecordset("tblContracts")
Set rst2 = daDb.OpenRecordset("tblContractList")

rst2.AddNew
rst2!Contract = newCont
rst2!ID = svID
rst2.Update
rst2.Close
Set rst2 = Nothing
'DoCmd.Close

Dim strSQL As String
'
' DELETE query must be run with care, as useful data may disappear!!!
'
strSQL = "DELETE * FROM tblContractList " _
& "WHERE (Contract " & " <> " & newCont & ")"
'
' or single quoting newCont if it is a string:
'
'strSQL = "DELETE * FROM tblContractList " _
'    & "WHERE (Contract " & " <> '" & newCont & "')"
'
'rst1.Close
'Set rst1 = Nothing
Set daDb = Nothing
DoCmd.RunSQL strSQL

DoCmd.OpenForm "frmContracts"
End Sub