?
Solved

VBA coding error expecting Select, update, delete, procedure

Posted on 2009-02-09
2
Medium Priority
?
214 Views
Last Modified: 2013-11-27
I am trying to run a set of code to send an email out via VBA. I am opening a recordset to a query and then pulling fields to fill out the email. However when I try and run the code I am getting the following error.

Run-time error '-2147217900 (80040e14)';

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

The query is pulling paramaters from the open form.

When testing I use a value from the database and hard code the dynamic form values. And this works great. I am confused. Can anyone help me with this?
>>>VBA<<<
Dim rs As New ADODB.Recordset
Dim strquery as String
 
strquery = "q_tt_fss_issue_open_vba"
 
rs.Open strquery, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
>>>Query<<<
SELECT tbl_tt_issues.recid, tbl_tt_issues.entryby, tbl_tt_issues.entrydt, tbl_tt_issues.systemid, tbl_tt_issues.ticketinitator, tbl_tt_issues.system, tbl_tt_issues.region, tbl_tt_issues.status, tbl_tt_issues.startdt, tbl_tt_issues.enddt, tbl_tt_issues.closeddt, tbl_tt_issues.issuedescription, tbl_tt_issues.issueresolution, tbl_tt_issues.resolvedby, tbl_tt_issues.mdn, tbl_tt_issues.min, tbl_tt_issues.homesid, tbl_tt_issues.carrier, tbl_tt_issues.roamsid, tbl_tt_issues.billingsystem, tbl_tt_issues.startdtofcalls, tbl_tt_issues.casestartdt, tbl_tt_issues.akeyprogistr_eguide, tbl_tt_issues.authenstatuschecktool, tbl_tt_issues.validssd_cloned, tbl_tt_issues.mtas_mtaspal, tbl_tt_issues.authenfailurerecording, tbl_tt_issues.allowservice_delauthen, tbl_tt_issues.dualnamissue, tbl_tt_issues.cellsite_networkproblem, tbl_tt_issues.dollarvalue, tbl_tt_issues.hotline_disconnectedusagecontinues, tbl_tt_issues.previousmin_esnusagecontinues, tbl_tt_issues.minnotinbilling, tbl_tt_issues.minwithmultipleesnshowingcalls, tbl_tt_issues.resellername, tbl_tt_issues.acctmanagername, tbl_tt_issues.acctmanageremailaddress, tbl_tt_issues.prioritylevel, tbl_tt_issues.resellersemailaddress, tbl_tt_issues.documentpath, tbl_tt_issues.closedcategory, tbl_tt_issues.approved, tbl_tt_issues.approvedby, tbl_tt_issues.approveddt, tbl_tt_issues.newrecord, tbl_tt_issues.systemissue
FROM tbl_tt_issues
WHERE (((tbl_tt_issues.recid)=[Forms]![f_tt_entry]![TroubleTicket]))
ORDER BY tbl_tt_issues.recid DESC;
 
>>>Query that works<<<
SELECT tbl_tt_issues.recid, tbl_tt_issues.entryby, tbl_tt_issues.entrydt, tbl_tt_issues.systemid, tbl_tt_issues.ticketinitator, tbl_tt_issues.system, tbl_tt_issues.region, tbl_tt_issues.status, tbl_tt_issues.startdt, tbl_tt_issues.enddt, tbl_tt_issues.closeddt, tbl_tt_issues.issuedescription, tbl_tt_issues.issueresolution, tbl_tt_issues.resolvedby, tbl_tt_issues.mdn, tbl_tt_issues.min, tbl_tt_issues.homesid, tbl_tt_issues.carrier, tbl_tt_issues.roamsid, tbl_tt_issues.billingsystem, tbl_tt_issues.startdtofcalls, tbl_tt_issues.casestartdt, tbl_tt_issues.akeyprogistr_eguide, tbl_tt_issues.authenstatuschecktool, tbl_tt_issues.validssd_cloned, tbl_tt_issues.mtas_mtaspal, tbl_tt_issues.authenfailurerecording, tbl_tt_issues.allowservice_delauthen, tbl_tt_issues.dualnamissue, tbl_tt_issues.cellsite_networkproblem, tbl_tt_issues.dollarvalue, tbl_tt_issues.hotline_disconnectedusagecontinues, tbl_tt_issues.previousmin_esnusagecontinues, tbl_tt_issues.minnotinbilling, tbl_tt_issues.minwithmultipleesnshowingcalls, tbl_tt_issues.resellername, tbl_tt_issues.acctmanagername, tbl_tt_issues.acctmanageremailaddress, tbl_tt_issues.prioritylevel, tbl_tt_issues.resellersemailaddress, tbl_tt_issues.documentpath, tbl_tt_issues.closedcategory, tbl_tt_issues.approved, tbl_tt_issues.approvedby, tbl_tt_issues.approveddt, tbl_tt_issues.newrecord, tbl_tt_issues.systemissue
FROM tbl_tt_issues
WHERE (((tbl_tt_issues.recid)=5417) AND ((tbl_tt_issues.systemid)=4));

