Error 3061. Too few parameters, expected 1 running a function.

[jimatqsi]

Gareth,
Look at your data. This doesn't nullify anything twinnyfo and Stewart have said, but I suspect you are encountering a name that has a quote within the name. You are not handling that possibility and when it happens it gives you a bad SQL string and the behavior you are seeing.

Jim

[jimatqsi]

As I reread these posts it came to me that you are saying the failure happens every time, no matter what the data is. Or maybe you are using the same data every time, in which case it is possible the first name has a single-quote in it. But that seems unlikely to me.

So for sure you post the SQL code as Twinnyfo requested. Let's see what that code really looks like.

[Gareth Jones]

Thanks all for continuing to look into this.

After a relatively small amount of manual work, I have the table where I need it, and have been able to workout the duration etc and the report is now effectively finished.

It would still be good to see where I have gone wrong with this function however so below is the current code. I am now using the employee reference instead of the name, which is a reference no. with no spaces, dashes etc.

            Public Function UpdateTable()
    On Error GoTo EH:
        Dim db As Database
        Dim rstNames As DAO.Recordset
        Dim rstCallOuts As DAO.Recordset
        Dim strSQL As String
        Dim intState1 As Integer
        Set db = CurrentDb()
        strSQL = "SELECT emp_ref " & _
            "FROM table2 " & _
            "GROUP BY emp_ref " & _
            "HAVING emp_ref <> 'N/A';"
        Set rstNames = db.OpenRecordset(strSQL, dbOpenDynaset)
        If Not rstNames.RecordCount = 0 Then
            rstNames.MoveFirst
            Do While Not rstNames.EOF
                strSQL = "SELECT * " & _
                    "FROM table2 " & _
                    "WHERE emp_ref = '" & rstNames!emp_ref & "' " & _
                    "ORDER BY TimeStamp;"
                    Debug.Print strSQL
                Set rstCallOuts = db.OpenRecordset(strSQL, dbOpenDynaset)
                Debug.Print strSQL
                If Not rstCallOuts.RecordCount = 0 Then
                Debug.Print strSQL
                    rstCallOuts.MoveFirst
                    intState1 = rstCallOuts!State1
                    rstCallOuts.MoveNext
                    Do While Not rstCallOuts.EOF
                        If rstCallOuts!State1 <> intState1 Then
                            intState1 = rstCallOuts!State1
                        Else
                            With rstCallOuts
                                .Delete
                            End With
                        End If
                        rstCallOuts.MoveNext
                    Loop
                End If
                rstNames.MoveNext
            Loop
        End If
        Exit Function
EH:
        MsgBox Err.Number & ":  " & Err.Description
        Exit Function
    End Function

The table only has 5 fields, ID, emp_ref, employee_name, state, and date. I have put a few debug lines in to try and catch the error but makes no difference.

Thanks all again

[Stewart Ross]

I ran a test based on adapting your lines 9-12 to a calendar table of mine with no problems at all in setting the recordset.

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    strSQL = "SELECT fiscal_qtr " & _
              "FROM Calendar " & _
              "GROUP BY fiscal_qtr " & _
              "HAVING fiscal_qtr <> 'N/A';"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

As this assigned the recordset as expected and with no error I cannot understand what could be going wrong. As has been mentioned, we'd need to see what strSQL contains. If you debug.print it you can then copy the SQL and paste it into the SQL view of the query editor. If the same SQL runs from there I'm at a loss to suggest what else could be going wrong.

@Twinnyfo the DAO qualification applied in particular to Office 2003 versions of Access, where ADO was for a time the default type of recordset instead of DAO. From Office 2007 onwards this was reversed back to DAO being the default, and indeed ADO types were superseded by .NET versions that have no relevance to Access.

The ADO/DAO difference when not explicitly declared caused endless trouble at the time. As the OP did not tell us (nor did we ask) what version of Access was in use I have stuck with a practice of qualification of the type of recordset that at least ensures that it is indeed the right type of recordset that has been declared.

-Stewart

[twinnyfo]

@Stewart

Thanks for the info on the history of the ADO/DAO issue. That helps me to understand some of the problems that could arise. I appreciate your sharing this background for us!