5套记录让我发疯 [英] 5 record sets making me crazy

问题描述

我有一个应用程序，它将基于五个单独的查询生成一个Word文档，这需要我创建一个ADODB连接：


''创建与当前数据库的连接
Dim Conn As ADODB.Connection

设置Conn = CurrentProject.Connection


有五个单独的记录集，每个查询一个。记录集使用书签在Word文档中填写表格。 Word文档的某些区域具有重复表。例如，其中一个表包含有关特定文档的信息，如备忘录，信件。如果要有多个引用文档，则表格将重复并根据查询中的下一条记录填写。这没有问题。


当集成用户注释的单独查询（和记录集）时，它会变得有趣。这些用户注释描述了备忘录或字母，以便让Word文档的读者了解并理解各种字母和备忘录所代表的历史。由于我的Word文档所需的格式，这需要一个嵌套循环。见下面的代码



''这个重复用于记录查询和宏


''数据库中记录集的变量，记录集的命令

Dim lettersQuery As New ADODB.Recordset

Dim comLetters As New ADODB.Command


''访问名称查询

Dim lettersQuery As String

lettersQuery =" lettersQuery"


with comLetters

Set .ActiveConnection = Conn

.CommandText =" lettersQuery"

.CommandType = adCmdStoredProc

Set parameter = .CreateParameter（" ReferenceNo"， adVarChar，_

adParamInput，Len（RefNo），RefNo）

.Parameters.Append parameter

结束


lettersQuery.Open comLetters ,, adOpenKeyset，adLockOptimistic


''Loop for letters

Do虽然不是lettersQuery.EOF


''在Word文档中运行宏

wordApp.Run" CreateLettersTable"


''用查询数据填充文档书签

（（代码不相关））


''循环注释


''设置letterNo变量等于当前记录条目号

LetterNo = lettersQuery（" LetterNo"）。值


''记录集的变量在数据库中，记录集的命令

Dim notesQuery As New ADODB.Recordset

Dim comNotes as new ADODB.Command


'' Access查询的名称

Dim notesQuery As String

notesQuery =" notesQuery"


with comNotes

Set .ActiveConnection = Conn

.CommandText =" notesQuery"

.Co mmandType = adCmdStoredProc

设置参数= .CreateParameter（" LetterNo"，adVarChar，_

adParamInput，Len（LetterNo），LetterNo）

.Parameters.Append参数

结束


notesQuery.Open comNotes ,, adOpenKeyset，adLockOptimistic ************


''循环注释

Do While Not notesQuery.EOF


''在Word文档中运行宏

如果notesQuery.AbsolutePosition> 1然后

wordApp.RunAddNoteLine

结束如果


''使用查询数据填充文档书签
（（代码不相关））


''移动到数据源中的下一条记录

notesQuery.MoveNext


循环''音符循环结束。


''关闭记录集

notesQuery.Close

''设置notesQuery = Nothing

comNotes.Cancel


''移动到数据源中的下一条记录

lettersQuery.MoveNext


循环''字母结束循环


''关闭记录集

lettersQuery.Close

comLetters.Cancel


我试图简化代码中的事物和名称，我可能会有一些错误，所以我没有抓到。关键是 - 代码有效 - 所发生的事情不是在第二个字母和后续字母中添加适当的注释，而是添加第一个字母的注释，就好像注释的第一个记录集仍处于活动状态一样。星（***）位于第二次循环不能正常工作的线下。查询正常工作并提取正确的信息。我已经尝试将记录集设置为空，没有结果。


我希望这有意义

I have an application that will produce a Word document based on five separate queries this has required that I create a ADODB connection:

''Create connection to current database
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection

There are five separate recordSets, one for each query. The record sets each fill out a table in the Word document, utilizing bookmarks. Some areas of the Word document have repeating tables. For example one of the tables has information about particular documents, like memos, letters. If there is to be more than one referenced document, the table repeats and is filled out based on the next record in the query. There is no problem with this.

It gets interesting when a separate query (and recordset) for the user notes is integrated. The memo or letter is described by these user notes to let the reader of the Word document know and understand the history being represented by the various letters and memos. Because of the required format of my Word document this requires a nested loop. See below code


''This repetition is for the record query and macro

''Variables for recordset in database, command for recordset
Dim lettersQuery As New ADODB.Recordset
Dim comLetters As New ADODB.Command

