vba - 如何修复此代码以防止访问崩溃。我假设循环出现了一些错误
问题描述
我得到它只是列出所有内容,但我需要做一些分组。我的想法是获取地址列表,然后当我遍历这些地址时,使用我想要显示的信息过滤另一个查询。如果我这样做,我不会收到错误,但它会挂起程序。我假设这是循环的问题,但我不确定如何。有什么建议么?
Public Function getActionItems(strID As String, strType As String) As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdfAddress As DAO.QueryDef
Dim rst As DAO.Recordset
Dim rstAddress As DAO.Recordset
Dim s As String
Set dbs = CurrentDb
'Get the parameter query
Set qdf = dbs.QueryDefs("qryActionItems")
Set qdfAddress = dbs.QueryDefs("qryActionItemsAddresses")
'get all records
Set rst = qdf.OpenRecordset()
Set rstAddress = qdfAddress.OpenRecordset()
'get all records with the submisison number
rstAddress.filter = "submission_number=" & strID
Set rstAddressFiltered = rstAddress.OpenRecordset
'cycle through the addresses
If Not rstAddressFiltered.EOF Then
rstAddressFiltered.MoveFirst
s = s + "<strong>" & rstAddressFiltered!Address & "</strong>" & vbLf & "<ol>"
Do
'filter for the address
rst.filter = "submission_number=" & strID & "AND Address=" & """ & rstAddressFiltered!Address & """
Set rstFiltered = rst.OpenRecordset
'cycle through the records with the address
If Not rstFiltered.EOF Then
rstFiltered.MoveFirst
Do
s = s + vbTab & "<li>" & rstFiltered!Address & " - " & rstFiltered!Notes & " - " & rstFiltered!Due_date & "</li>" & vbLf
rstFiltered.MoveNext
Loop Until rstFiltered.EOF
End If
Loop Until rstAddressFiltered.EOF
s = s + "</ol>"
End If
End Function
编辑:我想可能是我错过了 .movenext,但我还没有机会尝试。
主要查询有submission_number、type、address、notes
我正在尝试获得类似 123 main st 的东西
- 富吧
- 吧台
126 主街
- 笔记
当我运行查询时,我不知道我有什么或多少个地址。所以我想我会使用 query1 来获取地址,然后使用地址query1
进行过滤query2
,打印这些结果。
如果你看到更好的方法,我很开放!
解决方案
过滤条件有语法错误。AND前面需要一个空格。引号分隔符错误。使用撇号而不是尝试加倍引号。
rst.filter = "submission_number=" & strID & " AND Address='" & rstAddressFiltered!Address & "'"