Error 3061: Too Few Parameters. Expected 2

Ally

Ally

Registered User.
Local time
Today, 20:05
Joined
Sep 18, 2001
Messages
617
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:

Code: 
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?
 
Last edited:
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.

Code: 
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:

Code: 
Dim x As String, y As String, z As String
 
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.
 
I'm going to try and recreate the error.
 
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.
 

Attachments

Here is a DAO example of how to pass parameters when you execute a query in code. I don't know how to do this with ADO but ADO does not have a parameters collection so the method would be different.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryQCMainSizeCheck
qd.Parameters![YourParm1] = "SomeValue"
qd.Parameters![YourParm2] = "SomeValue"
Set rst = qd.OpenRecordset

If you are only going to execute this query from code I would change the parm names which I gather are now in the form -
Forms!YourFormName!YourControl. You would replace [YourParm1] with whatever the form field reference is -
qd.Parameters![Forms]![YourForm]![YourControl] = [Forms]![YourForm]![YourControl]
 
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
 
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?
 
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.
Click to expand...

Can anyone help please?
 
UnitNo does not belong in three tables. It should only be in tblAdminDischData. tblACPData and PtCoExistProb are both related to tblAdminDischData by EpisodeID. You can obtain UnitNo by joining to tblAdminDischData when you need it. By storing it in three places, you run the risk of it's being different in those places.

I am lost as to what the current problem is though. If you run a parameter query from VBA, you MUST supply parameters as in the code I posted. It doesn't matter if the parameter is referencing a form field. You STILL need to populate the parameters as I showed.
 
Thanks - am off work at the moment, but when I get back I'll have another look.
 
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.
 
Code: 
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.
 
Isn't it
qd.Parameters![Forms]![frmACPData]![UnitNo] = [Forms]![frmACPData]![UnitNo]
 
It comes up with the same error.
 
I feel that I've got the code in completely the wrong place. Should it be on load or something of the form?
 
You must log in or register to reply here.

Similar threads

C
Solved Too few parameters
Replies
11
Views
429
CJ_London
C
D
Password update form now has errors
Replies
1
Views
172
Gasman
Gasman
B
Outlook - Filtered Unread, Server duplicate emails & think Unread = False not work so errors
Replies
6
Views
437
bignose2
B
K
Solved Pass Parameter from Form to Report
Replies
16
Views
654
arnelgp
arnelgp
C
Getting "The error 3061, Too few paramaters. Expected 6"
2
Replies
29
Views
2,194
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom