currentdb.execute won't run query

I have an update query that computes fields from unbound controls on an open form:

UPDATE NoticeTemp SET NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grpDateByOrOn]=1), NoticeTemp.TickleType = [Forms]![frmLtrAppt]![grpInterviewType], NoticeTemp.EncloseForms = ([Forms]![frmLtrAppt]![chkEncloseForms]=-1)
WHERE (((NoticeTemp.PinTag)=getusername()))

The update works fine using docmd.openquery (so long as I setwarnings off), but when I use currentdb.execute I get error #3061 "Too few parameters, expected 3.

Why?  Would it work if I referred to the controls using different syntax?  Does it matter?  Would it be more efficient to update the records in code, by stepping through the recordset?  I usually figure Access's native implementation of a query is more efficient than anything I do, but maybe this is an exception.


Who is Participating?
MikeRenzConnect With a Mentor Commented:
I think it is because the way access is designed.  If you use .execute, then it is straight sql, where access isn't pre-compiling its interpretation of the sql you are passing it.  So Access won't translate form.txtfield  and  functions like getusername().  The same thing how you can't reference global variables from a Macro, but you can reference fields on a form.  There is a heirarchical design that you just get a feel for as you use access.
I believe you need syntax like this:

currentdb.execute "UPDATE NoticeTemp SET NoticeTemp.Appt = (" & [Forms]![frmLtrAppt]![grpDateByOrOn]=1" & "), NoticeTemp.......
Hi clipster,

Have you tried it without the...


criteria? I think you will find that .execute won't allow this becuase it uses the dbenjine direct as it would in something like VB.

Perhaps if you physically create the query and run it by name using .execute it may work.

Good Luck

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Try enclosing your parms in quotes and single quotes like this:

UPDATE NoticeTemp SET NoticeTemp.Appt = & "'"([Forms]![frmLtrAppt]![grpDateByOrOn]=1)& "'", NoticeTemp.TickleType = & "'" [Forms]![frmLtrAppt]![grpInterviewType]& "'", NoticeTemp.EncloseForms = & "'"([Forms]![frmLtrAppt]![chkEncloseForms]=-1& "'")
WHERE (((NoticeTemp.PinTag)= & "'" getusername()& "'"))

This should pass the parms to SQL. They need to be strings if you are passing text.

It looks as though you could just remove you control references where you are explicitly defining the value of the update. i.e. NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grpDateByOrOn]=1)

change it to NoticeTemp.Appt = 1

Good luck!

clipsterAuthor Commented:
Simon, I .execute other queries that include my getusername() function as a criterion, and this query is saved.  I can docmd.openquery "qupdnotctmpafter", but I cannot currentdb.execute "qupdnotctmpafter".

Mike, your syntax (running the SQL instead of the saved query) will undoubtedly work, and might even be the better way to do this, but I'm still curious why I cannot .execute the saved query.

clipsterAuthor Commented:
jshaw, it's not as clear as it could be without seeing the datatypes, but APPT is a logical field, TRUE if (grpByDateOrOn = 1).

Mike, I guess "It's the way Access is designed" is as good an answer as any.  But note that I CAN use getusername(), it's ONLY the Forms!... references that cause .execute to fail.

you could:

dim qdf as querydef

    Set qdf = CurrentDb.QueryDefs("qupdnotctmpafter")
    qdf.Parameters![PinTag]="'" &  getusername() & "'"
clipsterAuthor Commented:
Mike, I don't understand (maybe because I've never used a parameter query).

[PinTag] is the name of a text field in the table.  getusername() returns a string.  The criterion for the query is [PinTag] = getusername()

Was that clear to you when you suggested the above qdf.Parameters code?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.