Run-time Error '3061' Too Few Parameters. Expected 6 (1 Viewer)

donsi

Registered User.
Local time
Today, 13:43
Joined
Sep 1, 2016
Messages
73
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:43
Joined
Aug 30, 2003
Messages
36,118
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.
 

donsi

Registered User.
Local time
Today, 13:43
Joined
Sep 1, 2016
Messages
73
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?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 16:43
Joined
Oct 17, 2012
Messages
3,276
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).
 

June7

AWF VIP
Local time
Today, 12:43
Joined
Mar 9, 2014
Messages
5,423
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
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 16:43
Joined
Oct 17, 2012
Messages
3,276
Or just use parameters and use saved, compiled queries. :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:43
Joined
Feb 28, 2001
Messages
27,001
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.
 

donsi

Registered User.
Local time
Today, 13:43
Joined
Sep 1, 2016
Messages
73
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.
 

Users who are viewing this thread

Top Bottom