Microsoft Access Error 3061: Too few parameters. Expected 1

Question

I am working on number 2 in my code. The idea here is to create a query that can archive data from one access database to another using the front end forms in access. I keep getting this error and cant figure it out. It seems to be the WHERE component in referencing my Form. The code in the prior step correctly creates the string to be inputted into the WHERE clause, and that is just a textbox on the form. The place where I got the base code from here:

https://www.everythingaccess.com/tutorials.asp?ID=Archive%3A-Move-records-to-another-table

This is the code below:

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:\Data\Pipeline Detail\ArchiveDatabase.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 Documents\MyArchive.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

Try with:

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

or:

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