Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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

0
slamhound
Asked:
slamhound
  • 12
  • 10
3 Solutions
 
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
 
Kelvin SparksCommented:
Try adding the line

cmd.Prepared = True

just before the .Execute line
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 SparksCommented:
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
 
nmcdermaidCommented:
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
 
slamhoundAuthor 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now