Populate Form From a Stored Procedure

I'm trying to populate a form from the restuls of a stored procedure but I'm getting this error: "Operation is not supported for this type of object"

What am I doing wrong?
Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
   
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={SQL Server};Server=SOY-SQL;Database=SOYSourceSQL;UID=xxxx;Password=xxxxx"
    
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn 
 
    cmd.CommandText = "usp_printerorder_pending"
    cmd.CommandType = adCmdStoredProc
    
    'Set the parameters
'    cmd.Parameters.Append (cmd.CreateParameter("@FromDate", adDate, adParamInput, 10, Me.dteFrom.Value))
'    cmd.Parameters.Append (cmd.CreateParameter("@ToDate", adDate, adParamInput, 10, Me.dteTo.Value))
 
    Set rs = New ADODB.Recordset
    Set rs = cmd.Execute
    Me.frmPrinterOrdersub.Form.Recordset = rs ' --------- This is where I get the error
 
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
 
    Me.frmPrinterOrdersub.Requery

Open in new window

LVL 10
slamhoundAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
slamhoundConnect With a Mentor Author Commented:
I got it working! Thanks for all your help!

If you want to help me work out why it's running so slow, see http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23493546.html
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
 
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={SQL Server};Server=SOY-SQL;Database=SOYSourceSQL;UID=xxxxx;Password=xxxxxx"
    cnn.CursorLocation = adUseClient
 
    Set cmd = New ADODB.Command 'New Command
 
    With cmd
        .ActiveConnection = cnn
        ' .ActiveConnection = Application.CurrentProject.Connection
        .CommandText = "usp_printerorder_pending"
        .CommandType = adCmdStoredProc
        .Prepared = True
        '.Parameters.Refresh
        .Parameters("@FromDate").Value = Me.dteFrom.Value
        .Parameters("@ToDate").Value = Me.dteTo.Value
        Set rs = .Execute
    End With
    
    Set Me.frmPrinterOrdersub.Form.Recordset = rs
 
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing

Open in new window

0
 
Kelvin SparksCommented:
What version of Access? From memory binging a form to a recordset was introduced for MS Access 2002 (although I never used it to try)
0
 
Kelvin SparksCommented:
Further to that, was introduced in Access 2000
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Kelvin SparksCommented:
Try adding the line

cmd.Prepared = True

just before the .Execute line
0
 
Kelvin SparksCommented:
Also, drop the line Set rs = NEW ADODB.Recordset
0
 
slamhoundAuthor Commented:
Using Access 2003. Made your suggested changes but the error still comes up in the same place.

Any more ideas?
0
 
Kelvin SparksCommented:
Which line returns the error?
0
 
slamhoundAuthor Commented:
Me.frmPrinterOrdersub.Form.Recordset = rs ' --------- This is where I get the error
0
 
slamhoundAuthor Commented:
At the moment the recordset should be returning zero rows. Could this be an issue?

If it is, I'm getting a "Object Required" error on the first of the Paremeter lines so I can't define the parameters to give us some records at the moment.
0
 
Kelvin SparksCommented:
Possibly.

I personally don't use that syntax preferring not to use parameters append, but to use
.Execute Parameters:=Array(Me.dteFrom, Me.dteTo)

I hadn't looked closely at your code - I personally don't use the .value either (although technically correct).

But here, passing Null values may create an issue

Try and see
0
 
slamhoundAuthor Commented:
I tried this without success:
Set rs=cmd.Execute Parameters:=Array(Me.dteFrom, Me.dteTo)

Can you post the code you use? I don't really care how it works, just so long as it works.

0
 
Kelvin SparksConnect With a Mentor Commented:
Note the active connection

and () after execute
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
 
Set cmd = New Command
 
With cmd
        .ActiveConnection = Application.CurrentProject.Connection
        .CommandText = "usp_printerorder_pending"
        .CommandType = adCmdStoredProc
        .Prepared = True
        
        Set rs = .Execute() ParametersParameters:=Array(Me.dteFrom, Me.dteTo)

Open in new window

0
 
nmcdermaidConnect With a Mentor Commented:
At a guess, try

Set Me.frmPrinterOrdersub.Form.Recordset = rs

0
 
Kelvin SparksCommented:
Just looked back at your code - forget the active connection bit I missed that

try .ActiveConnection = cnn in your case
0
 
Kelvin SparksCommented:
Alsdo, if passing NULLs to your stored proc, make sure that NULLs are permitted there or temporarily add default values to resolve.
0
 
slamhoundAuthor Commented:
Access doesn't like this line with or without the double "Parameters":
Set rs = .Execute() ParametersParameters:=Array(Me.dteFrom, Me.dteTo)

"Expected: end of statement" after the Execute()
0
 
slamhoundAuthor Commented:
I've got the below code working ok but I still can't get it into the recordset of the form.
The final line gives this error message: "The object you entered is not a valid Recorset property"

We're almost there! Any more ideas?
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
 
    Set cnn = New ADODB.Connection
    cnn.Open "Driver={SQL Server};Server=SOY-SQL;Database=SOYSourceSQL;UID=xxxx;Password=xxxxxxxx"
 
    Set cmd = New ADODB.Command 'New Command
 
    With cmd
        .ActiveConnection = cnn
        ' .ActiveConnection = Application.CurrentProject.Connection
        .CommandText = "usp_printerorder_pending"
        .CommandType = adCmdStoredProc
        .Prepared = True
        .Parameters.Refresh
        .Parameters(0).Value = Me.dteFrom
        .Parameters(1).Value = Me.dteTo
        Set rs = .Execute
    End With
 
    Set Me.frmPrinterOrdersub.Form.Recordset = rs ' <--- error here

Open in new window

0
 
Kelvin SparksCommented:
Try

Set Forms("frmPrinterOrdersub").Recordset = rs
0
 
slamhoundAuthor Commented:
The above fails because it can't find the form. frmPrinterOrdersub is a subform so I tried:

Set Forms!frmPrinterOrder.Form.frmPrinterOrdersub.Form.Recordset = rs

and I'm getting "The object you entered is not a valid Recordset property." again.
0
 
Kelvin SparksCommented:
What about

Set Forms!frmPrinterOrder.!frmPrinterOrdersub.Form.Recordset = rs
or
Set Forms!frmPrinterOrder!frmPrinterOrdersub.Recordset = rs
0
 
slamhoundAuthor Commented:
The first line (using ! instead of .!) returns "The object you entered is not a valid Recorset Objeect" and the second generated "Object doesn't support this property or method"
0
 
Kelvin SparksCommented:
Set Forms!frmPrinterOrder.Form!frmPrinterOrdersub.Recordset = rs

What does this give?
0
 
slamhoundAuthor Commented:
"Object doesn't support this property or method"
0
All Courses

From novice to tech pro — start learning today.