Error 3061: Too Few Parameters. Expected 2

[Ally]

I have a form, which is opened from another form. When I try doing anything on the second form, I get the error message:

Run-time error ‘3061’:

Too few parameters. Expected 2

The code that is running is:

Private Sub ACPDailyDate_Exit(Cancel As Integer)
    Dim x, y, z As String
    Dim r As Recordset, db As Database
    Dim DocName As String
    Dim LinkCriteria As String
    DocName = "frmACPData"
    Dim intNewRecord As Integer
    intNewRecord = IsNull(Me.ACPDailyID)
    
    Set db = CurrentDb()
    Set r = db.OpenRecordset("qryPtACPDaily")

    'If intNewRecord Then
        Do Until r.EOF
            If r.Fields("acpdailydate") = Me.ACPDailyDate.Value Then
            
            msgbox r.Fields("PtFirstName") & " " & r.Fields
("PtLastName") & " already has ACP data for this date " & _
            Chr(13) & r.Fields("acpdailydate"), vbOKOnly + 
vbExclamation, "Warning"
            
            DoCmd.Close
        
            Exit Sub
        
            ElseIf r.NoMatch Then
          
          End If
        
        r.MoveNext
        
        Loop
        
    'Else
        Exit Sub
        
    'End If
    
    
            Me.Refresh
        
        Exit Sub
End Sub

The line highlighted is: Set r = db.OpenRecordset("qryPtACPDaily")

In the query there are 2 parameters:
[Forms]![frmACPData]![EpisodeID]
[Forms]![frmACPData]![UnitNo]

The query runs fine on its own (when the form’s open).

Does anyone know what’s wrong please?

 

[Mile-O]

It would look like you are opening the query (via DAO) when it requires the parameters to open the query and, if the form is not open, it can't get to them.

Keep the form frmACPData open, or if you don't want to see it set its Visible property to NO (at least that way it is still there and can be referenced.


On another note, even though you didn't use the variables x, y, and z in that routine I just want to highlight something in case you don't know.

Dim x, y, z As String

Will create three variables: x (variant), y (variant) z (string)

Maybe you want the first two as variants but thought I'd say, if you wanted them as strings, it should be:

Dim x As String, y As String, z As String

 

[Ally]

The form is open - just minimized.

Have got rid of Dim x etc as I'd copied the code over from something else and realized I didn't need them.

Am still getting the error.

 

[Mile-O]

I'm going to try and recreate the error.

 

[Mile-O]

OKay, I recreated it and found that I couldn't get it to work either.

So, the easiest way to get it to work was to build the actual query in SQL within the code as a string (strSQL) and then use:

Set r = db.OpenRecordset(strSQL)

It got the parameters that way.

Here's my recreation of it.

 

[chewy]

dont know if this will help you but this happened to me today. Check out this post.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=45587&perpage=15&pagenumber=1

 

[harra]

I have also had this very annoying error message pop up when I'm calling a query from code. I think I have found that it comes up almost 100% of the time if I have a reference to a form's field in the query. For example, if the criteria of the stored query is [Forms]![FormName]![SomeField] (may have syntax wrong ), when I try and use the query in my SQL Code with the CurrentDB.OpenRecordset("QueryName") I get the error Ally is getting.

The only way I have found to get around it and keep my hair in my head is to build the Query in my VBA code as Mile suggested

 

[Ally]

Wow - thanks for all this ... but I'm getting a bit confused.

I've had a go at both Mile-O and Pat's suggestions for starters.

With Mile-O's I can't get the SQL right. Then with Pat's I started putting it on the OnExit of the field that was giving me the error, then realized it would have to go on the OnClick of the open form - then realized that I don't actually want to run just the query, I need to open the form which the query sits behind, which at the moment runs fine - it's just the error happens on exit of one of the fields where I was running some other code - so now I haven't a clue where I'm supposed to run this SQL / other code from.

I now think I know what the problem may be: have attached a copy of the relationships.

tblACPData is supposed to hold UnitNo and in the qryACPData which feeds frmACPData, it displays fine, but when I looked at the table it was blank. It feeds it into the query fine which is why I hadn't noticed it before. Therefore my problem is (yet again) a relationship one I feel. I've attached a copy of the query and the relationships. I tried a cut down version of the db, but even with the bare objects required, it's too big.

