By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,190 Members | 1,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,190 IT Pros & Developers. It's quick & easy.

Set Form Recordset as ADO Recordset

P: n/a
I have a form with a search button. I'm using command parameters to
pass search criteria to a stored procedure. Here is my code:

Stored procedure:
CREATE PROCEDURE [dbo].[sp_GetPersConfidential]
@strCriteria varchar(200)
AS
execute (@strCriteria)
GO

VB Code for Personnel form in Access:
Private Sub Form_Activate()

' if these booleans get lost, rebuild them
If blnGlobalsSet = False Then
Call GetSQLConn
Call CheckValidUser
End If

Set Rst = New ADODB.Recordset
Set Cmd = New ADODB.Command
Set Prm = New ADODB.Parameter
Cmd.ActiveConnection = Cnn
Rst.ActiveConnection = Cnn

If SrchCriteria = "" Then
SrchCriteria = "Select * from Personnel ORDER by Name"
End If

With Cmd
.ActiveConnection = Cnn
.CommandType = adCmdStoredProc
If Security = "C" Or Security = "A" Then
Cmd.CommandText = "sp_GetPersConfidential"
Else
Cmd.CommandText = "sp_GetPersNoConfidential"
End If
.Parameters.Append .CreateParameter("@strCriteria",
adVarChar, adParamInput, 200, SrchCriteria)

With Rst
.ActiveConnection = Cnn
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
End With
Set Rst = .Execute

MsgBox "SrchCriteria = " & SrchCriteria & Chr(13) &
Rst.Fields(0).Value
' Form.Recordset = Rst
' If Rst.RecordCount < 0 Then
' MsgBox "No Records match the criteria you have
entered.", 48, "Warning"
' End If
End With

' Set the form's Recordset property to the ADO recordset
Set Me.Recordset = Rst

' Rst.Close
Set Cmd = Nothing
Set Rst = Nothing
Set Prm = Nothing
MyCriteria = ""

End Sub

If I change the select statement to pass me the count of records, I
see the appropriate recordcount based on the search criteria I pass.
However, when I change it back to the asterisk and try to SET
ME.RECORDSET = RST, I get the error runtime error 7965 the object you
entered is not a valid recordset property. I tried moving the SET
ME.RECORDSET = RST to within the WITH RST construct, but get the same
error message.

When the form loads, all the fields show ?Name. If I add a msgbox to
show me the value of RST.FIELDS(0).VALUE, I see valid data there. How
do I get this data to populate on my form? I see all these posts
about how this is supposed to work but I can't get the recordset or
recordsource properties to equal the ADO recordset data.

The Personnel form is not closed when the search button is clicked,
but it is requeried once the search form is closed.

Thanks for your help.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I'm not sure if it has something to do with how the textboxes were
created. The conrol source entries are the field name entries from
the select statement. They don't include any reference to the ADO
recordset. Should they?
Nov 12 '05 #2

P: n/a
Has anyone done this successfully?
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.