INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Error When Running A Macro

Error When Running A Macro

(OP)
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.




RE: Error When Running A Macro

(OP)
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.

RE: Error When Running A Macro

(OP)
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?

RE: Error When Running A Macro

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:
Dim sSQL As String
sSQL = "
Debug.Print sSQL
 
...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
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)

RE: Error When Running A Macro

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

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

RE: Error When Running A Macro

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
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)

RE: Error When Running A Macro

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

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

RE: Error When Running A Macro

THAT is not what I instructed you to do.

CODE

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

SELECT Dept, SomeFieldName FROM SomeTableName ORDER BY Dept
 

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
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

RE: Error When Running A Macro

Yes

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
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?


RE: Error When Running A Macro

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
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.

RE: Error When Running A Macro

This code segment...

CODE

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
I will give this another shot tomorrow. I appreciate the help.

RE: Error When Running A Macro

(OP)
Taking a step back was useful. Today, I re-read our conversation from the top, and I believe I finally understood what you were saying in your post on Sep 16 14:40

After I made the edits that you had outlined in that post, below is what happened when I ran the macro:

This run time error appeared


This was the debug screen. The text in the immediate window reads: SELECT Dept, P0917 FROM SalesF ORDER by Dept


Here is the SalesF table. The Dept values range from p0107 to p0717

Am I interpreting the Immediate window results: SELECT Dept, P0917 FROM SalesF ORDER by Dept, to mean that the macro is looking for a Dept value of p0917 that is not there?

RE: Error When Running A Macro

P0917 is a Field name not a Dept.

Where is field P0917 in your design view?

BTW, your table design seems to be terrible! You ought never to have field names that ate data values as P0917 seems to be along with all those other field names beginning with P. Uuuuuugly! Horrible to analyze.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
Below is what the top half of the table looks like in Design View. I added P0817 and P0917 to the table after I took the earlier screen shot in the post above. Once P0917 was added, the macro then ran successfully. Thanks for all your help. Sorry I misread the directions that you gave very early on in this thread. It would have saved a lot of time and effort. And again, I really appreciate you sharing your time and expertise.

RE: Error When Running A Macro

Why do you have all those field names that look as if they are date related? You're really shooting yourself in the foot with that kind of design. Maybe you aren't the one who did design it. But that design makes analysis and reporting very VERY clumsy.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error When Running A Macro

(OP)
The design was not mine. I am not a programmer/developer. These Access tables do read and send data from/to other non-Access databases. The developers of the Access tables and related applications, who are no longer with the company, may have been mirroring the table design of the other non-Access tables. It was mentioned that the Access tables would need some date and week record maintenance to keep them in sync with the non-Access tables, but the SalesF table was never mentioned as one of those tables. Since P0917 is what the macro was expecting to find, I went ahead and added it, and it seemed to resolve the problem.

At some point something will come up that will require an Access developer, hopefully my company will have someone on board with those skills by then.

RE: Error When Running A Macro

Okay. Good luck. Glad you got this sorted out.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close