Solved

Populate Form From a Stored Procedure

Posted on 2008-06-12
23
191 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
  • 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now