Solved

Populate Form From a Stored Procedure

Posted on 2008-06-12
23
204 Views
Last Modified: 2008-06-25
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
Comment
Question by:slamhound
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
23 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775201
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775210
Further to that, was introduced in Access 2000
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775233
Try adding the line

cmd.Prepared = True

just before the .Execute line
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775235
Also, drop the line Set rs = NEW ADODB.Recordset
0
 
LVL 10

Author Comment

by:slamhound
ID: 21775543
Using Access 2003. Made your suggested changes but the error still comes up in the same place.

Any more ideas?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775551
Which line returns the error?
0
 
LVL 10

Author Comment

by:slamhound
ID: 21775561
Me.frmPrinterOrdersub.Form.Recordset = rs ' --------- This is where I get the error
0
 
LVL 10

Author Comment

by:slamhound
ID: 21775615
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775657
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
 
LVL 10

Author Comment

by:slamhound
ID: 21775683
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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 450 total points
ID: 21775741
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
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 50 total points
ID: 21775745
At a guess, try

Set Me.frmPrinterOrdersub.Form.Recordset = rs

0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775748
Just looked back at your code - forget the active connection bit I missed that

try .ActiveConnection = cnn in your case
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21775769
Alsdo, if passing NULLs to your stored proc, make sure that NULLs are permitted there or temporarily add default values to resolve.
0
 
LVL 10

Author Comment

by:slamhound
ID: 21775812
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
 
LVL 10

Author Comment

by:slamhound
ID: 21776207
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21776275
Try

Set Forms("frmPrinterOrdersub").Recordset = rs
0
 
LVL 10

Author Comment

by:slamhound
ID: 21776494
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21776537
What about

Set Forms!frmPrinterOrder.!frmPrinterOrdersub.Form.Recordset = rs
or
Set Forms!frmPrinterOrder!frmPrinterOrdersub.Recordset = rs
0
 
LVL 10

Author Comment

by:slamhound
ID: 21776564
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21777316
Set Forms!frmPrinterOrder.Form!frmPrinterOrdersub.Recordset = rs

What does this give?
0
 
LVL 10

Author Comment

by:slamhound
ID: 21787546
"Object doesn't support this property or method"
0
 
LVL 10

Accepted Solution

by:
slamhound earned 0 total points
ID: 21808191
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question