Problem with loading data into a SubForm (datasheet) from a stored Procedure

[azizsohail]

hi all,

i got a main Form , which got a couple of dropdowns combos. then i got a command button. and a subform with a datasheet view.
when i make selection in the combos , and click the button , i want to execute a stored procedure, and show the returned data in the datasheet (subform).

my code in the button is as follows.

Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim srtqry As String
rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenDynamic

Set Cmd = New ADODB.Command
Cmd.ActiveConnection = CurrentProject.Connection

Cmd.CommandText = "dbo.ICT_Reports_LoadData_Searchform"
Cmd.CommandType = adCmdStoredProc
Cmd.Parameters.Refresh
Cmd.Parameters(1).Value = Me.ApplicationCombo.Column(0)
Cmd.Parameters(2).Value = Me.CategoryCombo.Column(0)


Set rsData = Cmd.Execute()

Me.SubFormA_SearchForm.RecordSource = rsData
'Close Connection

Set rsData = Nothing


Exit_CmdSearch_Click:
Exit Sub
Err_CmdSearch_Click:
MsgBox Err.Description
Resume Exit_CmdSearch_Click

End Sub

but this give me an error "Method or data member not found" ...

Please help, its urgent


(sorry there was some problem with the website,,, kept on giving me the message connection timeout)

 

[JamBoston]

You're trying to assign your recordset object to the recordsource string variable. Instead you need to assign the recordset to the recordset of the form using Set. Something like Set Me.SubFormA_SearchForm.RecordSet = rsData

 

[LPurvis]

Just to mention....

>> rsData.ActiveConnection = CurrentProject.Connection

So this is an ADP you're using?
You could bind more simply

Me.SubFormA_SearchForm.Form.RecordSource = "EXEC dbo.ICT_Reports_LoadData_Searchform '" & Me.ApplicationCombo.Column(0) & "', '" & Me.CategoryCombo.Column(0) & "'"

I mean literally - that is your entire code.

Just to mention also you refer to
'Close Connection
But you did not (just destroyed a recordset object variable) - and in an ADP you would very rarely want to close its connection.

Are you wanting an updatable result set in the subform?

Cheers.

 

[azizsohail]

Leigh - Thanks for the very useful tips. Yes i am using ADP. as we require very customised functionality from my Application, Plus totally UNbound froms. that was the reason i used ADP.
i am new to Access. i am crystal/SQL developer. i really appriciate your help. i think i need to go through all my code again,as i think i used eccessive code in many places.
This is a search from, where user will select what he want to see in the dropdowns then click on the button, and it will fill the datasheet subform,
then the user will double click onthe required row to open it in a new form in full and update accordingly. thats the plan....