Open in new window

0
Comment
Question by:woodje
2 Comments
 
LVL 16

Accepted Solution

by:
kmslogic earned 2000 total points
ID: 23596213
When you are testing the hard coded version and it works are you still calling a saved Access query (q_tt_fss_issue_open_vba)?  Or are you putting the actual SQL statement directly into strquery?

I'd recommend putting the sql statement directly in strQuery like this:

The important line is:

            "WHERE tbl_tt_issues.recid=" & [Forms]![f_tt_entry]![TroubleTicket] & " " & _

strQuery = "SELECT tbl_tt_issues.recid, tbl_tt_issues.entryby, tbl_tt_issues.entrydt, " & _
			"tbl_tt_issues.systemid, tbl_tt_issues.ticketinitator, tbl_tt_issues.system, " & _
			"tbl_tt_issues.region, tbl_tt_issues.status, tbl_tt_issues.startdt, tbl_tt_issues.enddt, " & _
			"tbl_tt_issues.closeddt, tbl_tt_issues.issuedescription, tbl_tt_issues.issueresolution, " & _
			"tbl_tt_issues.resolvedby, tbl_tt_issues.mdn, tbl_tt_issues.min, tbl_tt_issues.homesid, " & _
			"tbl_tt_issues.carrier, tbl_tt_issues.roamsid, tbl_tt_issues.billingsystem, " & _
			"tbl_tt_issues.startdtofcalls, tbl_tt_issues.casestartdt, tbl_tt_issues.akeyprogistr_eguide, " & _
			"tbl_tt_issues.authenstatuschecktool, tbl_tt_issues.validssd_cloned, tbl_tt_issues.mtas_mtaspal, " & _
			"tbl_tt_issues.authenfailurerecording, tbl_tt_issues.allowservice_delauthen, tbl_tt_issues.dualnamissue, " & _
			"tbl_tt_issues.cellsite_networkproblem, tbl_tt_issues.dollarvalue, tbl_tt_issues.hotline_disconnectedusagecontinues, " & _
			"tbl_tt_issues.previousmin_esnusagecontinues, tbl_tt_issues.minnotinbilling, tbl_tt_issues.minwithmultipleesnshowingcalls, " & _
			"tbl_tt_issues.resellername, tbl_tt_issues.acctmanagername, tbl_tt_issues.acctmanageremailaddress, tbl_tt_issues.prioritylevel, " & _
			"tbl_tt_issues.resellersemailaddress, tbl_tt_issues.documentpath, tbl_tt_issues.closedcategory, tbl_tt_issues.approved, " & _
			"tbl_tt_issues.approvedby, tbl_tt_issues.approveddt, tbl_tt_issues.newrecord, tbl_tt_issues.systemissue " & _
		"FROM tbl_tt_issues " & _
		"WHERE tbl_tt_issues.recid=" & [Forms]![f_tt_entry]![TroubleTicket] & " " & _
		"ORDER BY tbl_tt_issues.recid DESC;"

Open in new window

0
 

Author Closing Comment

by:woodje
ID: 31544729
This worked. Why would coding the sql directly work and calling a query would not?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 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