Solved

Populate Form From a Stored Procedure

Posted on 2008-06-12
23
184 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

21 Experts available now in Live!

Get 1:1 Help Now