Solved

Syntax, currentdb.Execute

Posted on 2010-11-24
24
705 Views
Last Modified: 2012-05-10
Experts, I have a syntax below that I only get if I insert the row of
>  CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
If I remove the row then there is no syntax.  
I think I should also setwarnings to prevent the window from coming up.  
Do you see some kind of syntax here:
Private Sub Amount_AfterUpdate()
         
   Dim strSQL As String
 
  DoCmd.RunCommand acCmdSaveRecord
  CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
  'testing the qry bc get error if record is new
 
  strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & "," & Me!EndUserID & "," & Me!Amount & ")"

  Debug.Print strSQL
 
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

    MsgBox "Dont forget to double click this field and enter in the reason why the LC Amount changed.", vbInformation

    If IsNull(Me!Currency) Then
        MsgBox "Dont forget to enter the Currency", vbInformation
    End If
   
End Sub
0
Comment
Question by:pdvsa
  • 11
  • 8
  • 2
  • +3
24 Comments
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
I assume that you are referring to an error that you are getting? What is the error?
0
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError

is wrong, as you need a SQL string instead;

example
CurrentDb.Execute "Select X from tblTest", dbFailOnError
0
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
The sql however needs to be an "Action" query, my entry above was for illustration only!
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 100 total points
Comment Utility
I'm not sure I understand the problem.  Are you getting a syntax error?  If so, what is the error number and message.

If you are running an action query (update, insert, ...) then I prefer to use the Execute method, with the dbFailOnError parameter rather than the RunSQL method.  With RunSQL, you have to disable/enable the Warnings, but with the execute method, you don't.  You only need to make sure that you add the dbFailOnError parameter and have error handling code that will properly handle the approprate error.

Try defining the a database object.

Dim db as DAO.Database
Set db = Currentdb

Then, instead of using Currentdb in your code, use the db object

db.Execute "qryUpdateEUNameMS", dbFailOnError

dbExecute strSQL, dbFailOnError

0
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
fyed;

Dim db as DAO.Database
Set db = Currentdb
db.Execute strSQL, dbFailOnError
set db = Nothing

is exactly the same as;

Currentdb.Execute strSQL, dbFailOnError

but with the later, you don't need to declare
0
 

Author Comment

by:pdvsa
Comment Utility
OK I looked a little deeper into this and i get teh syntax becuase the [EndUserID] is null.  The qry is an updateqry that will update the EndUserID and my thinking is that it is executed before the Insert Into and therefore the EndUserID will no longer be null and prevent the error.  
error:  3134, Syntax Insert into statement

what do you think now?
0
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & ",'" & Me!EndUserID & "'," & Me!Amount & ")"

User the above if the EndUserID is alphanumeric as you need to wrap alphanumeric within quotes, note the single quotes;

",'" & Me!EndUserID & "',"
0
 

Author Comment

by:pdvsa
Comment Utility
Ok that eliminated the error but that update qry does not seem to be executing before the Insert.  Do you see somethign there?  If I click on the qry and run it that way then it does update and I get the warnings "you are about to update",,,etc... I dont get the warnings the way teh update qry is incorporated into the code.  

what do you think?
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
you can execute a query in db.execute i do it all the time but it has to be an action query
0
 

Author Comment

by:pdvsa
Comment Utility
I would think it is an action qry?  
0
 

Author Comment

by:pdvsa
Comment Utility
maybe I can run the qry some other way besides dbexecute.  I dont know.  I just need it to run somehow.
0
 
LVL 11

Expert Comment

by:Runrigger
Comment Utility
I wonder if the Database.Execute bypasses the Warnings?

Perhaps fyed knows?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pdvsa
Comment Utility
It might not bypass the warnings....It is not being executed at all for certain.  Jmoss says it has to be an action qry but I dont understand this.  
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
Comment Utility
I see there is some confusion here on the Execute Method.

The Execute Method in DAO runs an 'action query (append, delete, make table, update) OR and SQL statement that translates to an 'action query'.  

