Run-time Error '3061' Too Few Parameters. Expected 1

[Eezeepc]

Hi
I hope someone can help me.
Since upgrading to Office 2016, I now receive the 3061 run time error when I attempt to delete a record in my database. I am a novice access user and whilst able to create queries and reports, my knowledge of vba is limited so please bear with me.
The following code is designed to delete a single record and move a subset of the data to a 'deleted people' table:

Private Sub cboDelReason_AfterUpdate()
    
    Dim rstPubs As DAO.Recordset
    
On Error GoTo Err_cboDelReason_AfterUpdate
    
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    zStr = "SELECT tblPeople.PeopleID, tblPeople.Title, tblPeople.[First Name], "
    zStr = zStr & "tblPeople.[Last Name], tblPeople.Postcode, tblPeople.Telephone, tblPeople.[Date of Entry], "
    zStr = zStr & "tblRoles.[Role Name], tblPeopleRoles.Barcode "
    zStr = zStr & "FROM tblPeople LEFT JOIN (tblPeopleRoles LEFT JOIN tblRoles ON "
    zStr = zStr & "tblPeopleRoles.RoleID = tblRoles.RoleID) ON "
    zStr = zStr & "tblPeople.PeopleID = tblPeopleRoles.PeopleID "
    zStr = zStr & "WHERE ((tblPeople.PeopleID) "
    zStr = zStr & "Like '" & [Forms]![frmEditPeople]![PeopleID] & "')"

    Set dbs = CurrentDb()
    Set rstData = dbs.OpenRecordset(zStr, dbOpenDynaset)
    rstData.MoveFirst
    If rstData.BOF Then
    Else
        Set rst = dbs.OpenRecordset("tblDelPeople", dbOpenDynaset)
        rst.AddNew
        rst!Title = Me!Title
        rst![First Name] = Me![First Name]
        rst![Last Name] = Me![Last Name]
        rst!PeopleID = Me![PeopleID]
        rst!Postcode = Me!Postcode
        rst![Date Joined] = Me![Date of Entry]
        rst![Date Deleted] = Format(Now(), "dd/mm/yyyy")
        rst![Telephone] = Me![Telephone]
        rst!Reason = Me!cboDelReason
        rst.Update
        
        Set rstPubs = dbs.OpenRecordset("tblDelPeople_Roles", dbOpenDynaset)
        Do Until rstData.EOF
            rstPubs.AddNew
            rstPubs!PeopleID = rstData![PeopleID]
            rstPubs![Pub] = rstData![Role Name]
            rstPubs![Barcode] = rstData![Barcode]
            rstPubs.Update
            rstData.MoveNext
        Loop
        
        zStr = "DELETE tblPeople.*, tblPeople.PeopleID "
        zStr = zStr & "FROM tblPeople "
        zStr = zStr & "WHERE ((tblPeople.PeopleID)= " & Me![PeopleID] & ")"
        dbs.Execute zStr
        Me.Requery
        Me.Refresh
        Me.PeopleID.SetFocus
        Me.cboDelReason.Visible = False
    End If
    
Exit_cboDelReason_AfterUpdate:
    rst.Close
    rstData.Close
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Set dbs = Nothing
    Exit Sub
    
Err_cboDelReason_AfterUpdate:
    Select Case Err
    Case 91
        Resume Next
    Case 3021
        Resume Exit_cboDelReason_AfterUpdate
    Case Else
        MsgBox Err.Number & ":- " & Err.Description
        Resume Next
    End Select
    
End Sub

After searching, the solution might be with adding a ' character in the where statement but I'm not sure if this is correct. The database is used to manage volunteers who donate time to a charity.

Many thanks
Chris

 

[isladogs]

Hi ezee and welcome to AWF.

I'm sorry to tell you that the vast majority of your code is redundant.
Instead of using a SELECT statement followed by a recordset append loop and a DELETE query, all you need is an APPEND query followed by the DELETE.
Using dB.execute, the set warnings code can be removed

