Trouble setting report recordsource (in Access 2007 adp project) to an ADO recordset returned by SQL Server 2005 stored proc

I have run into this problem with both Acces2k3 and 2k7.  It's a very tricky issue that will involve just trying different things to solve it.  There are a few discoveries I have made:
1.  A corrupt form/report
2. An invalid .RowSource in a combo/listbox.  I had this happen when I somehow had a miss-spelled field in a .RowSource query.
3. The project or report is corrupt
4. Corrupt code in report/form - If this happens you would be able to open a VBA window within the report/form.

Since this is a report I would suspect it has something to do with the report (as opposed to the project) but this what is hard about the issue.  You might want to open a new .adp file and import all the objects from this project.  Also, I found with Access 2k7 that code can get corrupt as well.  Is there any other code in the report?  If these things don't work then, as I have done several times, you might just have to re-write that code snippet with something else that does the same thing.  I would need to see your code as well.  For example, why are you trying to set the recordset at runtime?  Is there a specific reason why you are doing this?  Otherwise, just set the SP on the report with no parameters or criteria.  Make sure and run the report "as is" to see if it works with you not trying to set the recordset (no paramter criteria).  If that works, the simply open the report in one of several ways:

1.
    stDocName = "rptYourReport"
    Me.Dirty = False
    DoCmd.OpenReport stDocName, acViewPreview, , "lngJob_ID=" & Me.lngJob_ID  '<----here's where
    the SP should filter the data for the report

2.  In the On Open Event handler, try this:
     Me.RecordSource = "spYourSP"
Or
3.
    Me.RecordSource = "Select * from spYourSP where Somefield=" & YourFieldValue
or
4.
DoCmd.OpenReport stDocName, acViewPreview, "lngJob_ID=" & Me.lngJob_ID '<--set up as filter rather than a report criteria.  Notice the comma and location of filter statement.

Again this is a very difficult issue that requires a bunch of experimenting to solve.  My biggest suspect is corruption but bad code can do it.  Check all the spellings of your code in the report.

Arji,
Thank you for your suggestions, I will try them.  
I am a beginner on this subject, so I appreciate any help I can get.
In short,
I have Access 2007 (adp) project as a front end (form with populated list box and View Report button)
A List box is populated from the Customer table with hidden ID field and with Customer Names.
OnClick of a button it should get the ID from the list box, call stored proc from SQL Server 2005 with this id as parameter and populate the report with the query returned from the stored procedure.
(I need to pass parameter because based on it sp will run different select for different customer)
This is what I have on the form:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_Handler

    'Purpose:  Open the report filtered to the items selected in the list box.
   
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
   
   
    'strDelim = """"            'Delimiter appropriate to field type.
    strDoc = "Customer Info"

    'Loop through the ItemsSelected in the list box.
    With Me.lstCid
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
   
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[Customer_Rebate_ID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Categories: " & Left$(strDescrip, lngLen)
        End If
    End If
   
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    sPar = lstCid.Value

    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.DESCRIPTION, , "cmdPreview_Click"
    End If
    Resume Exit_Handler

End Sub

This is what I have In the On Open Event on report:

Private Sub Report_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Dim sSql As String

sSql = "dbo.sp_GetCustomerInfo_By_Name" & " " & sPar
rst.Open sSql, cnn
'Set Me.Recordset = rst
   
Set rst = Nothing
Set cnn = Nothing
End Sub

All controls (textboxes and labels) on the report are unbound.
And I have Public sPar As Variant at the Module level.
The project crashing every time when I uncomment the 'Set Me.Recordset = rst

What would you suggest I should try in this case?

Thank you,

I'm assuming you tried a breakpoint and step(F8) through the code to examine variable values.  For instance, what is the value of sPar when the code gets to that line?

Some comments and experiments:

Private Sub Report_Open(Cancel As Integer)
Dim sSql As String
'I question your sSQL statement above.  Doing it this way is the hard way.  You should be able to just have:  Me.RecordSource = "dbo.sp_GetCustomerInfo_By_Name " but that won't pass a parameter(see below).
'ALSO,
'You are also appending sPar to the the end of a sp name?  This will result in an invalid string for you to open the recordset.  Basically, you are trying to open:

"dbo.sp_GetCustomerInfo_By_Name 2"  

Where 2 represents the value of sPar.  This statement will not work.  Without knowledge of your SP I'm assuming you are trying to refer to a singular Customer ID with the sPar value as a parameter criteria.  If so, then the proper way to do that is:

sSql = "dbo.sp_GetCustomerInfo_By_Name WHERE lngCustomerID=" & sPar
Me.RecordSource = sSQL
'replace lngCustomerID with whatever you named your customer ID

End Sub


One last thing.  Are you planning to put error handling into this sub?  It might give you more information if you trap the error and display the actual error number and description.  If  you need help with that I can post a generic handler for you.  You should have error handling in every sub/function you create or you could have the app crash during runtime.

Arji,
I tried it your way, but I am getting the following error:
 2580-The recordsource dbo.sp_GetCustomerInfo_By_Name Where @CustomerID =1001 specified on the form or report does not exists

You're welcome....

I've noticed this error seems to occur when using the SQL "bit" datatype in the scenario above.  I've worked around it by using "smallint".  This is handy also because -1 matches up to MS Access "true".  In my case the extra few bytes of data storage were worth it.   It can also happen by assigning the recordset to the same form twice in a row:   http://support.microsoft.com/kb/295191