访问运行时错误“3061”:参数太少。预计1
问题描述:
我得到一个Access运行时错误:访问运行时错误“3061”:参数太少。预计1
'3061': Too few parameters. Expected 1
试图运行从分报告形式的一些VBA代码时。
我试着在SQL查询中使用变量(CurAssetID
)以及直接链接(Forms!Details!ID
),但都导致相同的错误。我只是为了验证它正在拾取正确的值而放入MsgBox。
如果我更换一个值(HAVING (((Assignments.AssetID)=1));"
)的SQL查询的最后部分,它工作正常。这里发生了什么,我该如何解决它?
Private Sub LineSelect_Click()
CurAssetID = Forms!Details!ID
Status = MsgBox(CurAssetID, vbOKOnly)
Dim LastAssignment As DAO.Recordset
LastAssignmentSQL = "SELECT Assignments.AssetID, Last(Assignments.LocationID) AS LastLocationID FROM Assignments GROUP BY Assignments.AssetID HAVING (((Assignments.AssetID)=CurAssetID));"
Set LastAssignment = CurrentDb.OpenRecordset(LastAssignmentSQL, dbOpenDynaset, dbSeeChanges)
答
可以创建与在其内部串联参数值的字符串变量:
curAssetID = Forms!Details!ID
status = MsgBox(CurAssetID, vbOKOnly)
Dim lastAssignment As DAO.Recordset
lastAssignmentSQL = "SELECT Assignments.AssetID, " & _
"Last(Assignments.LocationID) AS LastLocationID " & _
"FROM Assignments " & _
"GROUP BY Assignments.AssetID " & _
"HAVING (((Assignments.AssetID)=" & CurAssetID & "));"
Set lastAssignment = CurrentDb.OpenRecordset(lastAssignmentSQL, dbOpenDynaset, dbSeeChanges)
' ...
作为附加的预防措施,如果Forms!Details!ID
被suposed是一个号码(Integer
或Long
),I建议您明确声明变量CurAssetID
:
Dim curAssetID as Integer ' Or Long
curAssetID = Forms!Details!ID
Dim lastAssignment As DAO.Recordset
lastAssignmentSQL = "SELECT Assignments.AssetID, " & _
"Last(Assignments.LocationID) AS LastLocationID " & _
"FROM Assignments " & _
"GROUP BY Assignments.AssetID " & _
"HAVING (((Assignments.AssetID)=" & CurAssetID & "));"
Set lastAssignment = CurrentDb.OpenRecordset(lastAssignmentSQL, dbOpenDynaset, dbSeeChanges)
' ...
如果该值为'字符串',则应将该值用引号括在查询中:
Dim curAssetID as String
' ...
lastAssignmentSQL = "SELECT Assignments.AssetID, " & _
"Last(Assignments.LocationID) AS LastLocationID " & _
"FROM Assignments " & _
"GROUP BY Assignments.AssetID " & _
"HAVING (((Assignments.AssetID)= '" & CurAssetID & "'));"
' ...
+0
这工作得很好,谢谢你的帮助! – user3377449
您的SQL无法看到您的VBA参数。 –