代码优化（二）

时　间:2017-12-26 08:18:42
作　者:杨雪   ID：42182  城市：南京
摘　要:代码优化
正　文:

源码：运行起来很慢。

strSQL = "Select HomestayName, BedScheduling, SexPreference, 剩余接待人数2, Area FROM qry_HomestaySelect " & _
             "where SexPreference like '*" & Me.[Sex] & "*' and Area in (select Headquarters from tbl_line where lineno=" & sqltext(Me.LineNo) & ")"
    Me.sfrChild.Form.RecordSource = strSQL
    Me.sfrChild.Form.Requery

优化代码：运行起来快了很多倍。

    Dim rst As Object
    Dim strSQL As String
    strSQL = "Select Headquarters FROM tbl_line Where [LineNo] = " & SQLText(Me.LineNo)
    Set rst = OpenAdoRecordset(strSQL)
    Dim strAreaList As String
    Do Until rst.EOF
        strAreaList = strAreaList & ",'" & rst!Headquarters & "'"
        rst.MoveNext
    Loop
    rst.Close
    If strAreaList <> "" Then strAreaList = " AND Area IN (" & Mid(strAreaList, 2) & ")"

    strSQL = " Select HomestayName, BedScheduling, SexPreference, 剩余接待人数2, Area" _
           & " FROM qry_HomestaySelect1 Where SexPreference Like '*" & Me.Sex & "*'" & strAreaList

    Me.sfrChild.Form.RecordSource = strSQL
    Me.sfrChild.Form.Requery