Solved

Run-time error '3061' Too few parameters. Expected1 using Access VBA

Posted on 2009-05-06
11
273 Views
Last Modified: 2012-05-06
I have a Function which passes in values from a form and then uses them to insert into a table. The table I am inserting into has 4 fields  
Master_Project_ID, SDLC_ID and PERCENT are numeric and Project_ID is a text field.
Function SDLC_Build(Master_Project_ID As Integer, PROJECT_ID, Project_Planning, Requirements, Design, Development, Testing, Implementation As String)
 

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim sSQL As String
 

    Dim SDLC1 As Integer

    SDLC1 = 1

    Dim Percent As Integer

    Percent = 0

    

     

    Set db = DBEngine(0)(0)

    

    If Project_Planning = "Yes" Then

    

    sSQL = "INSERT INTO TBL_SDLC (MASTER_PROJECT_ID,PROJECT_ID,SDLC_ID,[PERCENT]) VALUES ( " & Master_Project_ID & ", " & PROJECT_ID & ", " & SDLC1 & ", " & Percent & ");"

            

         

    db.Execute sSQL

    

    End If

Open in new window

0
Comment
Question by:jamsan92011
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
Comment Utility
"Too few parameters" means you've misspelled something, generally.

However, your "percent" variable is the most likely culprit. "Percent" is a reserved word - change your variable to something like "vPercent", and change your SQL string to reflect this as well.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You have SDLC_ID in the SQL statement and SDLC in the procedure.
Access doesn't know what to use for SDLC_ID
JimD.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<You have SDLC_ID in the SQL statement and SDLC in the procedure.>>
I meant SDLC1
JimD.
0
 
LVL 84
Comment Utility
Also, you function declaration:

Function SDLC_Build(Master_Project_ID As Integer, PROJECT_ID, Project_Planning, Requirements, Design, Development, Testing, Implementation As String)

isn't built using normal programming techniques. Master_Project_ID is declared as an Integer, and Implementation is declared as a String, but your other input parameters are all declared as a variant. this typically won't cause much trouble, but can if you're dealing with Null values (which Variants can handle, but your tables may not be able to). If you intend for your other input parameters to be String, you must declare EACH of those as a String:

Function SDLC_Build(Master_Project_ID As Integer, PROJECT_ID As String, Project_Planning As String etc etc

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
scratch that.  I was looking at it wrong.  LSM is most likely correct that it is percent that is causing the problem.
JimD.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 13

Expert Comment

by:Brian Withun
Comment Utility
Which call is producing this '3061' error?

In your SDLC_Build() function you should be typing each parameter, though that is almost certainly not the cause of your error.


Function SDLC_Build( Master_Project_ID As Integer _

                   , PROJECT_ID As <what?> _

                   , Project_Planning As <what?> _

                   , Requirements As <what?> _

                   , Design As <what?> _

                   , Development As <what?>

                   , Testing As <what?>

                   , Implementation As String )

Open in new window

0
 
LVL 84
Comment Utility
JD: I think SDLC1 is the variable, and SDLC_ID is the column name.
0
 

Author Comment

by:jamsan92011
Comment Utility
I've changed each parameter in the function as recommended and changed the Percent to vPercent.  In my table the Percent field is a Decimal and Field Size double.  Could this be the problem?  Technically I guess I don't even need to put this into my insert statement as the default will always be 0%.  I can try taking the Percent out completely and re-run.
0
 

Author Comment

by:jamsan92011
Comment Utility
I removed the Percent as a variable and from the Insert statement but now I am getting a syntax erro in Insert Into statement.  Attached are my changes.  Could I be missing a " somewhere?
Function SDLC_Build(Master_Project_ID As Integer, PROJECT_ID As String, Project_Planning As String, Requirements As String, Design As String, Development As String, Testing As String, Implementation As String)
 

    Dim db As DAO.Database           ' Current database

    Dim rs As DAO.Recordset

    Dim sSQL As String

    

    Dim SDLC1 As Integer

    SDLC1 = 1

     

    Set db = DBEngine(0)(0)

    

    If Project_Planning = "Yes" Then

    

    sSQL = "INSERT INTO TBL_SDLC (MASTER_PROJECT_ID,PROJECT_ID,SDLC_ID,) VALUES ( " & Master_Project_ID & ", " & PROJECT_ID & ", " & SDLC1 & ");"

            

         

    db.Execute sSQL

    

    End If

Open in new window

0
 
LVL 84
Comment Utility
If PROJECT_ID is a string:

sSQL = "INSERT INTO TBL_SDLC (MASTER_PROJECT_ID,PROJECT_ID,SDLC_ID,) VALUES ( " & Master_Project_ID & ", '" & PROJECT_ID & "', " & SDLC1 & ");"
0
 

Author Comment

by:jamsan92011
Comment Utility
I figure out the issue with my Insert (missing single quote before and after double quotes) and by removing the Percent it worked just fine.  Thank each of you for all the help
    sSQL = "INSERT INTO TBL_SDLC ( MASTER_PROJECT_ID, PROJECT_ID, SDLC_ID)" & _

    "Values ('" & Master_Project_ID & "','" & PROJECT_ID & "','" & SDLC1 & "');"

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now