2

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?

Improve this question
3
  • 1
    Just to clarify: The frm_selectDesigner form is open while you try to run the query from the other form, right? Commented Apr 26, 2013 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. Commented Apr 26, 2013 at 15:50
  • [Project Request Log TABLE].PercentComplete <> 1 Commented Apr 26, 2013 at 16:34

4 Answers 4

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
...
Improve this answer

2 Comments

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.
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!
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.