与qbe相比，编码记录集中缺少记录 [英] missing records from coded recordset when compared to qbe

问题描述

嗨


我有一个数据库，用于跟踪我们向公司发送的促销活动。


我通常根据数据库中公司的子集发送邮件

（使用查询：QryICTMassDistribution3找到），然后我使用表格和

以下代码在corrispondence表中创建一个新记录，以显示

已向各公司发送了什么相关信息。


然而，当我尝试时这对于我们最新的邮件来说，它仅适用于52个

348的记录。

QryICTMassDistribution3查询，链接多个表格，并返回348

正常运行但记录集下面的代码中的记录似乎只有

有52条记录。


有谁可以建议我哪里出错？


非常感谢

JM


代码：


Private Sub Command0_Click （）


Dim cnxn作为ADODB.C onnection

Dim rs As ADODB.Recordset

设置cnxn = CurrentProject.Connection

设置rs =新ADODB.Recordset

设置linktable =新ADODB.Recordset

Dim count As Integer

rs.Open" Select Count（*）As MatchCount FROM QryICTMassDistribution3"，cnxn

linktable.Open" TblCompanyId_Correspondence"，cnxn，adOpenKeyset，

adLockOptimistic

rs.MoveFirst

MsgBox"你是关于处理 &安培; rs！MatchCount& "记录

count = 0

而不是rs.EOF

count = count + 1

linktable.AddNew

linktable.Fields（" CompanyId"）= rs.Fields（" CompanyId"）

linktable.Fields（" CorespondenceId"）= Text5.Value

rs.MoveNext

Wend

rs.Close

MsgBox count& "记录添加到corrispondence表


设置rs =没有

linktable.Update

linktable.Close

Rem Set linktable = Nothing

End Sub

Hi

I have a database that I use to keep track of the sales promotions that we
send to companies.

I normally send a mailing based on a subset of the companies in the database
(found using the query: QryICTMassDistribution3) , I then use a form and
the code below to create a new record in the corrispondence table to show
what corrispondence has been sent to various companies.

However, when I tried this for our latest mailing it only worked for 52 of
348 the records.
The QryICTMassDistribution3 query, links several tables, and returns 348
records when run normally but in the code below the recordset only seems to
have 52 records.

Can anyone suggest where I have gone wrong?

Many thanks
JM

the code:

