通过查询访问 VBA 循环帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5747311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA Loop through Query help
提问by Len D'Alberti
I have a form (Cobind_frmMain) that allows the user to create a pool of titles that are attached to it. So there is a top level Pool Name (TopLvlPoolName) and on a subform, the titles are added to it. What I need is to issue a Report for each of the titles. I have the report and queries all set up. Right now, the report will show all the titles in one file. The titles are in a field called "CatCode".
我有一个表单 (Cobind_frmMain)，它允许用户创建一个附加到它的标题池。所以有一个顶级池名称 (TopLvlPoolName) 并且在子表单上，标题被添加到其中。我需要的是为每个标题发布一个报告。我已经设置好了报告和查询。现在，报告将显示一个文件中的所有标题。标题位于名为“CatCode”的字段中。
What I need is the following:
1. Save each title as a PDF and save it to our server.
2. Open email and attach the PDF.
3. Repeat until all titles are done.
我需要的是以下内容：
1. 将每个标题另存为 PDF 并将其保存到我们的服务器。
2. 打开电子邮件并附上 PDF。
3. 重复直到完成所有标题。
EDIT:This is what I have so far for code and the error message I still get is: "Too Few Parameters" on the Set Recordset line. I'm trying to set the parameter in the strSQL line. I want the PartPoolName (in Cobind_qryReport, a query) to equal the TopLvlPoolName on the open form. The SQL for Cobind_qryReport is listed below:
编辑：这是我到目前为止的代码，我仍然收到的错误消息是：Set Recordset 行上的“参数太少”。我正在尝试在 strSQL 行中设置参数。我希望 PartPoolName（在 Cobind_qryReport 中，一个查询）等于打开表单上的 TopLvlPoolName。Cobind_qryReport 的 SQL 如下所示：
Private Sub btn_Run_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "Select * FROM Cobind_qryReport WHERE PartPoolName = " & Me.TopLvlPoolName
Set rs = db.OpenRecordset(strSQL)
On Error GoTo Err_PO_Click
If MsgBox("Do you wish to issue the cobind invites?", vbYesNo + vbQuestion, "Confirmation Required") = vbYes Then
rs.MoveFirst
Do While Recordset.EOF = False
DoCmd.OutputTo acOutputReport, "Cobind_rptMain", acFormatPDF, "K:\OB MS Admin\Postage\CoBind Opportunities\Sent Invites\" & [CatCode] & "_" & [PartPoolName] & "Cobind Invite_" & Format(Now(), "mmddyy") & ".pdf"
DoCmd.SendObject acSendReport, "Cobind_rptMain", acFormatPDF, , , , [CatCode] & "_" & [PartPoolName] & " Cobind Invite", "Please find the cobind invite attached. Response is needed by " & [RSVP] & ". Thank you.", True
Recordset.MoveNext
Loop
End If
Exit_PO_Click:
MsgBox ("It didn't work")
Exit Sub
Err_PO_Click:
MsgBox Err.Description
Resume Exit_PO_Click
End Sub
Cobind_qryReport SQL:
SELECT tblEvents.EventTitle, Cobind_tblPartic.CatCode, Cobind_tblPartic.CodeQty, Cobind_tblPartic.PartPoolName, Cobind_tblTopLvl.RSVP, Cobind_tblPartic.ID
FROM Cobind_tblTopLvl, Cobind_tblPartic INNER JOIN tblEvents ON Cobind_tblPartic.CatCode = tblEvents.EventCode
GROUP BY tblEvents.EventTitle, Cobind_tblPartic.CatCode, Cobind_tblPartic.CodeQty, Cobind_tblPartic.PartPoolName, Cobind_tblTopLvl.RSVP, Cobind_tblPartic.ID
ORDER BY Cobind_tblPartic.ID;
Cobind_qryReport SQL：
SELECT tblEvents.EventTitle，Cobind_tblPartic.CatCode，Cobind_tblPartic.CodeQty，Cobind_tblPartic.PartPoolName，Cobind_tblTopLvl.RSVP，Cobind_tblPartic.ID FROM Cobind_tblTopLvl，Cobind_tblPartic INNER JOIN tblEvents ON Cobind_tblPartic.CatCode = tblEvents.EventCode GROUP BY tblEvents.EventTitle，Cobind_tblPartic。 CatCode, Cobind_tblPartic.CodeQty, Cobind_tblPartic.PartPoolName, Cobind_tblTopLvl.RSVP, Cobind_tblPartic.ID ORDER BY Cobind_tblPartic.ID;
Thank you again for all your help!
再次感谢您的帮助！
回答by Conrad Frix
You're query Cobind_qryReporthas a parameter that you need to set. if you want to know the parameter name try the following code
您的查询Cobind_qryReport有一个需要设置的参数。如果您想知道参数名称，请尝试以下代码
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Cobind_qryReport")
If qdf.Parameters.Count > 0 Then
MsgBox (qdf.Parameters(0).Name)
End If
UpdateSince you know you've got a parameter doing select * from Cobind_qryReportit might just be easier to set the parameter and then use the qdf to open the recordset e.g.
更新因为你知道你有一个参数，select * from Cobind_qryReport所以设置参数可能会更容易，然后使用 qdf 打开记录集，例如
Dim rs as DAO.Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Cobind_qryReport")
qdf.Parameters(0).Value = 7832
Set foo = qdf.OpenRecordset()
Note: you can use the parameter name in the place of the ordinal when setting the parametervalue
注意：可以在设置参数值时使用参数名称代替序号
e.g. qdf.Parameters("Foo").value = 7832
例如 qdf.Parameters("Foo").value = 7832
