?
Solved

currentdb.execute won't run query

Posted on 2000-04-27
8
Medium Priority
?
302 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

840 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