Syntax, currentdb.Execute

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
pdvsaProject financeAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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
 
RunriggerCommented:
I assume that you are referring to an error that you are getting? What is the error?
0
 
RunriggerCommented:
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError

is wrong, as you need a SQL string instead;

example
CurrentDb.Execute "Select X from tblTest", dbFailOnError
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RunriggerCommented:
The sql however needs to be an "Action" query, my entry above was for illustration only!
0
 
RunriggerCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
RunriggerCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
jmoss111Commented:
you can execute a query in db.execute i do it all the time but it has to be an action query
0
 
pdvsaProject financeAuthor Commented:
I would think it is an action qry?  
0
 
pdvsaProject financeAuthor Commented:
maybe I can run the qry some other way besides dbexecute.  I dont know.  I just need it to run somehow.
0
 
RunriggerCommented:
I wonder if the Database.Execute bypasses the Warnings?

Perhaps fyed knows?
0
 
pdvsaProject financeAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
pdvsaProject financeAuthor Commented:
Mx... Good stuff to know.   Will test it and lyk
0
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
RunriggerConnect With a Mentor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
RunriggerConnect With a Mentor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.