syntax error in stored procedure call

[belovedcej]

I have the following line in my VBA, attempting to open a recordset. I am getting a server syntax error near ",".

Does the error happen to jump out at anyone?

CurrentProject.Connection.Execute ("ReportName   
(" & longVariable & ", '" & dateVariable & "', '" & dateVariable & "')")

 

[PWise]

try
CurrentProject.Connection.Execute "ReportName
" & longVariable & ", '" & dateVariable & "', '" & dateVariable & "'

 

[belovedcej]

Thanks for the tip. The outer parentheses are required by VBA or it won't compile. But I tried this:

("ReportName
" & longVariable & ", '" & dateVariable & "', '" & dateVariable & "'")

I'm still getting the same error. I'm going to check some stuff on the server and see if maybe my error only "thinks" it is syntax.

 

[belovedcej]

Okay - so it was my own stupidity. No syntax error at all -just a problem in which report was using this. Thanks for the help!

 

[PWise]

CurrentProject.Connection.CommandText = "ReportName   Pramater1 ,pram2....."
Set rst = cmd.Execute()

 

[belovedcej]

Ah - I see what you are thinking. I didn't share the whole routine for brevity's sake.

This is my routine:

Public Sub setRst(Object As Object, SourceNoPrefix As String, Optional Parameter As Long)
'create a recordset object based on source.
'if only using one parameter, simply use the optional parameter variable.
'otherwise, build multiple parameters into the string of the Source and do
'not use the optional parameter.

Dim rst As New ADODB.Recordset

If Not (Parameter = 0) Then
    Set rst = CurrentProject.Connection.Execute(g_tablePrefix & SourceNoPrefix & "(" & Parameter & ")")
Else
    Set rst = CurrentProject.Connection.Execute(g_tablePrefix & SourceNoPrefix)
End If

Set Object.Recordset = rst
Set rst = Nothing

Then I call it from the open event of the report, form, combo box or listbox. In this instance, I use it this way:

Call setRst(Report_rptByCaseType, "RPT_By_Case_Type_SP( " & Form_frmCaseTypeListForReports.List0 & ", '" & g_parameter1 & "', '" & g_parameter2 & "')")

My mistake was actually in what report I was opening. once I fixed that, the problem was gone. Details, details... . sigh.

The beauty of this arrangment is that I simply have one line in most of the onOpen events in the system, since the "talking to the database" part was handled in my public routine. also, in our shop all our tables have a database specific prefix, which I capture via this variable: g_tablePrefix.

This way I can use the same front end for different databases.