Solved

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

Posted on 2009-05-06
11
274 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
ID: 24316349
"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
ID: 24316374
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
ID: 24316382
<<You have SDLC_ID in the SQL statement and SDLC in the procedure.>>
I meant SDLC1
JimD.
0
 
LVL 84
ID: 24316391
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
ID: 24316394
scratch that.  I was looking at it wrong.  LSM is most likely correct that it is percent that is causing the problem.
JimD.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Expert Comment

by:Brian Withun
ID: 24316401
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
ID: 24316420
JD: I think SDLC1 is the variable, and SDLC_ID is the column name.
0
 

Author Comment

by:jamsan92011
ID: 24316823
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
ID: 24316885
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
ID: 24316966
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
ID: 24317057
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

862 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

25 Experts available now in Live!

Get 1:1 Help Now