此示例需要用户提供职称,然后使用此职称做为查询的准则。
此示例调用过程 EnumFields 过程,且可以在 SELECT 语句示例中找到该过程。
Sub ParametersX()
Dim dbs As Database, qdf As QueryDef
Dim rst As Recordset
Dim strSql As String, strParm As String
Dim strMessage As String
Dim intCommand As Integer
' 在您的计算机中修改此行使其正确指到 Northwind 的路径。
Set dbs = OpenDatabase("Northwind.mdb")
Set dbs = OpenDatabase("NorthWind.mdb")
' 定义参数子句。
strParm = "PARAMETERS [Employee Title] TEXT; "
' 使用参数定义 SQL 语句。
'
strSql = strParm & "SELECT LastName, FirstName, " _
& "EmployeeID " _
& "FROM Employees " _
& "WHERE Title =[Employee Title];"
' 创建一个QueryDef对象基于
SQL 语句
Set qdf = dbs.CreateQueryDef _
("Find Employees", strSql)
Do While True
strMessage = "Find Employees by Job " _
& "职称:"& Chr(13) _
& " 选择工作职称:"& Chr(13) _
& " 1 - Sales Manager" & Chr(13) _
& " 2 - Sales Representative" & Chr(13) _
& " 3 - Inside Sales Coordinator"
intCommand = Val(InputBox(strMessage))
Select Case intCommand
Case 1
qdf("Employee Title") = _
"Sales Manager"
Case 2
qdf("Employee Title") = _
"Sales Representative"
Case 3
qdf("Employee Title") = _
"Inside Sales Coordinator"
Case Else
Exit Do
End Select
' 创建暂时的快照类型记录集。
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
' populateRecordset。
rst.MoveLast
' 调用 EnumFields 来打印记录集的内容。
'传递记录集对象和要求的字符宽度。
'
EnumFields rst, 12
Loop
' 删除 QueryDef 因为这是一个演示。
'
dbs.QueryDefs.Delete "Find Employees"
dbs.Close
End Sub