Also...
IF PeopleID is a text field you need text delimiters for both queries as in the first query. If its a number field, the second query is the correct approach.
In the first query you've wrongly used Like with no wildcard. It should be =.

If the code is being run from the form use the Me. notation for both queries. If outside the for, you need the Forms!....notation.

Also, you can handle the error 91 using the Nz function or similar.

However I question whether you should be doing this at all. Add a Boolean field Active to the table tblPeople and set its default value =True. If someone is no longer active, use an update statement to set the field = False. No need for the other two tables.

Sorry to be critical. I hope it helps.

 

[arnelgp]

if PeopleID is you will need to add ' to your delete statement:

zStr = "DELETE tblPeople.*, tblPeople.PeopleID "
zStr = zStr & "FROM tblPeople "
zStr = zStr & "WHERE ((tblPeople.PeopleID)= '" & Me![PeopleID] & "')"

 

[Eezeepc]

Huge thanks to both isladogs and arnelgp for the rapid response. I mentioned that I am a novice so much as I would like to follow the recommendations from isladogs, my level of knowledge is too low.

Taking the simpler suggestion of adding ' character to the WHERE statement did not work. PeopleID is a number.

This code is used behind a Form. Having selected the person to remove, a reason code is requested and that starts the code I included.

Another thing I have found since upgrading to Access 2016, I had to modify the references as I was getting a type mismatch error. The references are now:
Visual Basic for Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Visual Basic for Applications Extensability 5.3

I unchecked
Microsoft Office 14.0 Access Database Engine Object Library

Sorry for my lack of knowledge. The developer has left the charity and I am the only remaining support - such that it is!

 

[theDBguy]

I unchecked
Microsoft Office 14.0 Access Database Engine Object Library

Hi. Sorry for barging in but I was just curious. Were you really able to uncheck the above reference?

 

[theDBguy]

Never mind. I am answering my own question. For some reason, I thought that reference is required, so I checked. It turns out you could remove it. Very interesting. I wouldn't recommend it though. Cheers!

 

[isladogs]

Hi. Sorry for barging in but I was just curious. Were you really able to uncheck the above reference?

And even if you could, you shouldn't do so. Its a default reference and is needed for many standard functions to work.

However, depending on your code, you may not need:
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Visual Basic for Applications Extensibility 5.3

Sorry if you found my comments too complex to follow

If you are able to upload your app, someone can have a look at this issue and help with simpler & better code.

I advise my final recommendation - just add an extra Active field and don't delete any records at all

 

[arnelgp]

on your first query you also has single quote, so remove it.

 

[isladogs]

Hmm...see my answer in post 2

 

[Eezeepc]

I really appreciate all the comments made.
I guess I'm looking for an easy solution because the database worked perfectly with Access 2010 and now gives the error 3061 using Access 2016. It is very frustrating and the bottom line is I just want it to work. I do need to save a deleted volunteer record in the table delPeople to comply with GDPR regulations. There is no doubt that the code could be improved but I am an administrator, not a programmer as you can tell.

 

[isladogs]

I really appreciate all the comments made.
I guess I'm looking for an easy solution because the database worked perfectly with Access 2010 and now gives the error 3061 using Access 2016. It is very frustrating and the bottom line is I just want it to work. I do need to save a deleted volunteer record in the table delPeople to comply with GDPR regulations. There is no doubt that the code could be improved but I am an administrator, not a programmer as you can tell.

To comply with GDPR, you need to retain records that are no longer active. You do NOT need to put them in a separate table.
The easiest solution is what I wrote in post #2 in place of all your existing code:

Add a Boolean (Yes/No) field called Active to the table tblPeople and set its default value =True. If someone is no longer active, use an update statement to set the field = False. No need for the other two tables.

Whether or not you go down that route, as I said before, if you can upload a stripped down copy of your app, one of us can assist further if you need it