Solved

currentdb.execute won't run query

Posted on 2000-04-27
8
290 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:clipster
8 Comments
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2755644
I believe you need syntax like this:

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

Expert Comment

by:simonbennett
ID: 2755652
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
0
 
LVL 2

Expert Comment

by:Jshaw
ID: 2755690
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


 
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:clipster
ID: 2755695
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
0
 
LVL 3

Accepted Solution

by:
MikeRenz earned 50 total points
ID: 2755742
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.
0
 

Author Comment

by:clipster
ID: 2755784
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
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2755803
you could:

dim qdf as querydef

    Set qdf = CurrentDb.QueryDefs("qupdnotctmpafter")
   
    qdf.Parameters![PinTag]="'" &  getusername() & "'"
    qdf.execute
0
 

Author Comment

by:clipster
ID: 2755876
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question