VBA: select query error

[DajTwo]

Hi all,

This is my 1st select query in VBA.. The data returned will ALWAYS be only ONE record and the data will be used to populate a new excel spreadsheet generated from an excel template (.xlt).

My problem is that I get the error
Cannot Execute a Select Query.

Can someone point me in the right direction to eliminate this error? Thanks


Dim cmbvalue As String
Dim strSQL As String
Dim db As Database

Set db = CurrentDb

cmbvalue = Me.frm_report_summ_combo.Value

strSQL = "SELECT"
strSQL = strSQL & " [tbl_audit].[audit_no]"
strSQL = strSQL & ", [tbl_customer].[cust_name]"
strSQL = strSQL & ", [tbl_customer].[ccan]"
strSQL = strSQL & ", [tbl_customer].[cust_addr1]"
strSQL = strSQL & ", [tbl_customer].[cust_city]"
strSQL = strSQL & ", [tbl_customer].[cust_state]"
strSQL = strSQL & ", [tbl_customer].[cust_zip]"
strSQL = strSQL & ", [tbl_customer].[cust_contact_phone]"
strSQL = strSQL & ", [tbl_customer].[cust_contact_name]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_fax]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_compl_coord]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_type]"
strSQL = strSQL & ", [tbl_audit].[audit_physical_date]"
strSQL = strSQL & ", [tbl_audit].[audit_posted_date]"
strSQL = strSQL & ", [tbl_audit].[audit_days_to_close]"
strSQL = strSQL & ", [tbl_audit].[audit_extension]"
strSQL = strSQL & vbLf
strSQL = strSQL & "FROM ([tbl_customer]"
strSQL = strSQL & "INNER JOIN [tbl_extra_customer]"
strSQL = strSQL & "ON [tbl_customer].[ccan] = [tbl_extra_customer].[ccan])"
strSQL = strSQL & "INNER JOIN [tbl_audit]"
strSQL = strSQL & "ON ([tbl_customer].[ccan] = [tbl_audit].[ccan])"
strSQL = strSQL & "AND ([tbl_extra_customer].[ccan] = [tbl_audit].[ccan])"
strSQL = strSQL & vbLf
strSQL = strSQL & "WHERE ((([tbl_audit].[audit_no]) = cmbvalue))"

CurrentDb.Execute strSQL


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

Correct your SQL syntax and then use a recordset instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

ill keep looking

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

Here is the working code. Plagiat from several sources but it works

On Error GoTo ErrorHandler

Dim cmbvalue As Long 'For audit number from combobox
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String

cmbvalue = Me.frm_report_summ_combo.Value

strSQL = "SELECT"
strSQL = strSQL & " [tbl_audit].[audit_no]"
strSQL = strSQL & ", [tbl_customer].[cust_name]"
strSQL = strSQL & ", [tbl_customer].[ccan]"
strSQL = strSQL & ", [tbl_customer].[cust_addr1]"
strSQL = strSQL & ", [tbl_customer].[cust_city]"
strSQL = strSQL & ", [tbl_customer].[cust_state]"
strSQL = strSQL & ", [tbl_customer].[cust_zip]"
strSQL = strSQL & ", [tbl_customer].[cust_contact_phone]"
strSQL = strSQL & ", [tbl_customer].[cust_contact_name]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_fax]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_compl_coord]"
strSQL = strSQL & ", [tbl_extra_customer].[cust_type]"
strSQL = strSQL & ", [tbl_audit].[audit_physical_date]"
strSQL = strSQL & ", [tbl_audit].[audit_posted_date]"
strSQL = strSQL & ", [tbl_audit].[audit_days_to_close]"
strSQL = strSQL & ", [tbl_audit].[audit_extension]"
strSQL = strSQL & ", [tbl_audit].[audit_officer]"
strSQL = strSQL & ", [tbl_audit].[audit_closed_time]"
strSQL = strSQL & ", [tbl_audit].[audit_closed_date]"
strSQL = strSQL & vbLf
strSQL = strSQL & "FROM ([tbl_customer] "
strSQL = strSQL & "INNER JOIN [tbl_extra_customer] "
strSQL = strSQL & " ON [tbl_customer].[ccan] = [tbl_extra_customer].[ccan]) "
strSQL = strSQL & "INNER JOIN [tbl_audit] "
strSQL = strSQL & " ON ([tbl_customer].[ccan] = [tbl_audit].[ccan]) "
strSQL = strSQL & " AND ([tbl_extra_customer].[ccan] = [tbl_audit].[ccan]) "
strSQL = strSQL & vbLf
strSQL = strSQL & "WHERE ((([tbl_audit].[audit_no]) =" & cmbvalue & "))"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("\\Stsburfp02\department\EF Audit & Compliance\WAD-F\WAD-B\Audit_Recap_Summary.xlt")
objXLApp.Application.Visible = True

objXLBook.ActiveSheet.Range("C2") = Now()
objXLBook.ActiveSheet.Range("C4") = rs.Fields("audit_no")
objXLBook.ActiveSheet.Range("C6") = rs.Fields("cust_name")
objXLBook.ActiveSheet.Range("J6") = rs.Fields("ccan")
objXLBook.ActiveSheet.Range("C7") = rs.Fields("cust_addr1")
objXLBook.ActiveSheet.Range("C8") = rs.Fields("cust_city")
objXLBook.ActiveSheet.Range("C9") = rs.Fields("cust_zip")
objXLBook.ActiveSheet.Range("J9") = rs.Fields("cust_contact_phone")
objXLBook.ActiveSheet.Range("C10") = rs.Fields("cust_contact_name")
objXLBook.ActiveSheet.Range("J10") = rs.Fields("cust_fax")
objXLBook.ActiveSheet.Range("C12") = rs.Fields("audit_officer")
objXLBook.ActiveSheet.Range("J12") = rs.Fields("cust_type")
objXLBook.ActiveSheet.Range("C14") = rs.Fields("audit_physical_date")
objXLBook.ActiveSheet.Range("F14") = rs.Fields("audit_posted_date")
objXLBook.ActiveSheet.Range("J14") = rs.Fields("audit_days_to_close")
objXLBook.ActiveSheet.Range("C16") = rs.Fields("audit_extension")
objXLBook.ActiveSheet.Range("B99") = rs.Fields("cust_compl_coord")
objXLBook.ActiveSheet.Range("F102") = rs.Fields("audit_closed_date")
objXLBook.ActiveSheet.Range("C102") = rs.Fields("audit_closed_time")

ExitHere:

Set db = Nothing
Set rs = Nothing
Set objXLApp = Nothing
Set objXLBook = Nothing


If I did not say it before, I really appreciate the time and expertise of the users on this forum.