我需要一個(gè)代碼來遍歷表中的所有記錄，以便提取一些數(shù)據(jù)。除此之外，是否還可以遍歷過濾的記錄并再次提取數(shù)據(jù)？謝謝！
3 回答
holdtom
TA貢獻(xiàn)1805條經(jīng)驗(yàn) 獲得超10個(gè)贊
找到了一個(gè)很好的代碼，并帶有解釋每個(gè)語句的注釋。在-accessallinone找到代碼
Sub DAOLooping()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "tblTeachers"
'For the purposes of this post, we are simply going to make
'strSQL equal to tblTeachers.
'You could use a full SELECT statement such as:
'SELECT * FROM tblTeachers (this would produce the same result in fact).
'You could also add a Where clause to filter which records are returned:
'SELECT * FROM tblTeachers Where ZIPPostal = '98052'
' (this would return 5 records)
Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!!
'In English, this means that we have opened up a recordset
'and can access its values using the rs variable.
With rs
If Not .BOF And Not .EOF Then
'We don’t know if the recordset has any records,
'so we use this line of code to check. If there are no records
'we won’t execute any code in the if..end if statement.
.MoveLast
.MoveFirst
'It is not necessary to move to the last record and then back
'to the first one but it is good practice to do so.
While (Not .EOF)
'With this code, we are using a while loop to loop
'through the records. If we reach the end of the recordset, .EOF
'will return true and we will exit the while loop.
Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
'prints info from fields to the immediate window
.MoveNext
'We need to ensure that we use .MoveNext,
'otherwise we will be stuck in a loop forever…
'(or at least until you press CTRL+Break)
Wend
End If
.close
'Make sure you close the recordset...
End With
ExitSub:
Set rs = Nothing
'..and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
記錄集在遍歷數(shù)據(jù)時(shí)具有兩個(gè)重要的屬性，EOF（文件末尾）和BOF（文件開始）。記錄集就像表一樣，當(dāng)您遍歷一個(gè)表時(shí)，實(shí)際上是按順序從一個(gè)記錄移到另一個(gè)記錄。當(dāng)您在記錄中移動(dòng)時(shí)，EOF屬性設(shè)置為false，但在嘗試通過最后一條記錄后，EOF屬性變?yōu)閠rue。反之，BOF屬性的工作原理與此相反。
這些屬性使我們知道何時(shí)達(dá)到記錄集的限制。
- 3 回答
- 0 關(guān)注
- 979 瀏覽
添加回答
舉報(bào)
0/150
提交
取消