So, you can have:

CurrentDb.Execute "SomeSavedActionQueryName", dbFailOnError

or

Dim sSQL As String
'SQL for an Append 'action query'
sSQL = "INSERT INTO Table1 SELECT MyTable.* FROM MyTable"

CurrentDb.Execute sSQL, dbFailOnError

You cannot specify a SELECT query for the argument of the Execute Method.  Action queries do not return records. A SELECT query does.

Lets talk about SetWarnings .... = Bad Idea >> can have many undesirable ramifications.  

The problem with SetWarnings False is ... if one (or more) of the queries fail for *whatever* unexpected reason ... you will never know this because SetWarnings False masks out *all* errors, which can lead to you *thinking* that everything ran ok, when in fact it did not.

The approach below has two advantages:

1) You do not get the warning prompts - just like if you use SetWarnings False ... AND ...
2) If an Error DOES occur, it will be trapped and you can act accordingly.

Private Sub btnRunQry_Click()
   
    On Error GoTo btnRunQry_Click_Error
    CurrentDb.Execute "SomeActionQuery1", dbFailOnError
    CurrentDb.Execute "SomeActionQuery2", dbFailOnError
    MsgBox "Operation completed successfully!"

btnRunQry_Click_Exit:
    Err.Clear
    Exit Sub

btnRunQry_Click_Error:
   MsgBox "An error occurred:" & vbCrLf & Err.Number & vbCrLf & Err.Description
   GoTo btnRunQry_Click_Exit
End Sub

******

Another problem with SetWarnings False is ... if you *forget* to execute SetWarnings True ... OR ... some other error does occur and SetWarnings True does not get execute ... you have a big problem ... because, False stays in effect unit you close and reopen Access.  

So, for example if you are working in design view of say a form (or table, etc) ... and you make some changes ... and hit Save ... you are NOT prompted for 'Do you want to save these changes ...". I will just silently save the changes ... which may NOT be what you wanted to do!!


mx

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Minor typo above:

"OR and SQL statement that translates to an 'action query'.  "
>> OR an SQL statement that translates to an 'action query'.  
           ^^

mx
0
 

Author Comment

by:pdvsa
Comment Utility
Mx... Good stuff to know.   Will test it and lyk
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
Comment Utility
Just to mention that I, for one, aren't too sure where you're currently at.
Is the problem in the statement you execute in strSQL?  Or qryUpdateEUNameMS?
If the latter then surely you'd have shown the SQL definition of that query by now.
So if the former, what is the data type of EndUserID?  Is it Text as suggested?  (Such apparently foreign key values are text less often than they are numeric by practice.)

To allow for a Null in the numeric version (or a text version which doesn't allow zero length strings - which is a good practice IMO)

strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & "," & Nz("'" + Me!EndUserID + "'", "Null") & "," & Me!Amount & ")"

An Immediate If is an alternative to Nz - but just makes the code a tiny bit longer to post ;-)
e.g.
.... Me!letterofcreditID & "," & IIF(IsNull(Me!EndUserID),  "Null", "'" + Me!EndUserID + "'") & "," & Me!Amount & ")"

Quotes left for convenience as they'd be coerced for numeric inserts and appropriate for text types. (You should really aim for the appropriate type though - so if it was numeric then)
.... Me!letterofcreditID & "," & IIF(IsNull(Me!EndUserID),  "Null", Me!EndUserID) & "," & Me!Amount & ")"
or
.... Me!letterofcreditID & "," & Nz(Me!EndUserID,  "Null") & "," & Me!Amount & ")"

Cheers.
0
 

Author Comment

by:pdvsa
Comment Utility
I am pretty sure enduserid is a number.  I have it as a cbo w a row source qry.  The issue is not w qry
0
 

Author Comment

by:pdvsa
Comment Utility
The qry I have is:
UPDATE Projects INNER JOIN tblLetterOfCredit ON Projects.ID=tblLetterOfCredit.ProjectID SET tblLetterOfCredit.EndUserID = [projects].[EndUserID];

