Microsoft Access错误3061:参数太少.应为1

Question

我正在处理代码中的数字2。这里的想法是创建一个查询，该查询可以使用访问中的前端表单将数据从一个访问数据库归档到另一个。我老是犯这个错误，弄不明白。它似乎是引用我的Form的WHERE组件。上一步中的代码正确地创建了要输入WHERE子句的字符串，而这只是表单上的一个文本框。我从这里获得基本代码的地方：

https://www.everythingaccess.com/tutorials.asp?ID=Archive%3A-将记录移动到另一个表

这是下面的代码：

Sub DoArchive()
On Error GoTo Err_DoArchive
Dim ws As DAO.Workspace   'Current workspace (for transaction).
Dim db As DAO.Database    'Inside the transaction.
Dim bInTrans As Boolean   'Flag that transaction is active.
Dim strSql As String      'Action query statements.
Dim strMsg As String      'MsgBox message.
Dim CrdStrng As String   'Cord Code for reference
CrdStrng = [Forms]![0 - Front End - Macros-Mapping]![String_CORD]
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSql1 As String
Dim Delete_Txtbox As TextBox
'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
Set frm = Forms("0 - Front End - Macros-Mapping")
Set ctl = frm![CORD Type Selector]
Set Delete_Txtbox = frm![String_CORD]
'Step 2: Select Types
strSql1 = "[Type]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSql1 = strSql1 & Chr(34) & ctl.ItemData(varItem) & Chr(34) & " Or [Type]="
Next varItem
'Trim the end of strSQL
strSql1 = Left$(strSql1, Len(strSql1) - 11)
Delete_Txtbox.Value = Right(strSql1, Len(strSql1) - 0)
MsgBox Delete_Txtbox.Value
'Step 3: Execute the append.
strSql = "INSERT INTO [***Master Data***]( [Operated/Non-Operated], Level2, Level3, Level4, [Underlying/Allocation], [Business  ID], [Team ID], Team_Name, businessName, BU, Category, Material, [Asset Type], Grp_Name, Account_No, Account_Desc, [Higher Level Grp_Name], [GFO View], [MI View], ActiveYear, ActiveMonth, Type, [Gross Amount], [Net Amount], [Local Currency Gross], [Sort Order], VnOFlag, [VO Probability], [Rvrs-Act-Accr], [Line Segment], Notes, [Budget Owner], [High Level Budget Owner], [Sr Level Budget Owner], [Act-MI-Fcst], [PC - CC] ) " & _
"IN ""C:DataPipeline DetailArchiveDatabase.accdb"" " & _
"SELECT [***Detail Data Pivot Report***].[Operated/Non-Operated], [***Detail Data Pivot Report***].Level2, " & _
"[***Detail Data Pivot Report***].Level3, [***Detail Data Pivot Report***].Level4, [***Detail Data Pivot Report***].[Underlying/Allocation], " & _
"[***Detail Data Pivot Report***].[Business  ID], [***Detail Data Pivot Report***].[Team ID], [***Detail Data Pivot Report***].Team_Name, " & _
"[***Detail Data Pivot Report***].businessName, [***Detail Data Pivot Report***].BU, [***Detail Data Pivot Report***].Category, " & _
"[***Detail Data Pivot Report***].Material, [***Detail Data Pivot Report***].[Asset Type], [***Detail Data Pivot Report***].Grp_Name, " & _
"[***Detail Data Pivot Report***].Account_No, [***Detail Data Pivot Report***].Account_Desc, [***Detail Data Pivot Report***].[Higher Level Grp_Name], " & _
"[***Detail Data Pivot Report***].[GFO View], [***Detail Data Pivot Report***].[MI View], [***Detail Data Pivot Report***].ActiveYear, " & _
"[***Detail Data Pivot Report***].ActiveMonth, [***Detail Data Pivot Report***].Type, " & _
"[***Detail Data Pivot Report***].[Gross Amount], [***Detail Data Pivot Report***].[Net Amount], [***Detail Data Pivot Report***].[Local Currency Gross], " & _
"[***Detail Data Pivot Report***].[Sort Order], [***Detail Data Pivot Report***].VnOFlag, [***Detail Data Pivot Report***].[VO Probability], " & _
"[***Detail Data Pivot Report***].[Rvrs-Act-Accr], [***Detail Data Pivot Report***].[Line Segment], [***Detail Data Pivot Report***].Notes, " & _
"[***Detail Data Pivot Report***].[Budget Owner], [***Detail Data Pivot Report***].[High Level Budget Owner], " & _
"[***Detail Data Pivot Report***].[Sr Level Budget Owner] , [***Detail Data Pivot Report***].[Act-MI-Fcst], [***Detail Data Pivot Report***].[PC - CC] " & _
"FROM [***Detail Data Pivot Report***] " & _
"WHERE (CrdStrng);"
db.Execute strSql, dbFailOnError

'Step 3: Execute the append. (original code without fields entered)
'strSql = "INSERT INTO MyArchiveTable ( MyField, AnotherField, Field3 ) " & _
'"IN ""C:My DocumentsMyArchive.mdb"" " & _
'"SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField = True);"
'db.Execute strSql, dbFailOnError
'Step 4: Execute the delete.
strSql = "DELETE FROM [***Detail Data Pivot Report***] WHERE ([Forms]![0 - Front End - Macros-Mapping]![String_CORD]);"
db.Execute strSql, dbFailOnError
'Step 5: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If
Exit_DoArchive:
'Step 6: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then   'Rollback if the transaction is active.
ws.rollback
End If
Set ws = Nothing
Exit Sub
Err_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
Resume Exit_DoArchive
End Sub

Answer 1

尝试使用：

strSql1 = strSql1 & Chr(34) & ctl.ItemData(varItem) & Chr(34) & " Or [Type] = ''"

或：

strSql1 = strSql1 & Chr(34) & ctl.ItemData(varItem) & Chr(34) & " Or [Type] Is Null"