Run-time error 3061 (1 Viewer)

donsi · Jun 5, 2018

Hi all,

I have saved UpdateQry named qryvoidBlank on my FE to void blank records (not to delete). If I run query directly or via docm.openquery, it runs without any errors and makes necessary updates. However, if I use Currentdb.execute method then it kicks in error 3061 with "Too few parameters. Expected 2."
Now query is executed from the form by btn_click event and two criteria is passed from the form as well (date and employee number). Spends hours looking for solution, but failed so far. here is the code.

Code:

Private Sub Command36_Click()

CurrentDb.Execute "qryvoidBlank", dbFailOnError
end sub

arnelgp · Jun 5, 2018

Can you show the Sql string of the query.

donsi · Jun 5, 2018

arnelgp said:
Can you show the Sql string of the query.

I don't have the SQL string on vba. I created an update query in access and using the query name next to Execute. But here is the SQL from the query.

Code:

UPDATE TblTransactions SET TblTransactions.Void = True, TblTransactions.VoidTimeStamp = Now(), TblTransactions.[Voided by] = "Admin", TblTransactions.Comment = "Blank Record", TblTransactions.BankStatus = "Void"
WHERE (((TblTransactions.Void)=False) AND ((TblTransactions.IssFormNo) Is Null) AND ((TblTransactions.[CashOpsEmpLic#])=[Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8]) AND ((TblTransactions.Status)="Issued") AND ((TblTransactions.GamingDate)=[Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate]));

arnelgp · Jun 5, 2018

If you can't have it run using currentdb, and you said it works, you can try to use DoCmd.RunSql:

DoCmd.SetWarnings False
DoCmd.RunSql Currentdb.Querydefs("qryvoidBlank").SQL
DoCmd.SetWarnings True

Mark_ · Jun 5, 2018

Just to verify, you ARE checking that not only is NavigationForm OPEN, there are also values in both
[Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8]
and
[Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate]

I didn't see error checking for valid values in the button code you posted.

Pat Hartman · Jun 5, 2018

1. When you run a query using DAO you need to specify the parameters prior to the .execute method since "Access" isn't running the query, the database engine is running it and it doesn't have any knowledge of your forms and other objects.

Code:

Dim db As DAO.Database
Dim qd AS DAO.Querydef

Set db = CurrentDB
Set qd = db.Querydefs!qryvoidBlank
    qd.Parameters("[Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate])") = [Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate]
    qd.Parameters("[Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8]") = [Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8]
    qd.Execute dbFailOnError

PS, I would change the query so that instead of referring to the form field, it refers to a parameter. It will make the code a little more understandable since it looks pretty strange to set a parameter named x to x.
PPS, it is always best to give your objects meaningful names rather than allowing Access to use names like Combo8.
2. I never use Navigation forms (I roll my own). Only ONE subform can be loaded on a Navigation form at any one time so this will only work if the specifically referenced subform is the one that is visible.

donsi · Jun 5, 2018

arnelgp said:
If you can't have it run using currentdb, and you said it works, you can try to use DoCmd.RunSql:

DoCmd.SetWarnings False
DoCmd.RunSql Currentdb.Querydefs("qryvoidBlank").SQL
DoCmd.SetWarnings True

I don't want to set warnings to false just in case if it fails, thus used execute.

donsi · Jun 5, 2018

Pat Hartman said:
1. When you run a query using DAO you need to specify the parameters prior to the .execute method since "Access" isn't running the query, the database engine is running it and it doesn't have any knowledge of your forms and other objects.

Code:

Dim db As DAO.Database Dim qd AS DAO.Querydef Set db = CurrentDB Set qd = db.Querydefs!qryvoidBlank qd.Parameters("[Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate])") = [Forms]![NavigationForm]![NavigationSubform].[Form]![TxtGamingdate] qd.Parameters("[Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8]") = [Forms]![NavigationForm]![NavigationSubform].[Form]![Combo8] qd.Execute dbFailOnError

PS, I would change the query so that instead of referring to the form field, it refers to a parameter. It will make the code a little more understandable since it looks pretty strange to set a parameter named x to x.
PPS, it is always best to give your objects meaningful names rather than allowing Access to use names like Combo8.
2. I never use Navigation forms (I roll my own). Only ONE subform can be loaded on a Navigation form at any one time so this will only work if the specifically referenced subform is the one that is visible.

Following your code give me an error message Run-time 3265- "Item not found in this collection" at line 5 where first parameter is set.

The_Doc_Man · Jun 6, 2018

When you get "Item not found" in any variety at all, the first thing you always check is that you spelled it correctly.

arnelgp · Jun 6, 2018

Dont worry about the warning set to false, if it fails it will still show the message.
By the way i run my code a couple of times and it worked without error.

Pat Hartman · Jun 6, 2018

"Item not found in this collection"

Looks like cut and paste left an extra right paren in the string.

Run-time error 3061 (1 Viewer)

donsi

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

donsi

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

donsi

Registered User.

donsi

Registered User.

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread