Too Few Parameters : Expected 1 performing while using UPDATE

Question

Option Compare Database

Private Sub load_data()
    Dim sql As String
    sql = "Select * from COMM_RRC"
    List21.ColumnHeads = True
    List21.ColumnCount = 12
    List21.RowSourceType = "Table/Query"
    List21.RowSource = sql
End Sub

Private Sub Command16_Click()
    CurrentDb.Execute "Insert Into COMM_RRC(ID_NO,DATEIST,INBOUND_FLTS,IB_SECTOR,APT,OUTBOUND_FLTS,OB_SECTOR,GUESTS,MAX_DIS_TIME,DISP_ACT,REMARKS_COMM_RRC,NOTE_COMM_RRC,CODE_SHARE_FLTS)" & _
    "Values('" & ID_NO.Value & "','" & DATEIST.Value & "','" & INBOUND_FLTS.Value & "','" & IB_SECTOR.Value & "','" & APT.Value & "','" & OUTBOUND_FLTS.Value & "','" & OB_SECTOR.Value & "','" & GUESTS.Value & "','" & MAX_DIS_TIME.Value & "','" & DISP_ACT.Value & "','" & REMARKS_COMM_RRC.Value & "','" & NOTE_COMM_RRC.Value & "','" & CODE_SHARE_FLTS.Value & "')"
    Call load_data
End Sub

Private Sub Command17_Click()
    ID_NO.Value = ""
    DATEIST.Value = ""
    INBOUND_FLTS.Value = ""
    IB_SECTOR.Value = ""
    APT.Value = ""
    OUTBOUND_FLTS.Value = ""
    OB_SECTOR.Value = ""
    GUESTS.Value = ""
    MAX_DIS_TIME.Value = ""
    DISP_ACT.Value = ""
    REMARKS_COMM_RRC.Value = ""
    NOTE_COMM_RRC.Value = ""
    CODE_SHARE_FLTS.Value = ""
End Sub

Private Sub Command18_Click()
    CurrentDb.Execute "Update COMM_RRC Set DATEIST = '" & DATEIST.Value & "',INBOUND_FLTS = '" & INBOUND_FLTS.Value & "',IB_SECTOR = '" & IB_SECTOR.Value & "',APT = '" & APT.Value & "',OUTBOUND_FLTS = '" & OUTBOUND_FLTS.Value & "',OB_SECTOR = '" & OB_SECTOR.Value & "',GUESTS = '" & GUESTS.Value & "',MAX_DIS_TIME = '" & MAX_DIS_TIME.Value & "',DISP_ACT = '" & DISP_ACT.Value & "',REMARKS_COMM_RRC = '" & REMARKS_COMM_RRC.Value & "',NOTE_COMM_RRC = '" & NOTE_COMM_RRC.Value & "',CODE_SHARE_FLTS = " & CODE_SHARE_FLTS.Value & " where ID_NO =  '" & ID_NO.Value & " ';"
    Call load_data
End Sub

Private Sub Command19_Click()
    CurrentDb.Execute "Delete from COMM_RRC Where ID_NO = " & ID_NO.Value & ""
    Call load_data
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()
    Call load_data
End Sub

Private Sub List21_AfterUpdate()
    ID_NO.Value = List21.Column(0)
    DATEIST.Value = List21.Column(1)
    INBOUND_FLTS.Value = List21.Column(2)
    IB_SECTOR.Value = List21.Column(3)
    APT.Value = List21.Column(4)
    OUTBOUND_FLTS.Value = List21.Column(5)
    OB_SECTOR.Value = List21.Column(6)
    GUESTS.Value = List21.Column(7)
    MAX_DIS_TIME.Value = List21.Column(8)
    DISP_ACT.Value = List21.Column(9)
    REMARKS_COMM_RRC.Value = List21.Column(10)
    NOTE_COMM_RRC.Value = List21.Column(11)
    CODE_SHARE_FLTS.Value = List21.Column(12)
End Sub

Private Sub List21_Click()

End Sub

I am trying to create a simple data entry form where in operations like Add, New, Update and Delete can be performed. (I am Using Microsoft 365)

Here is the structure of Database Database structure1 and this one Database structure2

But I am getting this error - "Run time error 3061. Too few parameters. Expected 1." ERROR MSG

After clicking on Debug ..the editor is pointing towards this block of code -

Error in Code