Is it an action query?
This can  be used in:
 CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError

==>It still is not working in the code but it does work if I click on a button (dont want a button has to be auto)

REgarding the Insert:  This one worked from RunRigger
(Notice the wrapping on EndUserID. )
 strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & ",'" & Me!EndUserID & "'," & Me!Amount & ")"                                                                                                    '^^^^^^^^^^^^^^^^^^^

But this one gave me Datatype mismatch from LPurvis and highlighted the entire Insert Into row:
  'strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & "," & Nz("'" + Me!EndUserID + "'", "Null") & "," & Me!Amount & ")"                                                                        '^^^^^^^^^^^^^^^^^^^^
 
Notice the wrapping on EndUserID.  
EndUserID is Number format in the table tblLCAmountHistory , text box display control (it is not a combo box with a row source qry as I had said in previous post)

Why would I get the datatype mismatch?  
0
 

Author Comment

by:pdvsa
Comment Utility
I have the InsertInto working
I now need that qry to work in the code

 
Private Sub Amount_AfterUpdate()
          
   Dim strSQL As String
  
  DoCmd.RunCommand acCmdSaveRecord
  CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
  'testing the qry bc get error if record is new
  
  'strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & "," & Me!EndUserID & "," & Me!Amount & ")"
  strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & ",'" & Me!EndUserID & "'," & Me!Amount & ")"
  'strSQL = "INSERT INTO tblLCAmountHistory (fldDate, letterofcreditID, EndUserID, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!letterofcreditID & "," & Nz("'" + Me!EndUserID + "'", "Null") & "," & Me!Amount & ")"
  'LPurvis 11/24/10, error
  
  Debug.Print strSQL
  
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

    MsgBox "Dont forget to double click this field and enter in the reason why the LC Amount changed.", vbInformation


    If IsNull(Me!Currency) Then
        MsgBox "Dont forget to enter the Currency", vbInformation
    End If
    
End Sub

Open in new window

0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 200 total points
Comment Utility
pdvsa,

Just to confirm that it is this which is not working;

CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError

and the SQl of qryUpdateEUNameMS is;

UPDATE Projects INNER JOIN tblLetterOfCredit ON Projects.ID=tblLetterOfCredit.ProjectID SET tblLetterOfCredit.EndUserID = [projects].[EndUserID];

Can you have a look at the tables "tblLetterOfCredit" and "Projects" and advise the Data Type of the field "EndUserID " in both tables please

Thanks
Dave
0
 

Author Comment

by:pdvsa
Comment Utility
Hi Dave, yes the stmnt above is correct. The qry is not being activated.  

The format of EndUserID is number in both table.  One is a text box and the other is a combobox with a row soirce qry.  

Let me know what you think now...
0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 200 total points
Comment Utility
I think that I see what is wrong now?

This section;

UPDATE Projects

Indicates that you are trying to update the table "Projects"

This however is contradictory to the "SET" section which reads;

SET tblLetterOfCredit.EndUserID = [projects].[EndUserID];

So, your SQL statement should either be;

UPDATE Projects INNER JOIN tblLetterOfCredit ON Projects.ID=tblLetterOfCredit.ProjectID SET Projects.EndUserID = [tblLetterOfCredit].[EndUserID];

or

UPDATE tblLetterOfCredit INNER JOIN tblLetterOfCredit ON tblLetterOfCredit.ProjectID =Projects.ID SET tblLetterOfCredit.EndUserID = [Projects].[EndUserID];

Try that.
0
 

Author Comment

by:pdvsa
Comment Utility
Runrigger, I think I have found a solution.  The qry was running OK but I could nto get it to fire in the AfterUpdate code.  But now I dont need it at all.  What I did was drew a relationship between the tblProjects and tblLetterOfCredit and linked the Master and Child form on EndUserID and now the EndUserID will populate.  

I think I am good now and will divy up pts.  thank you...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now