Public Sub CreateP2Procedure() '创建本示例需要用的参数存储过程 '表名和字段名请按你的实际情况填写
Dim strSQL As String strSQL = "Create Procedure procCreateMyList(@P1 int =0,@P2 nvarChar(50)='') As SELECT field1 FROM tblTable1 WHERE field2 = @P1 and field3 like @P2" CurrentProject.Connection.Execute strSQL End Sub
Private Sub Form_Open(Cancel As Integer) '将本过程放在窗体的打开事件中,设定组合框的行来源
Me.ComboBox.RowSourceType = "value list" Me.ComboBox.RowSource = GetRowSource("p2", 155, "%") End Sub
Public Function GetRowSource(ByVal strCommandText As String, _ Optional ByVal varP1, _ Optional ByVal varP2, _ Optional ByVal varP3) '本函数作用: '将存储过程的执行结果转换为用分号分隔的值列表 '现在参数是 3 个,可以更改一下,用数组来代替
Dim Rs As New ADODB.Recordset Dim cmd As New ADODB.Command Dim prm As ADODB.Parameter Const DELIM As String = ";" With cmd .ActiveConnection = CurrentProject.Connection .CommandText = strCommandText .CommandType = adCmdStoredProc End With
If Not IsMissing(varP1) Then Set prm = cmd.CreateParameter("P1", _ adInteger, adParamInput, , varP1) cmd.Parameters.Append prm End If If Not IsMissing(varP2) Then Set prm = cmd.CreateParameter("P2", _ adVarChar, adParamInput, 50, varP2) cmd.Parameters.Append prm End If If Not IsMissing(varP3) Then Set prm = cmd.CreateParameter("P3", _ adVarChar, adParamInput, 50, varP3) cmd.Parameters.Append prm End If
Rs.Open cmd GetRowSource = "" If Not Rs.EOF Then GetRowSource = Rs.GetString(adClipString, , _ DELIM, DELIM) End If