Private Sub Command18_Click()
    CurrentDb.Execute "Update COMM_RRC Set DATEIST = '" & DATEIST.Value &
        "',INBOUND_FLTS = '" & INBOUND_FLTS.Value & "',IB_SECTOR = '" &
        IB_SECTOR.Value & "',APT = '" & APT.Value & "',OUTBOUND_FLTS = '" & 
        OUTBOUND_FLTS.Value & "',OB_SECTOR = '" & OB_SECTOR.Value & "',GUESTS = '" &
        GUESTS.Value & "',MAX_DIS_TIME = '" & MAX_DIS_TIME.Value &
        "',DISP_ACT = '" & DISP_ACT.Value & "',REMARKS_COMM_RRC = '" &
        REMARKS_COMM_RRC.Value & "',NOTE_COMM_RRC = '" & NOTE_COMM_RRC.Value &
        "',CODE_SHARE_FLTS = " & CODE_SHARE_FLTS.Value & " where ID_NO =  '" &
        ID_NO.Value & " ';"
    Call load_data
End Sub

And when i click on END the record gets UPDATED but creates another duplicate copy.

How can stop this error and UPDATE the record.

Answer 1

In Command17_Click() you write

CODE_SHARE_FLTS.Value = ""

this means that CODE_SHARE_FLTS is a string. But in Command18_Click() you have no '':

CODE_SHARE_FLTS = " & CODE_SHARE_FLTS.Value &  " where ID_NO =  '" & ID_NO.Value & " ';"

This means that it is a numerical value. Please check what is correct. For testing it is helpful to execute the SQL statement with

docmd.RunSQL

This offers more valuable info in case of errors. Later you can change this to

CurrentDB.Execute [Your SQL string], dbFailOnError

Answer 2

I suggest you open a recordset based on a parameter query which targets the row matching your target ID_NO value.

If the recordset is empty at open, you want to add a new row with the ID_NO value. Then, whether or not the recordset was empty at open, you can update the values of the remaining fields.

Here is an example for the first 3 fields of your table. Call it from your "add" and "update" command buttons.

Private Sub InsertOrUpdate()
    Dim strSelect As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    strSelect = "SELECT ID_NO, DATEIST, INBOUND_FLTS FROM COMM_RRC" & vbCrLf _
        & "WHERE ID_NO = [which_id];"
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(vbNullString, strSelect)
    qdf.Parameters("which_id").Value = Me.ID_NO.Value
    Set rs = qdf.OpenRecordset
    If rs.EOF Then
        rs.AddNew
        rs!ID_NO.Value = Me.ID_NO.Value
    Else
        rs.Edit
    End If
    rs!DATEIST.Value = Me.DATEIST.Value
    rs!INBOUND_FLTS.Value = Me.INBOUND_FLTS.Value
    rs.Update
End Sub

Answer 3

For starters, you should ALWAYS have the statement Option Explicit as the first line in each module. This will ensure you have no hidden errors due to misspelled variable names.

Now to your problem. Your issue lies in two areas of the SQL statement. @Zen actually identified both. ID_NO is numeric and should not be enclosed in quotes. In addition to that and going back to my previous comment, you are appending a space to ID_NO.value in your WHERE clause which needs to be removed. You also need to enclose CODE_SHARE_FLTS.value in quotes. Your final UPDATE statement should read as follows (formatted for readability). You can just copy and paste this into your code:

    CurrentDb.Execute "Update COMM_RRC Set " & _
                      "DATEIST = '" & DATEIST.Value & "'," & _
                      "INBOUND_FLTS = '" & INBOUND_FLTS.Value & "'," & _
                      "IB_SECTOR = '" & IB_SECTOR.Value & "'," & _
                      "APT = '" & APT.Value & "'," & _
                      "OUTBOUND_FLTS = '" & OUTBOUND_FLTS.Value & "'," & _
                      "OB_SECTOR = '" & OB_SECTOR.Value & "'," & _
                      "GUESTS = '" & GUESTS.Value & "'," & _
                      "MAX_DIS_TIME = '" & MAX_DIS_TIME.Value & "'," & _
                      "DISP_ACT = '" & DISP_ACT.Value & "'," & _
                      "REMARKS_COMM_RRC = '" & REMARKS_COMM_RRC.Value & "'," & _
                      "NOTE_COMM_RRC = '" & NOTE_COMM_RRC.Value & "'," & _
                      "CODE_SHARE_FLTS = '" & CODE_SHARE_FLTS.Value & "' " & _
                      "Where ID_NO = " & ID_NO.Value & ";", dbFailOnError

@MikaOukka also pointed out a potential issue with DATEIST which you should address. If you leave it as is then there is a potential for it to come back and bite you in future.