|
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.
| 