Private Sub Command0_Click()

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set linktable = New ADODB.Recordset
Dim count As Integer
rs.Open "Select Count(*) As MatchCount FROM QryICTMassDistribution3", cnxn
linktable.Open "TblCompanyId_Correspondence", cnxn, adOpenKeyset,
adLockOptimistic
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNew
linktable.Fields("CompanyId") = rs.Fields("CompanyId")
linktable.Fields("CorespondenceId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Update
linktable.Close
Rem Set linktable = Nothing
End Sub

推荐答案

说实话，我可以''看看它是如何做的。


rs.open将返回一个记录集，其中包含一个包含计数

值的记录。因此循环遍历该记录集只会循环一次。

.addnew需要一个相应的.update才能告诉链接表

记录集实际保留记录你已经添加。


您是否尝试过断点并遵循您的逻辑

通过？


为什么不只是使用追加查询？它会更容易。

" Jan" < JA ************* @ hotmail.com>在消息中写道

新闻：d3 ********** @ news8.svr.pol.co.uk ...

To be honest, I can''t see how it does anything at all.

The rs.open will return a recordset with a single record containing a count
value. Looping through that recordset will therefore loop just once.
The .addnew needs a corresponding .update in order to tell the linktable
recordset to actually retain the record you''ve added.

Have you tried dropping a breakpoint in there and following your logic
through?

Why not just use an append query? It''d be a great deal easier.
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news8.svr.pol.co.uk...

嗨

我有一个数据库，用于跟踪我们发送给公司的促销活动。

我通常根据公司的一部分发送邮件。
数据库（使用查询：QryICTMassDistribution3找到），然后我使用表格和下面的代码在corrispondence表中创建一个新记录，以显示已向各公司发送了什么相关信息。

然而，当我尝试使用我们最新的邮件时，它仅用于记录中的52个。
QryICTMassDistribution3查询，链接几个表，并返回348
记录正常运行但在记录集下面的代码中只有
才有52条记录。

任何人都可以建议我哪里出错了？

很多谢谢
JM

代码：

Private Sub Command0_Click（）

Dim cnxn作为ADOD B.Connection
Dim rs As ADODB.Recordset
设置cnxn = CurrentProject.Connection
设置rs =新ADODB.Recordset
设置linktable =新ADODB.Recordset
Dim算作整数

rs.Open" Select Count（*）As MatchCount FROM QryICTMassDistribution3"，cnxn
linktable.Open" TblCompanyId_Correspondence"，cnxn，adOpenKeyset，
adLockOptimistic
rs.MoveFirst
MsgBox你即将处理 &安培; rs！MatchCount& "记录
count = 0
而不是rs.EOF
count = count + 1
linktable.AddNew
linktable.Fields（" CompanyId"）= rs。 Fields（" CompanyId"）
linktable.Fields（" CorespondenceId"）= Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count& ; "添加到corrispondence表的记录

设置rs = Nothing
linktable.Update
linktable.Close
Rem Set linktable = Nothing
End Sub

" Rob Oldfield" < BL ** @ blah.com>在消息中写道

新闻：O8 ************** @ TK2MSFTNGP10.phx.gbl ...

"Rob Oldfield" <bl**@blah.com> wrote in message
news:O8**************@TK2MSFTNGP10.phx.gbl...

说实话，我根本无法看到它是如何做的。

rs.open将返回一个记录集，其中包含一个包含
计数值的记录。因此，循环播放该记录集只会循环一次。
.addnew需要一个相应的.update才能告诉linktable
记录集实际保留你添加的记录。

为什么不使用追加查询？这会更容易。


你知道吗，我很高兴你发布了这个。我之前看过OP的代码，

它没有任何意义。我认为这肯定是一些技巧，我不会理解。我无法弄清楚的是她如何得到52条记录

的代码。


" Jan" < JA ************* @ hotmail.com>在消息中写道
新闻：d3 ********** @ news8.svr.pol.co.uk ...

To be honest, I can''t see how it does anything at all.

The rs.open will return a recordset with a single record containing a count value. Looping through that recordset will therefore loop just once.
The .addnew needs a corresponding .update in order to tell the linktable
recordset to actually retain the record you''ve added.

Have you tried dropping a breakpoint in there and following your logic
through?

Why not just use an append query? It''d be a great deal easier.
You know, I''m glad you posted that. I looked at the OP''s code earlier and
it just didn''t make any sense. I thought it must be some technique that I
didn''t comprehend. What I couldn''t figure out is how she got 52 records out
of that code.

"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news8.svr.pol.co.uk...

嗨

我有一个数据库，用于跟踪我们发送给公司的
的促销活动。

我通常根据

Hi

I have a database that I use to keep track of the sales promotions that we send to companies.

I normally send a mailing based on a subset of the companies in the

中的一部分公司发送邮件
数据库

database

（使用查询找到：QryICTMassDistribution3），然后我使用表格
和下面的代码在corrispondence表中创建一个新记录到
show什么反应被发送到各个公司。

但是，当我尝试使用我们最新的邮件时，它仅适用于348美元的52
记录。
QryICTMassDistribution3查询，链接几个表，并在正常运行时返回
348条记录，但在记录集下方的代码中只有

(found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what corrispondence has been sent to various companies.

However, when I tried this for our latest mailing it only worked for 52 of 348 the records.
The QryICTMassDistribution3 query, links several tables, and returns 348 records when run normally but in the code below the recordset only seems

到

有52条记录。

非常感谢
JM

代码：

Private Sub Command0_Click（）

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
设置cnxn = CurrentProject .Connection
设置rs =新ADODB.Recordset
设置linktable =新ADODB.Recordset
Dim count As Integer

rs.Open" Select Count（*）作为MatchCount FROM QryICTMassDistribution3"，
cnxn linktable.Open" TblCompanyId_Correspondence"，cnxn，adOpenKeyset，
adLockOptimistic
rs.MoveFirst
MsgBox"您即将处理&＃ &安培; rs！MatchCount& "记录
count = 0
而不是rs.EOF
count = count + 1
linktable.AddNew
linktable.Fields（" CompanyId"）= rs。 Fields（" CompanyId"）
linktable.Fields（" CorespondenceId"）= Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count& ; "添加到corrispondence表的记录

设置rs = Nothing
linktable.Update
linktable.Close
Rem Set linktable = Nothing
End Sub

您好


感谢先前的评论。


你可能觉得在VBA编程并不是我的强项！


我不确定我理解你为什么说rs。开放只返回一个

记录？我实际上在过去使用过这段代码的不同查询

返回50到60条记录并且它已经有效。


如何让它返回所有记录？


我不能使用追加查询，因为关系太复杂而且

查询最终说''记录集不可更新'' 。因此我决定

最简单的方法是简单地循环查询中的记录并添加

他们的ID以及与新表的对应关系。


我在代码的末尾有一个.update，你是说它应该在

循环中使用.addnew？


非常感谢你的帮助

JM


" Rob Oldfield" < BL ** @ blah.com>在消息中写道

新闻：O8 ************** @ TK2MSFTNGP10.phx.gbl ...

Hi

Thanks for the previous comments.

As you may guess programming in VBA isn''t my strong point!

I''m not sure I understand why you are saying rs.open only returns one
record? I have actually used this code in the past with different queries
that return 50 to 60 records and it has worked.

How do I make it return all the records?

I cant use an append query as the relationships are too complex and the
query ends up saying ''record set is not updateable''. Hence I decided the
easiest way would be to simple loop through the records in the query and add
their ID together with a correspondence to a new table.

I have got a .update at the end of the code, are you saying it should be in
the loop with the .addnew?

Many thanks for the help
JM

"Rob Oldfield" <bl**@blah.com> wrote in message
news:O8**************@TK2MSFTNGP10.phx.gbl...

说实话，我根本无法看到它是如何做的。

rs.open将返回一个记录集，其中包含一个包含
count
值的记录。因此，循环播放该记录集只会循环一次。
.addnew需要一个相应的.update才能告诉linktable
记录集实际保留你添加的记录。

为什么不使用追加查询？它会更容易。

" Jan" < JA ************* @ hotmail.com>在消息中写道
新闻：d3 ********** @ news8.svr.pol.co.uk ...

To be honest, I can''t see how it does anything at all.

The rs.open will return a recordset with a single record containing a
count
value. Looping through that recordset will therefore loop just once.
The .addnew needs a corresponding .update in order to tell the linktable
recordset to actually retain the record you''ve added.

Have you tried dropping a breakpoint in there and following your logic
through?

Why not just use an append query? It''d be a great deal easier.
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news8.svr.pol.co.uk...

嗨

我有一个数据库，用于跟踪我们发送给公司的促销活动。

我通常根据公司的一部分发送邮件。

Hi

I have a database that I use to keep track of the sales promotions that
we
send to companies.

I normally send a mailing based on a subset of the companies in the

数据库

（使用查询找到：QryICTMassDistribution3），然后我使用表格和下面的代码在corrispondence表中创建一个新记录来显示
已经向各公司发送了什么相关信息。

然而，当我尝试使用我们最新的邮件时，它只能用于记录。 br /> QryICTMassDistribution3查询，链接几个表，并在正常运行时返回348
记录，但在记录集下面的代码中只有

(found using the query: QryICTMassDistribution3) , I then use a form and
the code below to create a new record in the corrispondence table to show
what corrispondence has been sent to various companies.

However, when I tried this for our latest mailing it only worked for 52
of
348 the records.
The QryICTMassDistribution3 query, links several tables, and returns 348
records when run normally but in the code below the recordset only seems

到

有52个记录。

任何人都可以建议我哪里出错了？

非常感谢
代码：

Private Sub Command0_Click（）

Dim cnxn As ADODB.Connection
Dim rs as ADODB .Recordset
设置cnxn = CurrentProject.Connection
设置rs =新ADODB.Recordset
设置linktable =新ADODB.Recordset
Dim count As Integer

rs.OpenSelect Count（*）As MatchCount FROM QryICTMassDistribution3，
cnxn
linktable.OpenTblCompanyId_Correspondence，cnxn，adOpenKeyset，
adLockOptimistic
rs.MoveFirst < MsgBox你即将处理 &安培; rs！MatchCount& "记录
count = 0
而不是rs.EOF
count = count + 1
linktable.AddNew
linktable.Fields（" CompanyId"）= rs。 Fields（" CompanyId"）
linktable.Fields（" CorespondenceId"）= Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count& ; "添加到corrispondence表的记录

设置rs = Nothing
linktable.Update
linktable.Close
Rem Set linktable = Nothing
End Sub

这篇关于与qbe相比，编码记录集中缺少记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！