maknit
asked on
Runtime error '3061': Too few parameters. Expected 4.
Hello - I'm migrating some VBA code from using docmd.openquery to db.execute. I got this code from an example on the web:
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "COM_CREATE_COMMISSIONS", dbFailOnError
db.Execute "COM_CREATE_COMMISSIONS_GM ", dbFailOnError
db.Execute "COM_CREATE_COMMISSIONS_RE V", dbFailOnError
The three queries (in quotes) are append queries, and when running I get the error above "Runtime error 3061': Too few parameters. Expected 4. I have tried quite a few permutations but this error seems to be the closest to actually running the code, so this is the example I'm posting hear. The three queries run fine (and ran fine using the docmd.openquery code), and they are in an Access 2013 database. The code did have some problems before I referenced the "Microsoft Office 15.0 Access database engine Object Library" and "Microsoft Data Access Components Installed Version", which seemed to fix that problem.
In summary, I'm just looking for the complete syntax for using the db.execute statement to run three already defined Access 2013 queries.
Note - this is my workaround for suppressing Action Query messages, since it's the only published method to do so when using SharePoint Lists as the data source (which is the case for the queries in this case). The Action Query confirmations remained even though they were turned off in Client Options, the autoexec macro, and even in the code. So a solution to completely turn off these confirmations (when using SharePoint Lists) during an action query would also be equally effective for me, as I can simply use the docmd.openquery syntax.
Thanks!
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "COM_CREATE_COMMISSIONS", dbFailOnError
db.Execute "COM_CREATE_COMMISSIONS_GM
db.Execute "COM_CREATE_COMMISSIONS_RE
The three queries (in quotes) are append queries, and when running I get the error above "Runtime error 3061': Too few parameters. Expected 4. I have tried quite a few permutations but this error seems to be the closest to actually running the code, so this is the example I'm posting hear. The three queries run fine (and ran fine using the docmd.openquery code), and they are in an Access 2013 database. The code did have some problems before I referenced the "Microsoft Office 15.0 Access database engine Object Library" and "Microsoft Data Access Components Installed Version", which seemed to fix that problem.
In summary, I'm just looking for the complete syntax for using the db.execute statement to run three already defined Access 2013 queries.
Note - this is my workaround for suppressing Action Query messages, since it's the only published method to do so when using SharePoint Lists as the data source (which is the case for the queries in this case). The Action Query confirmations remained even though they were turned off in Client Options, the autoexec macro, and even in the code. So a solution to completely turn off these confirmations (when using SharePoint Lists) during an action query would also be equally effective for me, as I can simply use the docmd.openquery syntax.
Thanks!
If all your parameters are control references, you can also do a loop on the parameters collection and use Eval() to resolve the reference:
Jim.
Dim prm as Parameter
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Jim.
ASKER
Hello - and thanks to both posts - very helpful. I have the exact code pasted below. I started seeing my app error out all over the place, so I had to uncheck the references - Microsoft Office 15.0 Access database engine Object Library and Microsoft Data Access Components Installed Version. That got rid of all the problems, but now the code below causes the error - "Compile error: User-defined type not defined". Any help would be great - and I've tried to enable the reference Microsoft DAO 3.6 Object Library but get a "Error in Loading DLL". I'm thinking that it doesn't apply to Access 2013, as I've validated the DLL and registered it multiple times to no avail.
Code:
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS ")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
qd.Parameters(1) = [Forms]![frm_Main]![cmb_Cu rrent_Year ].Value
qd.Execute , dbFailOnError
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS _GM")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
qd.Execute , dbFailOnError
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS _REV")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
qd.Execute , dbFailOnError
MsgBox "Commission transactions created"
Code:
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
qd.Parameters(1) = [Forms]![frm_Main]![cmb_Cu
qd.Execute , dbFailOnError
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
qd.Execute , dbFailOnError
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
qd.Execute , dbFailOnError
MsgBox "Commission transactions created"
You need this reference:
Microsoft Office 15.0 Access database engine Object Library
/gustav
Microsoft Office 15.0 Access database engine Object Library
/gustav
ASKER
Thank you - got past the references error - now I think it's a syntax issue, but I can't seem to get past it. The following statement errors "Run-time error '3061': Too few parameters. Expected 4" :
qd.Execute (dbFailOnError)
For some reason there aren't too many clear depictions of what the syntax should be. If I can get a syntax example, hopefully this should do it - thanks again.
qd.Execute (dbFailOnError)
For some reason there aren't too many clear depictions of what the syntax should be. If I can get a syntax example, hopefully this should do it - thanks again.
It isn't a syntax question. It means that either one or more fields are misspelled or that some parameters haven't been assigned values.
It's easy to debug:
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS ")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
qd.Parameters(1) = [Forms]![frm_Main]![cmb_Cu rrent_Year ].Value
Debug.Print "SQL:", qd.SQL
qd.Execute , dbFailOnError
Now study the SQL string and correct until success.
/gustav
It's easy to debug:
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
qd.Parameters(1) = [Forms]![frm_Main]![cmb_Cu
Debug.Print "SQL:", qd.SQL
qd.Execute , dbFailOnError
Now study the SQL string and correct until success.
/gustav
ASKER
Thanks Gustav - I missed the dependent queries. I need to look at it deeper, but I think the parameters in the dependent queries make up the 4 in the error. It sounds like using the Execute method requires all query parameters, in all levels of the queries, to be explicitly called out. I'll give that a try.
Thanks,
Mike
Thanks,
Mike
Yes, that's a trap. The parameters must be defined on the lowest level as they are "carried forward" to overlying query.
/gustav
/gustav
ASKER
Hello - sorry for the long response time - I've been debugging and testing, and thank you again for the help. I've been able to get 2 out of the 3 queries working. The query that still doesn't seem to work is the first one - COM_CREATE_COMMISSIONS. I've tried all kinds of permutations of parameters, query defs, etc., but it's not appending a record that should be added (It's an append query). Also, when I debug and check the SQL, it's running as expected, and when I copy that SQL and run separately, it works. It only doesn't work when in this DAO format. I'm thinking it might be the fact that the parameter [Forms]![frm_Commissions_W orkbench]! [txt_INV_C OM_YEAR] occurs two times (in two of the underlying queries), and its only in the procedure once. I tried adding another parameter so that there are two [Forms]![frm_Commissions_W orkbench]! [txt_INV_C OM_YEAR] parameters , but it fails. It doesn't fail with just one, but it doesn't add the record. So I'm thinking it's relating to the parameters and possibly that one of them occurs more than once, but is specified only once. And again, the other two queries (with one parameter) work fine. Any ideas? The code follows:
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS ")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
qd.Parameters(1) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_C OM_YEAR].V alue
qd.Parameters(2) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_DATE].V alue
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS _GM")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Set qd = db.QueryDefs("COM_CREATE_C OMMISSIONS _REV")
qd.Parameters(0) = [Forms]![frm_Commissions_W orkbench]! [txt_INV_A PP_ID].Val ue
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS"
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS_GM "
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS_RE V"
MsgBox "Commission transactions created"
Thanks!
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
qd.Parameters(1) = [Forms]![frm_Commissions_W
qd.Parameters(2) = [Forms]![frm_Commissions_W
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = [Forms]![frm_Commissions_W
Debug.Print "SQL:", qd.SQL
qd.Execute (dbFailOnError)
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS"
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS_GM
Rem DoCmd.OpenQuery "COM_CREATE_COMMISSIONS_RE
MsgBox "Commission transactions created"
Thanks!
What does Debug.Print "SQL:", qd.SQL return?
Try forcing a date value here:
qd.Parameters(2) = DateValue([Forms]![frm_Com missions_W orkbench]! [txt_INV_A PP_DATE].V alue)
> I'm thinking it might be the fact that the parameter
> [Forms]![frm_Commissions_W orkbench]! [txt_INV_C OM_YEAR] occurs two times
Did you specify it as a parameter in both queries?
/gustav
Try forcing a date value here:
qd.Parameters(2) = DateValue([Forms]![frm_Com
> I'm thinking it might be the fact that the parameter
> [Forms]![frm_Commissions_W
Did you specify it as a parameter in both queries?
/gustav
ASKER
Hi Gustav,
I'll give the DateValue a try.
All these form controls are doing is functioning as query criteria. I haven't defined them as parameters. The other two that work don't have them defined as parameters, just criteria. I'll get back to you on the DateValue asap.
I'll give the DateValue a try.
All these form controls are doing is functioning as query criteria. I haven't defined them as parameters. The other two that work don't have them defined as parameters, just criteria. I'll get back to you on the DateValue asap.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok - thanks - I'll give that a try as well.
ASKER
Thanks, Gustav - entering them as parameters resolved it. All queries are working as intended. Thanks again for all of your help!
You are welcome!
/gustav
/gustav
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qd = db.QueryDefs("COM_CREATE_C
qd.Parameters(0) = Forms!frmYourForm!txtSomeT
qd.Parameters(1) = Forms!frmYourForm!txtSomeT
qd.Parameters(2) = Forms!frmYourForm!txtSomeT
qd.Parameters(3) = Forms!frmYourForm!txtSomeT
qd.Execute([Options])
Or by name:
qd.Parameters("Forms!frmYo
/gustav