I know the answer is probably staring me in the face but why is the UnitNo field blank? And is this the problem that's causing the error?

 

[Mile-O]

I've attached a copy of the query and the relationships.

To another thread?

 

[Ally]

Oops!

Thanks Mile-O!

 

[Ally]

Have played around and the Unit No is now being saved to the table, but am still getting the error.

I've had a go at both Mile-O and Pat's suggestions for starters.

With Mile-O's I can't get the SQL right. Then with Pat's I started putting it on the OnExit of the field that was giving me the error, then realized it would have to go on the OnClick of the open form - then realized that I don't actually want to run just the query, I need to open the form which the query sits behind, which at the moment runs fine - it's just the error happens on exit of one of the fields where I was running some other code - so now I haven't a clue where I'm supposed to run this SQL / other code from.

Can anyone help please?

 

[Ally]

Thanks - am off work at the moment, but when I get back I'll have another look.

 

[Ally]

Sorry to keep dragging this one out. Have made the changes with the UnitNo.

I have tried the parameter code you gave me Pat - I do need to open a form rather than a query, but tried it with just opening the query first and am getting "Item not found in this collection".

Do you know what I'm doing wrong please.

 

[Ally]

Private Sub cmdACPDaily_Click()
On Error GoTo Err_cmdACPDaily_Click

'    Dim stDocName As String
'    Dim stLinkCriteria As String
'    DoCmd.Minimize

'    stDocName = "frmPtACPDaily"
'    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qryPtACPDaily
    qd.Parameters![Forms]![frmACPData]![UnitNo] = UnitNo
    qd.Parameters![Forms]![frmACPData]![EpisodeID] = EpisodeID
    Set rst = qd.OpenRecordset
    
Exit_cmdACPDaily_Click:
    Exit Sub

Err_cmdACPDaily_Click:
    msgbox Err.Description
    Resume Exit_cmdACPDaily_Click
    
End Sub

Even taking out the unnecessary objects, I can't get the db small enough to post here. I am happy to email it if that is easier.

 

[Rich]

Isn't it
qd.Parameters![Forms]![frmACPData]![UnitNo] = [Forms]![frmACPData]![UnitNo]

 

[Ally]

It comes up with the same error.

 

[Ally]

I feel that I've got the code in completely the wrong place. Should it be on load or something of the form?

 

[Ally]

I'm sorry but I don't understand what you mean about opening the recordset and "not doing anything with it"?

I have changed the code but it still comes up with "Item not found in this collection" and it highlights the line when debugged:

qd.Parameters![UnitNo] = [Forms]![frmACPData]![UnitNo]

 

[Ally]

Right ... there's some progress forward but a couple of steps back. I put the code in a separate module and declared the parameters in the query and it has got rid of the error messages I was getting ... BUT

• When I open the first form (from another) I get parameter value boxes appear "Unit No" and "Episode ID".
  
• The first form that I call the second form from, is erroring with a flicker in the calculated field boxes. I put on a command button with the same calculation on a msgbox and got the following message:
  
• The information I enter on the second form does not appear in the first forms listbox.
  
  
  "Run time error '64479': The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Unit No'."

 

[K-CJ]

I had exactly the same problem as Ally. After spending many hours trying most of the suggestions above which created the same results as for Ally, I finally found the solution to my particular problem. So, if anyone else comes across the same problem, I'd like to share the other thing that might be wrong when referencing to text boxes on a form in code.

This is an extract from my code where it went wrong:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryEmailCirculationRecipients")
rs1.Filter = "BPlanApplication.idBplanApplication =" & [Forms]![Form1]![idBplanApplication]
Set rs2 = rs1.OpenRecordset

It didn't matter if I defined my query in a SQL statement in code, or tried defining the paramater with a QueryDef, or adding the WHERE clause in my saved query, I got the various errors as Ally got.

The solution was very simple and the error lied in my query:
In my "qryEmailCirculationRecipients" query I had my unique ID (idBplanApplication) that links my contacts table to my main table added from the table in my subform (BPlanCirculationContacts.idBplanApplication) instead of the ID from my main table (BPlanApplication.idBplanApplication).
I still don't know why this would make a difference, but it solved my problem. Thus, the correct field that the paramater refers to should be present in your query.