Error When Running A Macro 1

[kc27]

When I run a macro on an Access database (using Access 2013), I get the following error:

Run-time error '3061'
Too few parameters. Expected 1.

It then highlights the line of code shown in this link.

Further complicating this issue is that I am not a programmer, and am not familiar with the tables or data contained therein. The macro is supposed to be run on a weekly basis. One week ago, the macro ran without failing. This week it failed. From the way the error reads, it seems as though the the syntax in the macro is not correct. The macro has not been edited since the time is successfully ran. How could it work one week, and then fail the following week?

Any advice on how to troubleshoot this would be appreciated.

 

[SkipVought]

HI,

Need to know exactly what VALUES are in the variables in that highlighted statement when you DeBug on that error. FAQ707-4594

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Thanks for the advice. I will have to give it another try tomorrow. Didn't get anywhere with it today. Access and VBA are all new to me.

 

[kc27]

OK, I tried the watch window, and it is probably due to user error, but I did not get anywhere.

Is the error message "Run-time error '3061' Too few parameters. Expected 1" and indication that the external data type has changed?

 

[SkipVought]

That particular error has nothing to do with VBA. That is an error in your SQL statement text that you are constructing using VBA. It indicates that a Field Name in your SQL is incorrect.

Prior to the statement that is highlighted, insert this:
[pre]
Dim sSQL As String
sSQL = "
Debug.Print sSQL
[/pre]
...and then COPY from OpenRecordset: "Select Dept," to "Order by Depy" and PASTE after sSQL =

Then REPLACE the part you COPIED in the OpenRecordset with sSQL.

So what you end up with is a new variable, sSQL that contains the SQL statement that 1) will be printed in the Immediate Window and 2) will be executed by your OpenRecordset statement. So when the code errors, you will have the exact SQL that it choked on. COPY that SQL code from the Immediate Window, Go to the Access GUI, Open the SQL window, PASTE the SQL in and RUN. It should give you the same error. Figure out how to FIX IT. Then apply that knowledge to FIX your VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Thanks Skip

Knowing that a field name is incorrect is very helpful. Very basic question: Is the database that the SQL querying DB_RPCOMM? My role in this process was to run the macro, I'm just trying to sort out why its failing after working prior to this, and I am doing it with zero previous Access usage or any programming skills, so I really appreciate the information you supplied. Would I be correct in assuming that the field name Dept from the statement below has changed?

Set RSD = DBR.OpenRecordset("SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept", dbOpenDynaset)

 

[SkipVought]

1) Your VBA appears to be opening the database, since the previous statement executes without error. That is the db you need to manually OPEN and manually RUN the SQL string that errors in your VBA to determine what is wrong.

2) it is possible that SOME name is incorrect in the SQL that is being supplied in the OpenRecordset method when you get that error.

This will take looking at the name of the Database you have manually opened, the Table and the Field names in that table and comparing the values that you have in that copied SQL.

Of course you can post back any time to get help interpreting what you discover.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I located the RPCOMM database. The field "Dept" is in multiple tables in that database, but in all cases it is named Dept.

 

[SkipVought]

BUT...you also have Table Names. They ALL must be correct. Did you take the entire SQL string and run it in the appropriate db/table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

No, I only reviewed the field names. If I am going to use the SQL string, do I only use this portion?

SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept

Or does it have to be the entire line from the macro?

Set RSD = DBR.OpenRecordset("SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept", dbOpenDynaset)

 

[SkipVought]

Dim sSQL as String

sSQL = "SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept"

Debug.Print sSQL

Set RSD = DBR.OpenRecordset(sSQL, dbOpenDynaset)

When it errors, 1) go to the Immediate Window, 2) COPY the string starting with SELECT and ending with ORDER BY Dept, 3) open the database, GUI for simple Select statements and PASTE into the SQL window and RUN.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Am I doing this correctly? This is what I see when I paste the statement into a SQL query and run it.

 

[SkipVought]

THAT is not what I instructed you to do.

Dim sSQL As String

sSQL = "SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept"

Debug.Print sSQL

...this
...results in something like this (of course your results will be significantly different than mine) in the Immediate Window...

[pre]
SELECT Dept, SomeFieldName FROM SomeTableName ORDER BY Dept
[/pre]

Take THIS and paste it into the SQL Editor in the GUI.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Thanks again for the help. Does this sound correct for creating the query?


1. Click "Create"
2. Click "Query Design"
3. Close the "Show Table" window
4. Click "Design" tab
5. Click "SQL View" button
6. Query window opens with "SELECT;" (without the quotes) at the top left of the window
7. Paste code
8. Click Run

 

[SkipVought]

Yes

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

OK, thanks for your patience with me on this.

The first time I did this, at step 7, I pasted

"SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept"

into the query window. I deleted the SELECT; that was in the window by default.


Trying this again,if I am reading your instructions correctly, I pasted the code below into the query window, again removing the SELECT; that was in the window by default

Dim sSQL As String

sSQL = "SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept"

Debug.Print sSQL

When I do that and click run, I get the message below. Am I still not following your directions correctly?

 

[SkipVought]

Did you COPY the string from the Immediate Window? I think NOT!

That string from the Immediate Window, NOT YOUR VBA CODE, is what need to be run!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Again, thanks for the help. When the macro fails, the debug window highlights the following line:

Set RSD = DBR.OpenRecordset("SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept", dbOpenDynaset)

I thought you had identified this as the string I needed to paste into s SQL query:

"SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept" ?

If that is the string, then yes, I did paste into a blank query window and click run. The results of running that query were in my post at 21 Sep 16 18:08.

 

[SkipVought]

This code segment...

"SELECT Dept, " & dbField & " FROM " & rTable & " ORDER BY Dept" ?

...means ABSOLUTELY NOTHING in the SQL window!!!

What we're trying to discover is what value is in dbField and rTable so that the resolved statement WILL BE MEANINGFUL IN THE SQL WINDOW AND WILL RUN, in order to discover why it errors in the Set RSD.... statement.

Hence the...

Debug.Print sSQL

...statement that PRINTS the resolved SQL in the Immediate Window that you REFUSE to OPEN and COPY from.

In the VB Editor, hit the View menu item to find the Immediate Window.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I will give this another shot tomorrow. I appreciate the help.