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

[Gareth Jones]

Hi all,

I have a function in order to amend some data in a table.

Basically I have a list of names and a state column with T or F in it. I have sorted by name and the last state for each name should be F. The function groups the names first in a recordset, and then if the state for the record above is the same (as in 2 consecutive T's) it should delete it. Otherwise, it should exit the function.

Here is the function so far

Public Function UpdateTable()
    On Error GoTo EH:
        Dim db As Database
        Dim rstNames As Recordset
        Dim rstCallOuts As Recordset
        Dim strSQL As String
        Dim dtmFirstCallout As Date
        Set db = CurrentDb()
        strSQL = "SELECT name1 " & _
            "FROM table1 " & _
            "GROUP BY name1 " & _
            "HAVING name1 <> 'N/A';"
        Set rstNames = db.OpenRecordset(strSQL, dbOpenDynaset)
        If Not rstNames.RecordCount = 0 Then
            rstNames.MoveFirst
            Do While Not rstNames.EOF
                strSQL = "SELECT ID, name1, state1, time1 " & _
                    "FROM table1 " & _
                    "WHERE name1 = '" & rstNames!Name1 & "' " & _
                    "ORDER BY TimeStamp;"
                Set rstCallOuts = db.OpenRecordset(strSQL, dbOpenDynaset)
                If Not rstCallOuts.RecordCount = 0 Then
                    rstCallOuts.MoveFirst
                    dtmFirstCallout = rstCallOuts!TimeStamp
                    rstCallOuts.MoveNext
                    Do While Not rstCallOuts.EOF
                        If (DLookup("[state1]", "table2", "[ID]=" & "[ID]" - 1) <> "[state1]") Then
                        Exit Function
                        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
 

I have tried a number of different things however none seem to be working. A few different sites have similar problems and enclosing the WHERE statement in ' ' helps however it was already done.

Any ideas anyone?

Thanks in advance.

Gareth

[twinnyfo]

Gareth,

I think what you need to do is establish another variable to hold the values you are trying to evaluate in your If...Then in Line 27. Otherwise, your code will never evaluate as true (as it is written).

Instead of setting a varaible for the TimeStamp (which you never use, have a variable for State1. Then compare the next record's value for State1 with that variable.

I think that should get you moving in the right direction.

[Gareth Jones]

Thanks for replying. Am I right in saying I need to replace timestamp in line 24 with state1 so that it reads

 dtmFirstCallout = rstCallOuts!State1 

and replace line 27 so that it reads

  If (DLookup("[state1]", "table2", "[ID]=" & "[ID]" - 1) <> dtmFirstCallout) Then   

I have tried this and I get the same error unfortunately :(

Thanks

[twinnyfo]

Well, there are several issues with your code (particularly line 27).

First, I don't know what type of data "State1" is. Let's assume an integer for the following re-do of your code (but please modify as required for your actual data):

Public Function UpdateTable() 
    On Error GoTo EH: 
        Dim db As Database 
        Dim rstNames As Recordset 
        Dim rstCallOuts As Recordset 
        Dim strSQL As String 
        Dim intState1 As Integer 
        Set db = CurrentDb() 
        strSQL = "SELECT name1 " & _ 
            "FROM table1 " & _ 
            "GROUP BY name1 " & _ 
            "HAVING name1 <> 'N/A';" 
        Set rstNames = db.OpenRecordset(strSQL, dbOpenDynaset) 
        If Not rstNames.RecordCount = 0 Then 
            rstNames.MoveFirst 
            Do While Not rstNames.EOF 
                strSQL = "SELECT ID, name1, state1, time1 " & _ 
                    "FROM table1 " & _ 
                    "WHERE name1 = '" & rstNames!Name1 & "' " & _ 
                    "ORDER BY TimeStamp;" 
                Set rstCallOuts = db.OpenRecordset(strSQL, dbOpenDynaset) 
                If Not rstCallOuts.RecordCount = 0 Then 
                    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

However, there will still be problems with this code, as Your line 28 assumes only one iteration, but it appears from your origianl post that you want to cycle through all records. Note the replacement of line 28 above, which then re-assignes the value of intState1 (which would then assume the deletion of line 28)

Again, there may be additional challenges with this code, as what if there are three consecutive records that have hte sate State1?

Without additional information on everything your Tables have in them and how this is intended to be used, this is about all I can offer at this time.

I hope this helps (and I hope the code isn't fat-fingered with syntax errors).

[Gareth Jones]

I have tried the code above however I am still getting the same error that there were too few parameters :(

The background is the table records the times someone is called out. The state column contains the letter T or F. T indicates when they were called out (the date column records the time) F indicates the end of the call out in the next record. I need to calculate the duration between each T and F. It would be plain sailing if each of the records were in sequential order in that it was like T,F,T,F,T,F,T,F etc however occasionally its is recorded incorrectly and its T,T,F. I need to remove any instances where there is 2 or more T's together so that there is only the 1st T and the F of that particular call out.

I can do that easily in a query using:
IIf(DLookUp("[state1]","table2","[ID]=" & [ID]-1)=[state1],"N/A",[state1])

However that only works if the previous person ended on a F, otherwise if the F was not recorded for that person it takes the T for the previous person and the next F from the subsequent person.

Hope this makes sense :) I have tried subqueries and this function as I believe I need to remove the additional T's for each person individually to prevent the issue above.

Thanks :)

[twinnyfo]

In addition to correcting the current data, may I also recommend coding the prevention of such errors....

It is difficult to understand the entire scope of your DB from your description, but have you considered the value of having a field representing the "T" and a separate field in the same record representing the "F"? This would make each "callout" a single record--thus preventing the need for a separate record for each instatnce.

This of course, assumes that every "T" has corresponding "F", and thaaat the information unique to each "T" is also relevant (and unchanged) to the corresponding "F". I hope this made sense. It made sense when I initially started typing it.....

[twinnyfo]

BUT, this still does not get your code executing properly. At which line of the code are you getting that error? This may help us to pinpoint how to correct the initial problem and work forward from there!

We want to be able to automate your corrections!

[Gareth Jones]

I appreciate your help so far. I have stepped through all the code and and it doesnt fail until the function is finished. I did notice however when stepping through, on line 21 it says rstcallouts = nothing when I hover over it, and line 22 says rstcallouts.recordcount = <object variable or with block variable not set>

It appears to me that the recordset has nothing in it to work with at the moment. All the lines seem ok and populated with data until line 21.

[twinnyfo]

Gareth,

Keep in mind that when your debugger is currently ON line 21, that rstCallOuts has NOT been set. The debugger pauses prior to executing the line.

I am certain that when the debugger pauses on line 22, that rstCallOuts will no longer be empty. Likewise, on line 23, RecordCount will now give you a value.

What is strange, however, is that your code does not break until after the function finishes???

This--at least on the surface--indicates there is an error somewhere else in your code (but not in this function). Have you continued to step through your code line by line (F8) to ensure the error is firing on this procedure?

[Gareth Jones]

I have done some more testing and those 2 lines still say the same after passing past them. It jumps straight from line 21 to 42. It then gives the error, and after clicking ok, it goes to line 43 and then starts again. This is the only code and only function in the DB. It is based on a simple table and not a query. Once it works, I can copy the function to the real DB. I may end up having to delete the records I don't need manually however its not really practical once the amount of records gets high :)

[twinnyfo]

Try adding between Lines 20 and 21:

Debug.Print strSQL

Then paste the result that shows up in the Immediate Window.

[Stewart Ross]

Two comments on what I see here. Firstly, as a possible explanation for jumping out of your record set with a too few parameters error you need to qualify all recordset definitions as DAO types. For example,

Dim rstNames as DAO.Recordset

ADO recordset definitions are likely to be expected if you do not qualify the type, and crucially their argument lists are not the same as the DAO calls, which is the most likely explanation for the parameter error and the jump to the error handler without executing the set recordset statement.

My second comment is that your relational design is not right in this example. You are trying to recover call out states with no details of the call out itself recorded. You are looping manually and deleting states to make up for the missing relationship. You will never get this to work effectively.

You have also got a problem with choice of primary key for your person table - names are not unique and cannot be used as keys in this way. My strong advice is to read up on relational table design, as your missing design relations are the real problem here. If you had a call out entity defined you would be able to identify the specific callouts attended by each person then group on the states for each person without code at all.

-Stewart

Ps in case you think it a bit strong to mention the lack of relational design, what you have mentioned before about two consecutive T states and having to delete one and so on is indicating the same thing. Ignoring the internal use of your time stamp field, which as Twinnyfo indicated is not intrinsic to your application, and your ID field, which is not a candidate key in the way it is being used, you are allowing duplicate rows to be stored for each person, in violation of relational requirements that rows must be unique.

[Gareth Jones]

I have tried inserting the debug line between 20 and 21 however its still jumping to the end and erroring. I tried making it a DAO recordset as well however its still doing the same. I believe Stewart has hit the nail on the head in that I should be able to do this with a subquery however I couldnt get the syntax right. The ID is the primary key in the table and each person has a unique ref number too which I can group on. I will have another go at that today :)

Appreciate all your help. I will report back if I get any success :)

Thanks

[twinnyfo]

So, the generation of the SQL string in lines 17-20 is causing the error? That almost seems impossible, because if there was a problem with rstNames (or if it was empty) then those lines would not execute in the first place.

This one is starting to not make any sense at all.

[twinnyfo]

@Stewart,

I have never explicitly declared my Recordsets as DAO recordsets and have never run across any ambiguity issues. I think there may be some situations in which it can cause problems, but I have not run across any. In this case, there may be some issues, but as I consider the error being generated, I wonder if we both are looking in the wrong place....

@Gareth,

In the Field "Name1" are these pure text (alpha-numeric) fields ONLY? By this, I mean are there any entries in the "Name1" field which include either dashes or apostrophes? I had a query once that was trying to look for names and it worked perfectly for years until I entered a name that had an apostrophe--then it broke and gave me (I think) the same error.

Please review your data. I agree with Stewart, however, that your data is not properly normalized. You should never search on a "Name," but on the index which points to that name. Lack of proper normalization may be the primary cause in this instance.

[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!