Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-11-2018, 08:57 AM   #1
donsi
Newly Registered User
 
Join Date: Sep 2016
Posts: 62
Thanks: 40
Thanked 1 Time in 1 Post
donsi is on a distinguished road
Run-time Error '3061' Too Few Parameters. Expected 6

Hello,

I built the update qry which has criteria and update To fields set from the form which I am attempting to execute via VBA. When I run the VBA it throws a runtime error 3061. Not sure where the mistake is. I checked to see if there was a typo anywhere, but unable to find it. Please let me know how can it be fixed.

SQL of the Qry
Code:
UPDATE TblUsers SET TblUsers.EmpName = [Forms]![frmAddEditEmployee]![txtEmpName], TblUsers.LicNo = [Forms]![frmAddEditEmployee]![txtLicNo], TblUsers.UserName = [Forms]![frmAddEditEmployee]![txtUserName], TblUsers.AccessLevel = [Forms]![frmAddEditEmployee]![CboAccess], TblUsers.Deactive = [Forms]![frmAddEditEmployee]![chkDeActive]
WHERE (((TblUsers.ID)=[Forms]![frmAddEditEmployee]![txtPin]));
VBA
Code:
Private Sub cmdSave_Click()
Dim db As DAO.Database

Set db = CurrentDb

db.Execute "QryUpdateUser", dbFailOnError
    db.Close

Set db = Nothing

donsi is offline   Reply With Quote
Old 10-11-2018, 09:25 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time Error '3061' Too Few Parameters. Expected 6

The Execute method can't resolve form references. Try

DoCmd.OpenQuery "QryUpdateUser"

instead, or create and execute the SQL in VBA, concatenating in the form values.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
donsi (10-11-2018)
Old 10-11-2018, 10:05 AM   #3
donsi
Newly Registered User
 
Join Date: Sep 2016
Posts: 62
Thanks: 40
Thanked 1 Time in 1 Post
donsi is on a distinguished road
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Quote:
Originally Posted by pbaldy View Post
The Execute method can't resolve form references. Try

DoCmd.OpenQuery "QryUpdateUser"

instead, or create and execute the SQL in VBA, concatenating in the form values.
I would like to run the query without popping up messages so took the execute method. Now building SQL is beyond my current skill sets with VBA. Is there anyway you can lend a hand or point me to some resources?

donsi is offline   Reply With Quote
Old 10-11-2018, 10:20 AM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,911
Thanks: 74
Thanked 404 Times in 365 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Okay, first, go into your query and create actual parameters, using your form references as the names.

then try this:

Code:
Private Sub cmdSave_Click()

Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

        Set qdf = CurrentDb.QueryDefs("qryUpdateUser")
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        
        qdf.Execute dbSeeChanges + dbFailOnError
        
    End If

End Sub
You probably won't actually need dbSeeChanges unless you're not using an Access back-end.

Also, I'd strongly advise against closing the current database object, as that can have tragic consequences. If you assign CurrentDb to a variable, all you need to do is set it to nothing. While the rule is 'close what you open', note that you're not actually opening anything by assigning CurrentDb to db.

Anyway, this approach allows you to run a query where you have direct form references as parameters. If you have regular parameters as well, then you'll need to put in logic to tell the system which is which, but that's not the case here. And it allows you to avoid concatenating strings and then running them as queries (which, by the way, creates a small amount of bloat, too).
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
donsi (10-11-2018)
Old 10-11-2018, 10:25 AM   #5
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Concatenate literal text with variable references. Use apostrophe delimiters for text data, # for date type, nothing for numeric. Yes/No field is a number type. If code is behind the form with the referenced controls, can use Me. shorthand.
Code:
With Me
CurrentDb.Execute = "UPDATE TblUsers SET " & _
"EmpName = '" & .[txtEmpName] & "', " & _
"LicNo = '" & .[txtLicNo] & "', " & _
"UserName = '" & .[txtUserName] & "', " & _
"AccessLevel = '" & .[CboAccess] & "', " & _
"Deactive = " & .[chkDeActive] & _
" WHERE ID=" & .[txtPin] & ";"
End With
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-11-2018 at 11:13 AM.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
donsi (10-11-2018)
Old 10-11-2018, 10:29 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,911
Thanks: 74
Thanked 404 Times in 365 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Or just use parameters and use saved, compiled queries. :-)
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
donsi (10-11-2018)
Old 10-11-2018, 10:54 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,279
Thanks: 62
Thanked 1,145 Times in 1,046 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Just by way of clarification, to amplify pbaldy's comment:

CurrentDB.Execute takes the named query string AS-IS and passes it to the DB Engine (probably ACE unless you have a version of Access from before 2007 or were using an external active SQL back end).

DoCmd.OpenQuery parses the query string in Access GUI context to evaluate any functions or references, THEN passes it to the DB Engine. Which means that the DoCmd version can "see" open forms in order to evaluate form references.

ACE, however, CANNOT see the Access GUI context and so cannot evaluate form references. That is because ACE is a separate (child) process of Access. Being a separate child process means that ACE has its own private memory that is different from, and therefore non-overlapping, with the Access GUI. Other than any inter-task communication mechanisms, the two processes literally cannot see each other.

This isolation is actually a requirement imposed by the U.S. Government because without that process separation, Windows wouldn't have been compliant with the "Orange Book" security standards specifications for computer systems. A different and more stringent standard is now in place, but the C2 standard from the Orange Book was the one that forced Win07 and WinME to be rewritten to provide for "hard" task isolation.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
donsi (10-11-2018)
Old 10-11-2018, 11:14 AM   #8
donsi
Newly Registered User
 
Join Date: Sep 2016
Posts: 62
Thanks: 40
Thanked 1 Time in 1 Post
donsi is on a distinguished road
Re: Run-time Error '3061' Too Few Parameters. Expected 6

Quote:
Originally Posted by Frothingslosh View Post
Okay, first, go into your query and create actual parameters, using your form references as the names.

then try this:

Code:
Private Sub cmdSave_Click()

Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

        Set qdf = CurrentDb.QueryDefs("qryUpdateUser")
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        
        qdf.Execute dbSeeChanges + dbFailOnError
        
    End If

End Sub
You probably won't actually need dbSeeChanges unless you're not using an Access back-end.

Also, I'd strongly advise against closing the current database object, as that can have tragic consequences. If you assign CurrentDb to a variable, all you need to do is set it to nothing. While the rule is 'close what you open', note that you're not actually opening anything by assigning CurrentDb to db.

Anyway, this approach allows you to run a query where you have direct form references as parameters. If you have regular parameters as well, then you'll need to put in logic to tell the system which is which, but that's not the case here. And it allows you to avoid concatenating strings and then running them as queries (which, by the way, creates a small amount of bloat, too).
I had parameter set in the query so just needed the code to run the query without messages. You solution worked great. Thanks for the help.

donsi is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Run-time error '3061' to few parameters. expected 1 fritz.panganiban General 3 08-24-2016 05:12 AM
Run Time Error 3061 Too Few Parameters Expected 1 atrium Modules & VBA 3 02-09-2016 09:50 PM
Run time error 3061. Too few parameters. Expected 2 solty89 Modules & VBA 3 10-23-2013 04:00 AM
Run-time error 3061 - Too few parameters. Expected 1 ria.arora Queries 6 12-23-2012 08:47 AM
Run time error 3061 too few parameters. expected 1 pengiliverpool VB.NET 0 08-05-2010 05:16 AM




All times are GMT -8. The time now is 11:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World