VBA Error Please Help

[graviz]

Here's my code for running a query and outputting it to an Excel spreadsheet. I receive this error:

"No value given for one or more required parameters"

The place where I receive the error is in red below. I've made sure the spelling is correct on the query name. "DTC_Output" The weird thing is this worked yesterday and I tried it today and I'm getting this error and I don't believe I changed anything. Any ideas?


Public Function Output_DTC()
Dim cnn As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Dim MySQL As String
Dim MyExcelPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn
MySQL = "SELECT * FROM "
MySQL = MySQL & "DTC_Output"
MyRecordSet.Open MySQL
MyExcelPath = "\\Mer2-corpfs1\dnsc\Resource Management\Hasselgren\Auto Reports\TravelDoc Report\TravelDoc Template.xls"
Set Xl = CreateObject("excel.application")
Set XlBook = GetObject(MyExcelPath)
Xl.Visible = True
XlBook.Windows(1).Visible = True
Set XlSheet = XlBook.Worksheets("DTC")
XlSheet.Range("A2").CopyFromRecordset MyRecordSet
MyRecordSet.Close
Set cnn = Nothing
Set Xl = Nothing
XlBook.Save
XlBook.Close
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

 

[boblarson]

Change this:

MyRecordSet.ActiveConnection = cnn
MySQL = "SELECT * FROM "
MySQL = MySQL & "DTC_Output"
MyRecordSet.Open MySQL


to this:

MySQL = "SELECT * FROM "
MySQL = MySQL & "DTC_Output"
MyRecordSet.Open MySQL, cnn, adOpenDynamic, adLockOptimistic

 

[graviz]

Change this:

MyRecordSet.ActiveConnection = cnn
MySQL = "SELECT * FROM "
MySQL = MySQL & "DTC_Output"
MyRecordSet.Open MySQL


to this:

MySQL = "SELECT * FROM "
MySQL = MySQL & "DTC_Output"
MyRecordSet.Open MySQL, cnn, adOpenDynamic, adLockOptimistic

No luck, same error

 

[boblarson]

So, you said that DTC_Output is spelled exactly like that? Does that include the underscore, or is there a space there? If a space you would need to use square brackets instead of an underscore.

 

[graviz]

So, you said that DTC_Output is spelled exactly like that? Does that include the underscore, or is there a space there? If a space you would need to use square brackets instead of an underscore.

The query name is spelled with an underscore.

 

[boblarson]

What happens if you do a debug > compile?

I don't see any other reason why it would have a problem with this but to test, maybe you should try substituting a different table or query name to see if it still does it.

 

[graviz]

What happens if you do a debug > compile?

I don't see any other reason why it would have a problem with this but to test, maybe you should try substituting a different table or query name to see if it still does it.

I selected Debug then went to Compile Access9db and nothing happened. I tried the step through again and it bombed at the same place.

 

[boblarson]

About the only thing I can think of now is to try importing everything into a new, blank file to see if corruption has taken hold.

You might also post the database here so we can take a look.

 

[dcb]

I can reproduce the error by putting in a parameter in the query

Make sure your query "DTC_Output" does not have anything like this in the where clause:
(ActivityLog.Customer)=[TestMsg])
The red bold being the parameter

Herewith the OP from immediate once i placed the parameter in the query

?err.Number 
-2147217904 
?err.Description 
No value given for one or more required parameters.

I Used your code to test with:

Dim cnn As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Dim MySQL As String
Dim MyExcelPath As String
'Dim Xl As Excel.Application
'Dim XlBook As Excel.Workbook
'Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn
MySQL = "SELECT * FROM "
MySQL = MySQL & "query5"
MyRecordSet.Open MySQL

Debug.Print MyRecordSet.Fields(0)
Debug.Print MyRecordSet.Fields(1)

 

[wazz]

agree with dcb. make sure there are no parameters. if you need them you could add:

Dim prm As DAO.Parameter
...
'Evaluate and set the query's parameters.
For Each prm In qdef.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdef.OpenRecordset
...