''Name of Access query
Dim lettersQuery As String
lettersQuery = "lettersQuery"

With comLetters
Set .ActiveConnection = Conn
.CommandText = "lettersQuery"
.CommandType = adCmdStoredProc
Set parameter = .CreateParameter("ReferenceNo", adVarChar, _
adParamInput, Len(RefNo), RefNo)
.Parameters.Append parameter
End With

lettersQuery.Open comLetters, , adOpenKeyset, adLockOptimistic

''Loop for letters
Do While Not lettersQuery.EOF

''Run macro in Word document
wordApp.Run "CreateLettersTable"

''Populate document bookmarks with query data
((code not relevant))

''Loop for notes

''Set letterNo variable equal to current record entry number
LetterNo = lettersQuery("LetterNo").Value

''Variables for recordset in database, command for recordset
Dim notesQuery As New ADODB.Recordset
Dim comNotes As New ADODB.Command

''Name of Access query
Dim notesQuery As String
notesQuery = "notesQuery"

With comNotes
Set .ActiveConnection = Conn
.CommandText = "notesQuery"
.CommandType = adCmdStoredProc
Set parameter = .CreateParameter("LetterNo", adVarChar, _
adParamInput, Len(LetterNo), LetterNo)
.Parameters.Append parameter
End With

notesQuery.Open comNotes, , adOpenKeyset, adLockOptimistic ************

''Loop for notes
Do While Not notesQuery.EOF

''Run macro in Word document
If notesQuery.AbsolutePosition > 1 Then
wordApp.Run "AddNoteLine"
End If

''Populate document bookmarks with query data
((code not relevant))

''Move to next record in data source
notesQuery.MoveNext

Loop ''End of notes loop.

''Close record set
notesQuery.Close
''Set notesQuery = Nothing
comNotes.Cancel

''Move to next record in data source
lettersQuery.MoveNext

Loop ''End of letters loop

''Close record set
lettersQuery.Close
comLetters.Cancel

I have tried to simplify the scenario and names of things in the code a little bit I may have so typos I didn?t catch. The point is - the code works - what happens is instead of adding the appropriate notes to the second and subsequent letters, it adds the notes for the first letter, it is as if the first recordset for the notes is still active. The stars (***) are under the line that is not working properly the second time through the loop. The query works correctly and pulls the correct information. I have tried setting the record set to nothing with no results.

I hope this makes sense

推荐答案

我''我很困惑你定义了一些变量两次（例如notesQuery）。

虽然我注意到工作代码是一样的。

我发现的唯一的事情是也许注释查询需要的不仅仅是一个参数集。

I''m confused that you define some variables twice (notesQuery for instance).
Though I notice it is the same for the working code.
The only thing that occurs to me is that perhaps the notes query needs more than the one parameter set.

这些是我害怕创建的一些错别字，在我的代码中它们只被定义一次，我打算输入notesQueryRs或类似记录集的东西。我的查询只有一个参数，字母表与notes表有一对多的关系，并且足以按字母数查询。相应的字母编号被传递给笔记查询，它只是不会放弃以前的结果。

Those are some of the typos I was afraid I had created, in my code they are only defined once, I intended to type notesQueryRs or something like it for the record set. My query only has one parameter, the letters table has a one to many relationship with the notes table and it is adequate to query by letter number. The appropriate letter number is passed to the notes query, it just won''t let go of the previous results.

我认为在这里发布实际测试的代码是个好主意。

我很欣赏剥离不相关的代码，但实际代码应该在发布之前清除错别字并进行编译。


我有点困惑它听起来好像你已经从你的第一篇帖子那样做了。

我怀疑你为了简洁而释义，这是一个不错的主意，但更重要的是代码是编译的代码。


并不是说我可以保证为你解决问题 - 它看起来很复杂 - 但至少我可以再看看。

顺便说一句，别忘了使用CODE标签 - 它保留了代码的格式;）。

I think it would be a good idea to post actual tested code here.
I appreciate the stripping out of irrelevant code, but the actual code should be cleared of typos and compiled before posting.

I''m a little confused as it did sound as if you had done that already from your first post.
I suspect you''re ''paraphrasing'' for brevity, which is a nice idea, but more important is that the code is compiled code.

Not that I can promise to solve your problem for you - it looks quite complicated - but at least I can have another look.
BTW don''t forget to use the CODE tags - it keeps the formatting of the code ;).

这篇关于5套记录让我发疯的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！