Link to home
Start Free TrialLog in
Avatar of clipster
clipster

asked on

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.

Thanks.

-Bob
Avatar of MikeRenz
MikeRenz

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...

=getusername()

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

Simon
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!
jshaw


 
Avatar of clipster

ASKER

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.

-Bob
ASKER CERTIFIED SOLUTION
Avatar of MikeRenz
MikeRenz

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

-Bob
you could:

dim qdf as querydef

    Set qdf = CurrentDb.QueryDefs("qupdnotctmpafter")
   
    qdf.Parameters![PinTag]="'" &  getusername() & "'"
    qdf.execute
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?

-Bob