Too few parameters in OpenRecordset code

Question

I have two sets of code, that are the same I just change variables to another set that exist and now with the ones I changed I get an error saying "Run-time error '3061': Too few parameters. Expected 6."

This is the changed code:

  Dim rec As Recordset
    Dim db As Database
    Dim X As Variant
    Set db = CurrentDb
    Set rec = db.OpenRecordset("UnitMoreInfoQ")
    Const msgTitle As String = "Open Explorer"
    Const cExplorerPath As String = "C:\WINDOWS\EXPLORER.EXE"
    Const cExplorerSwitches As String = " /n,/e"
    cFilePath = rec("ProjFilePath")

It highlights this line:

Set rec = db.OpenRecordset("UnitMoreInfoQ")

This is the first code:

Dim rec As Recordset
Dim db As Database
Dim X As Variant
Set db = CurrentDb
Set rec = db.OpenRecordset("ProjectMoreInfoQ")
Const msgTitle As String = "Open Explorer"
Const cExplorerPath As String = "C:\WINDOWS\EXPLORER.EXE"
Const cExplorerSwitches As String = " /n,/e"
cFilePath = rec("ProjFilePath")

As you can see, the line has the same amount of parameters:

Set rec = db.OpenRecordset("ProjectMoreInfoQ")

This has gotten me quite confused for awhile because of this. How do I fix this error?

It's a query, that is started when you open the form the button that this code is in is on. This query has a column named "ProjFilePath". This column has the file location such as C:\Test — D347HxD
– D347HxD, Commented Nov 27, 2013 at 13:56
According to when I right click and go to parameters, unless that is not how you find out if it has parameters... no. — D347HxD
– D347HxD, Commented Nov 27, 2013 at 14:02

HansUp · Accepted Answer · 2013-11-27 18:48:32Z

2

I didn't get the same result as you when testing your db, and I still don't understand the difference. However, maybe we can still get you something which works in spite of my confusion.

The query contains 6 references to form controls, such as [Forms]![WorkOrderDatabaseF]![Text71]. Although you're certain that form is open in Form View when you hit the "too few parameters" error at db.OpenRecordset("UnitMoreInfoQ"), Access doesn't retrieve the values and expects you to supply them.

So revise the code to supply those parameter values.

Dim rec As DAO.Recordset
Dim db As DAO.database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim X As Variant
Set db = CurrentDb
'Set rec = db.OpenRecordset("UnitMoreInfoQ")
Set qdf = db.QueryDefs("UnitMoreInfoQ")
For Each prm In qdf.Parameters
    prm.value = Eval(prm.Name)
Next
Set rec = qdf.OpenRecordset(dbOpenDynaset) ' adjust options as needed

I'm leaving the remainder of this original answer below in case it may be useful for anyone else trying to work through a similar problem. But my best guess is this code change will get you what you want, and it should work if that form is open in Form View.

Run this statement in the Immediate window. (You can use Ctrl+g to open the Immediate window.)

DoCmd.OpenQuery "UnitMoreInfoQ"

When Access opens the query, it will ask you to supply a value for the first parameter it identifies. The name of that parameter is included in the parameter input dialog. It will ask for values for each of the parameters.

Compare those "parameter names" to your query's SQL. Generally something is misspelled.

Using the copy of your db, DoCmd.OpenQuery("UnitMoreInfoQ") asks me for 6 parameters.

Here is what I see in the Immediate window:

? CurrentDb.QueryDefs("UnitMoreInfoQ").Parameters.Count
 6 

for each prm in CurrentDb.QueryDefs("UnitMoreInfoQ").Parameters : _
? prm.name : next
[Forms]![WorkOrderDatabaseF]![Text71]
[Forms]![WorkOrderDatabaseF]![ClientNameTxt]
[Forms]![WorkOrderDatabaseF]![WorkOrderNumberTxt]
[Forms]![WorkOrderDatabaseF]![TrakwareNumberTxt]
[Forms]![WorkOrderDatabaseF]![WorkOrderCompleteChkBx]
[Forms]![WorkOrderDatabaseF]![WorkOrderDueDateTxt]

Make sure there is a form named WorkOrderDatabaseF open in Form View when you run this code:

Set rec = db.OpenRecordset("UnitMoreInfoQ")

edited Nov 27, 2013 at 18:48

answered Nov 27, 2013 at 14:08

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

10 Comments

D347HxD Over a year ago

I hope you mean put it in the button's code, as I put that code in the very beginning of the button's code and added a message box after to confirm it runs past that line. It ran past it, and stopped at the same place giving the same error. The query runs fine, with Access asking for no parameters.

HansUp Over a year ago

Something is fishy here. Restart Access, then use Ctrl+g to open the Immediate window and try DoCmd.OpenQuery "UnitMoreInfoQ" Does it then ask for parameters?

D347HxD Over a year ago

It does not ask for them.

HansUp Over a year ago

Interesting. Opening the query alone behaves differently than when opened from your VBA code. Perhaps the VBA code is corrupt. I suggest you create and test a simple procedure which attempts only the OpenRecordset. And if that one still expects parameters, I would like to see of copy of your db. ;-)

D347HxD Over a year ago

Yup, still asked for parameters even with only OpenRecordSet. My database is using Access2013, so if you have a copy that runs these versions of accdb files, then I can upload it to mediafire. Just confirm you can run newer files.

|

Steve Salowitz · Accepted Answer · 2013-11-27 14:08:21Z

1

Does the [UnitMoreInfoQ] query execute properly on its own? If you mistype a field in access it will treat that field as a parameter.

answered Nov 27, 2013 at 14:08

Steve Salowitz

1,3551 gold badge15 silver badges30 bronze badges

1 Comment

D347HxD Over a year ago

It does indeed, I use it to supply information onto a form's text boxes (same form that has the button). So the source of the form is UnitMoreInfoQ and it runs with no problems when the form opens; all text box data is filled out correctly.

dazedandconfused · Accepted Answer · 2013-11-27 13:58:18Z

0

ProjectMoreInfoQ and UnitMoreInfoQ are different queries... it sounds like one takes 6 parameters and the other doesn't. Look at the queries in Access and see if either have parameters defined.

answered Nov 27, 2013 at 13:58

dazedandconfused

3,2162 gold badges20 silver badges30 bronze badges

2 Comments

D347HxD Over a year ago

It says it has 0 parameters when I go to Query Design mode and right click then go to parameters.

D347HxD Over a year ago

Same thing, no parameters.

Collectives™ on Stack Overflow

Too few parameters in OpenRecordset code

3 Answers 3

10 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related