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?

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

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.

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

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?

Originally posted by Ally
I've attached a copy of the query and the relationships.

To another thread? :p

Oops!

Thanks Mile-O!

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?

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.

We'll need to see how you modified the 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?

Change the parameters in the query. Instead of referencing a form field use a parameter. Therefore


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

becomes:


qd.Parameters![Unit No] = [Forms]![frmACPData]![UnitNo]
qd.Parameters![Episode ID] = [Forms]![frmACPData]![EpisodeID]

But what is the point of opening a recordset and not doing anyghing with it?

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]

You need to change the query to match. Also, you'll need to use something other than the actual column name.

Where UnitNo = [Unit No]


[Unit No] is the parameter name and UnitNo is the column name.

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'."