Object Doesn't support property or method

ayazhoda · September 16th, 2009, 07:07 AM

Hi All

I am trying to get records froms stored procedure in access and link it with combobox but it gives error

Private Sub Form_Load()

'Dim r As New ADODB.Recordset

'r.CursorLocation = adUseClient

'Set r = CurrentProject.Connection.Execute("sp_CustomerSecu rityFilterTest")
'Set Me!Combobox.Recordset = r

Dim r As New ADODB.Recordset
Dim c As New ADODB.Command

c.ActiveConnection = SQLcon
c.CommandText = "sp_CustomerSecurityFilter"
c.CommandType = adCmdStoredProc
Set r = c.Execute

Set Me!SelectCustomer.RowSource = r

End Sub

Here's my stored procedure

ALTER PROCEDURE [dbo].[sp_CustomerSecurityFilterTest]

AS
-- Insert statements for procedure here
SELECT CID, Customer, CID, A1, APC,rep FROM Customers

Any suggestion or advice

Thanks

Ayaz

mmcdonal · October 16th, 2009, 09:08 AM

Generally to use a SELECT stored procedure, you would do this on the Access side:

Code:

'Open Connection
Set cn = New ADODB.Connection
cn.Open cString
 
'Create a new command object
Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn
 
'Specify the stored procedure to run
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "usp_MySelectProcedure"
 
'Create the parameter if needed
Set prmRecID = cmdCommand.CreateParameter("@RecordID", adInteger, adParamInput)
prmRecID.Value = lRecordID 'Long
cmdCommand.Parameters.Append prmRecID
 
'Open recordset
Set rs = cmdCommand.Execute

Now you can use the recordset as you normally would.

On the server side, the stored procedure might look like:

Code:

USE [MyDatabaseName]
GO
ALTER procedure [dbo].[usp_MySelectProcedure]
  @RecordID   int
as 
 
SELECT CID, Customer, A1, APC,rep 
FROM Customers
WHERE CID = @RecordID

Did that help? Of course you wouldn't need the parameter.

ayazhoda · November 16th, 2009, 07:35 AM

Hi donal

But I can't able to set Continous form Recordsource with recordobject

Regards

Ayaz