adp report source problem

[Keith G Hicks]

Using Access 2003 with A2k format

I'm trying somethign I haven't done before so I'm not sure if I'm on the
right track. I want to set the source of a report to an adodb command
object. Here's what I'm doing:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

With cmd
.ActiveConnection = cnnCurrProj
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.CommandText = "sp_RptNoticeReceipt"

.Parameters.Refresh
.Parameters.Item("@bitOneNoticeOnly").Value = bolOneNoticeOnly
.Parameters.Item("@iNoticeID").Value = IIf(bolOneNoticeOnly,
lngCurrNoticeID, 0)
.Parameters.Item("@sAttyIDs").Value = strGetAttyIDs
.Parameters.Item("@dtStartDate").Value = dteStartDate
.Parameters.Item("@dtEndDate").Value = dteEndDate

.Prepared = True

End With

rs.Open cmd

Me.RecordSource = rs.Source

All the above code (in the report's on open event) runs fine (no errors) but
when the report actually tries to display it closes right away and I get the
error:

The record source "|" specified on this form or report does not exist.

What am I doing wrong?

Thanks,

Keith

 

[Keith G Hicks]

I should mention that when I mouse over the line "Me.RecordSource =
rs.Source" in debug mode, the value shows:

rsSource = "{ ? = call sp_RptNoticeRecipt(?, ?, ?, ?, ?) }"

I also tried this in Access 2002/2003 format because I read some things
online that seemed to indicate that there were problems with this sort of
thing in A2k but that did not solve the problem. I had the exact same
results.

Keith

 

[Keith G Hicks]

Never mind. I figured it out. The end of the code should have been:

...
...
.Prepared = True
End With

rs.Open cmd, , adOpenStatic, adLockReadOnly

Set Me.Recordset = rs


I'm shocked nobody answered this. Seems like a normal thing to have to do
with ADP and SQL.

Keith

 

[RoyVidar]

Never mind. I figured it out. The end of the code should have been:

...
...
.Prepared = True
End With

rs.Open cmd, , adOpenStatic, adLockReadOnly

Set Me.Recordset = rs


I'm shocked nobody answered this. Seems like a normal thing to have to do
with ADP and SQL.

Shocked is perhaps a bit strong, I think even surprised would be strong,
since very few use ADPs, and this can only be done with ADPs. Also, not
very many use ADO.

Had this been the microsoft.public.access.adp.sqlserver, the ADP NG,
then I would perhaps have been surprised.