Opening recordset with parameter query results problem

[JonAccess]

Hello All -
Ultimate Goal = Exporting Query results to Excel
Problem = Run-Time Error '-2147217904 (80040e10)' No value given for one or more required paramters
The error occurs on red highlighted line in code below
I know the problem is referencing my form control value as the parameter in SQL via VBA. Any ideas?

Private Sub Command3_Click()
 
' Access Dims and Sets
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim MySQL As String
 
' Excel Dims
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
 
MySQL = "SELECT [CO-DIV].* FROM [CO-DIV] WHERE ((([CO-DIV].SHIPTO) Like [Forms]![frm_Queries].[FirstFive] & '*')) Or ((([CO-DIV].POINTER) Like [Forms]![frm_Queries].[FirstFive] & '*')) Or ((([CO-DIV].PRICE) Like [Forms]![frm_Queries].[FirstFive] & '*')) ORDER BY [CO-DIV].SHIPTO;"
 
[B][COLOR=darkred]MyRecordset.Open MySQL[/COLOR][/B]
 
MySheetPath = "C:\'Whatever'.xlsm"
 
Xl.Visible = True
XlBook.Windows(1).Visible = True
 
Set XlSheet = XlBook.Worksheets("Profiles")
XlSheet.Range("a1").CopyFromRecordset MyRecordset
 
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlSheet = Nothing
Set XlBook = Nothing

Thanks in adavnce!

 

[boblarson]

Any reason you are using ADO instead of DAO? I, personally, would use DAO. In fact, I have a function which does what you are looking for here, on my website:
http://www.btabdevelopment.com/ts/tq2xlspecwspath
And then you just paste it into a standard module (not form, report, or class module) and then name the module something other than the procedure name. Then you can simply call it. Make sure you have a DAO reference set.

 

[boblarson]

Oh, by the way, your SQL needs to have the form references OUTSIDE of the string:
MySQL = "SELECT [CO-DIV].* FROM [CO-DIV] WHERE ((([CO-DIV].SHIPTO) Like " & [Forms]![frm_Queries].[FirstFive] & "'*')) Or ((([CO-DIV].POINTER) Like " & [Forms]![frm_Queries].[FirstFive] & "'*')) Or ((([CO-DIV].PRICE) Like " & [Forms]![frm_Queries].[FirstFive] & "'*')) ORDER BY [CO-DIV].SHIPTO;"

Make sure you also see the double quotes I had to add near the asterisks.

 

[JonAccess]

Thanks for the reply.

The only reason I was using ADO is because I'm not familiar with exporting to Excel via DAO. So no real preference; whichever is easiest.

I tried using your function simply by

Private Sub Command3_Click()
 
Call SendTQ2XLWbSheet("qry_profiles", "profiles", "C:\Documents and Settings\jon.dadisman\Desktop\Price Master Template (VBA).xslm")

But it is giving me a "Too few parameters. Expected 1" error. The query runs fine except in VBA.

Also, I fixed the form references. Thank you, good eye.

Any ideas?

 

[boblarson]

What is the SQL for your query?

 

[JonAccess]

My Query in "SQL View" is:

SELECT [CO-DIV].*
FROM [CO-DIV]
WHERE ((([CO-DIV].SHIPTO) Like [forms]![frm_Queries].[FirstFive] & "*")) OR ((([CO-DIV].POINTER) Like [forms]![frm_Queries].[FirstFive] & "*")) OR ((([CO-DIV].PRICE) Like [forms]![frm_Queries].[FirstFive] & "*"))
ORDER BY [CO-DIV].SHIPTO;

 

[boblarson]

I think the problem lies in the use of the wildcards. You will likely need to either set the parameters using a querydef object before using the query or you can just use code for the query instead of trying to use a saved query.

 

[JonAccess]

If I were to use the SQL code instead of calling the name of my query, how would I do that using your function?

 

[boblarson]

Just create the SQL string in code assigned to a variable like strSQL and then just call it like:

Call SendTQ2XLWbSheet(strSQL, "profiles", "C:\Documents and Settings\jon.dadisman\Desktop\Price Master Template (VBA).xslm")

 

[JonAccess]

Error it couldn't find the table or query name. Me!headache = Now()