考虑以下功能集：
Function BuildQuerysql(lngsid As Long) As String
Dim intlvl As Integer
Dim strsel As String: strsel = selsql(intlvl)
Dim strfrm As String: strfrm = "people as p0 "
Dim strwhr As String: strwhr = "where p0.supervisor = " & lngsid
While HasRecordsP(strsel & strfrm & strwhr)
intlvl = intlvl + 1
BuildQuerysql = BuildQuerysql & " union " & strsel & strfrm & strwhr
strsel = selsql(intlvl)
If intlvl > 1 Then
strfrm = "(" & strfrm & ")" & frmsql(intlvl)
Else
strfrm = strfrm & frmsql(intlvl)
End If
Wend
BuildQuerysql = Mid(BuildQuerysql, 8)
End Function
Function HasRecordsP(strsql As String) As Boolean
Dim dbs As DAO.Database
Set dbs = CurrentDb
With dbs.OpenRecordset(strsql)
HasRecordsP = Not .EOF
.Close
End With
Set dbs = Nothing
End Function
Function selsql(intlvl As Integer) As String
selsql = "select p" & intlvl & ".personid from "
End Function
Function frmsql(intlvl As Integer) As String
frmsql = " inner join people as p" & intlvl & " on p" & intlvl - 1 & ".personid = p" & intlvl & ".supervisor "
End Function
在这里，BuildQuerysql可以为函数提供与PersonID对应的，Supervisor并且函数将为适当的查询返回“递归” sql代码，以获得PersonID主管的所有下属的。
因此，可以评估该函数以构造保存的查询，例如，对于具有的主管PersonID = 5，创建名为的查询Subordinates：
Sub test()
CurrentDb.CreateQueryDef "Subordinates", BuildQuerysql(5)
End Sub
或者根据您的应用程序的要求，对sql进行评估以打开结果的RecordSet。
602392714
清零编程群