Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

currentdb.execute won't run query

Posted on 2000-04-27
8
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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