Access 2003 VBA: query works when run directly, but run-time error 3061 when run from code?

Question

So I have a relatively simple query that has exactly two parameters, one of which pulls a long from a form and selects only records from a single table, where one field has that value. (It's a table of design projects and the user is selecting a designer whose projects should be listed.)

If I open the form and then manually open the query it works perfectly. If I have a second form (which populates a listbox with the query results) try to set a recordset equal to the query results, it fails with "Run-time error '3061'. Too few parameters. Expected 1."

If I set the parameter to a static integer, e.g. 3, it works fine (but is clearly useless). Why would my VBA code be unable to read text from a text field on a form, when Access itself clearly can?

Here is my query:

SELECT [Project Request Log TABLE].Designer1, [Project Request Log TABLE].Priority, [Project Request Log TABLE].ProjectName, [Project Request Log TABLE].Manager, [Project Request Log TABLE].SME1, [Project Request Log TABLE].Priority, [Project Request Log TABLE].ProjectID
FROM Designers INNER JOIN [Project Request Log TABLE] ON Designers.ID = [Project Request Log TABLE].Designer1
WHERE ((([Project Request Log TABLE].Designer1)=[Forms]![frm_selectDesigner]![txtDesignerId]) AND (([Project Request Log TABLE].PercentComplete)<>1))
ORDER BY [Project Request Log TABLE].Designer1, [Project Request Log TABLE].Priority;

Here is the line of VBA that gives the error:

  Set rst_projects = dbs.OpenRecordset("qryDesignerProjectPrioritySet", dbOpenDynaset)

Thanks.

Edit: the form on which one selects a designer opens the second form, on which the above code attempts to open a recordset. The original frm_selectDesigner is not closed, it is hidden when one clicks OK, but remains open.

Edit 2: If I include the line

DoCmd.OpenQuery "qryDesignerProjectPrioritySet"

The query opens and has the right results. If the very next line tries to assign the results of that query as a recordset as above, it gives the 3601 error? There must be some sort of error in how I wrote the OpenRecordset command, right?

Just to clarify: The `frm_selectDesigner` form *is* open while you try to run the query from the other form, right? — Gord Thompson, Apr 26 '13 at 15:37
*Why would my VBA code be unable to read text from a text field on a form, when Access itself clearly can?* Probably because VBA is used in contexts other than Access. — Conrad Frix, Apr 26 '13 at 15:50

score 1 · Accepted Answer · edited May 23 '17 at 12:14

That OpenRecordset() should be a simple basic operation; I can't understand why it's failing when DoCmd.OpenQuery "qryDesignerProjectPrioritySet" works. See what happens with a minimal procedure which does only enough to attempt OpenRecordset().

Insert the following code as a new standard module, and run Debug->Compile from the VB Editor's main menu. Assuming it compiles without error, test the sub with the frm_selectDesigner form open in form view. If it doesn't compile, you likely need to add a reference for DAO or ACEDAO.

Option Compare Database
Option Explicit

Public Sub test_OpenRecordset()
Dim dbs As DAO.Database
Dim rst_projects As DAO.Recordset

Set dbs = CurrentDb
Set rst_projects = dbs.OpenRecordset("qryDesignerProjectPrioritySet", dbOpenDynaset)
rst_projects.Close
Set rst_projects = Nothing
Set dbs = Nothing
End Sub

If it compiles and runs without error, compare that code with your failing code to see if you can spot differences such as the way the object variables are declared and assigned.

If that effort doesn't lead to a solution, or if test_OpenRecordset also throws the same error, all I can think to suggest is HOW TO decompile and recompile.

score 0 · Answer 2 · answered Apr 26 '13 at 16:56

0

you can set the parameters in code like this (you'll have to Dim/Set the query, too):

...
Dim prm As DAO.Parameter
Set qdef = db.QueryDefs("qryName")

'Evaluate and set the query's parameters.
For Each prm In qdef.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rs = qdef.OpenRecordset
...

answered Apr 26 '13 at 16:56

wazz

4,953
5
20
34

Execution stops on the line "set qdef ..." with the error, "Run-time error '424: Object required". The code contains the copy-pasted name of the query, which as above works correctly in a DoCmd.OpenQuery. Thanks. – CarlF Apr 26 '13 at 17:58
OK, I fiddled and got the above code to work. Debug.Print reports that the parameter Forms!frm_selectDesigner!txtDesignerID is 3. So why is Access complaining about a missing parameter? I even removed the other parameter from the query entirely to be sure it wasn't that one that was somehow wrong. VBA itself says the parameter is set, then complains that it's missing! – CarlF Apr 26 '13 at 18:39

score 0 · Answer 3 · answered Apr 29 '13 at 14:47

Not a very satisfying answer, I'm afraid. Rather than futz around, I used the SQL code from my query and just substituted in a variable (which is set based on the form line) instead of having VBA try to read the text field. My final code looks like this:

designerToPrioritize = Me.designerList.Column(2, Me.designerList.ListIndex + 1)

    queryText = "SELECT [Project Request Log TABLE].Designer1, [Project Request Log TABLE].Priority, [Project Request Log TABLE].ProjectName, [Project Request Log TABLE].Manager, [Project Request Log TABLE].SME1, [Project Request Log TABLE].Priority, [Project Request Log TABLE].ProjectID"
    queryText = queryText & vbCrLf & "FROM Designers INNER JOIN [Project Request Log TABLE] ON Designers.ID = [Project Request Log TABLE].Designer1"
    queryText = queryText & vbCrLf & "WHERE ((([Project Request Log TABLE].Designer1)=" & **designerToPrioritize** & " AND (([Project Request Log TABLE].PercentComplete)<1)))"
    queryText = queryText & vbCrLf & "ORDER BY [Project Request Log TABLE].Designer1, [Project Request Log TABLE].Priority;"
'That should recreate my original query. Let's see how it works.
        Set rst_projects = dbs.OpenRecordset(queryText, dbOpenDynaset)

This works fine, so I'm not going to put any more effort into figuring out the other way to do it. If nobody posts a brilliant answer in the next few days I'll mark this question answered. Thanks to everyone for the help getting this solved.

score 0 · Answer 4 · edited Oct 19 '15 at 18:10

I managed to bybass Run-time error '3061' by putting query condition into code. SQL for Query1 was like:

SELECT * FROM tbl1 WHERE field1=[Forms]![form1]![txt1]

code:

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Query1")

triggered Run-time error '3061'. I wrote query2 as:

SELECT * FROM tbl1

and changed code into:

dim txt1 as string
txt1=[Forms]![form1]![txt1]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Query2 WHERE field1=" & txt1)

and it worked fine.

Access 2003 VBA: query works when run directly, but run-time error 3061 when run from code?

4 Answers4