By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

missing records from coded recordset when compared to qbe

P: n/a
Jan
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

Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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 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

Nov 13 '05 #2

P: n/a
"Rob Oldfield" <bl**@blah.com> wrote in message
news:O8**************@TK2MSFTNGP10.phx.gbl...
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
(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


Nov 13 '05 #3

P: n/a
Jan
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...
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

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


Nov 13 '05 #4

P: n/a
(apologies for accidentally mailing this to your reply address)

I'm afraid that whatever code you have that is working, it's fundamentally
different from this code.

If you take the SQL statement Select Count(*) As MatchCount FROM
QryICTMassDistribution3 and paste it into the SQL window of a query, and
then switch to the datasheet, then you'll get a single record, single field
display showing you how many records you have.

You'd be better off just replacing that with Select * FROM
QryICTMassDistribution3... that will then give you a loop that works.
You'll be able to use rs.recordcount to return the number of records
returned for your message.

On the .update question then yes, you should have it run inside the loop as
you suggest.

And regarding the idea to use an append query, you should be appending on to
a table - ensuring that the values you're writing into foreign key fields
are correct. That would get around the recordset not being updatable.

"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg2.svr.pol.co.uk...
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...
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

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



Nov 13 '05 #5

P: n/a
Jan
Hiya

What you described is what I had originally! I had added the matchcount as
..recordcount only returns 1! Reading on the net it was suggested that there
is some timing issues and matchcount was recomended, i didnt realise that
would only return one record! :(

I have changed my code back to the original as shown below.

I now get told it will process 1 record, but it then processes 52! There is
still a difference between the number of records QryMassICTDist5 returns
when I put Select * FROM QryMassICTDist5 in an SQL view and then view the
records returned i get 345!!!!!

I appreciate your help :)

I now have:
rivate 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

rs.Open "Select * FROM QryMassICTDist5", cnxn
linktable.Open "TblSchoolId_Correspondence", cnxn, adOpenKeyset,
adLockOptimistic

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNew
linktable.Fields("SchoolId") = rs.Fields("SchoolId")
linktable.Fields("CorespondenceId") = Text5.Value
linktable.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...
(apologies for accidentally mailing this to your reply address)

I'm afraid that whatever code you have that is working, it's fundamentally
different from this code.

If you take the SQL statement Select Count(*) As MatchCount FROM
QryICTMassDistribution3 and paste it into the SQL window of a query, and
then switch to the datasheet, then you'll get a single record, single
field
display showing you how many records you have.

You'd be better off just replacing that with Select * FROM
QryICTMassDistribution3... that will then give you a loop that works.
You'll be able to use rs.recordcount to return the number of records
returned for your message.

On the .update question then yes, you should have it run inside the loop
as
you suggest.

And regarding the idea to use an append query, you should be appending on
to
a table - ensuring that the values you're writing into foreign key fields
are correct. That would get around the recordset not being updatable.

"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg2.svr.pol.co.uk...
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...
> 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
> 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
>>
>>
>>
>
>



Nov 13 '05 #6

P: n/a
RecordCount isn't accurate until you've read all of the records in the
recordset.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

What you described is what I had originally! I had added the matchcount as
.recordcount only returns 1! Reading on the net it was suggested that
there is some timing issues and matchcount was recomended, i didnt realise
that would only return one record! :(

I have changed my code back to the original as shown below.

I now get told it will process 1 record, but it then processes 52! There
is still a difference between the number of records QryMassICTDist5
returns when I put Select * FROM QryMassICTDist5 in an SQL view and then
view the records returned i get 345!!!!!

I appreciate your help :)

I now have:
rivate 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

rs.Open "Select * FROM QryMassICTDist5", cnxn
linktable.Open "TblSchoolId_Correspondence", cnxn, adOpenKeyset,
adLockOptimistic

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNew
linktable.Fields("SchoolId") = rs.Fields("SchoolId")
linktable.Fields("CorespondenceId") = Text5.Value
linktable.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...
(apologies for accidentally mailing this to your reply address)

I'm afraid that whatever code you have that is working, it's
fundamentally
different from this code.

If you take the SQL statement Select Count(*) As MatchCount FROM
QryICTMassDistribution3 and paste it into the SQL window of a query, and
then switch to the datasheet, then you'll get a single record, single
field
display showing you how many records you have.

You'd be better off just replacing that with Select * FROM
QryICTMassDistribution3... that will then give you a loop that works.
You'll be able to use rs.recordcount to return the number of records
returned for your message.

On the .update question then yes, you should have it run inside the loop
as
you suggest.

And regarding the idea to use an append query, you should be appending on
to
a table - ensuring that the values you're writing into foreign key fields
are correct. That would get around the recordset not being updatable.

"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg2.svr.pol.co.uk...
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...
> 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
> 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
>>
>>
>>
>
>



Nov 13 '05 #7

P: n/a
ADO uses a forward only cursor for recordsets by default.
change:
rs.Open "Select * FROM QryMassICTDist5", cnxn
to:
rs.Open "Select * FROM QryMassICTDist5", cnxn, adOpenKeyset

Then you will at least get a recordcount. I don't know why the discrepancy
in the number of records processed, but perhaps getting a recordcount might
give you a clue.

--
Randy Harris
(tech at promail dot com)
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg1.svr.pol.co.uk...
Hiya

What you described is what I had originally! I had added the matchcount as
.recordcount only returns 1! Reading on the net it was suggested that there is some timing issues and matchcount was recomended, i didnt realise that
would only return one record! :(

I have changed my code back to the original as shown below.

I now get told it will process 1 record, but it then processes 52! There is still a difference between the number of records QryMassICTDist5 returns
when I put Select * FROM QryMassICTDist5 in an SQL view and then view the
records returned i get 345!!!!!

I appreciate your help :)

I now have:
rivate 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

rs.Open "Select * FROM QryMassICTDist5", cnxn
linktable.Open "TblSchoolId_Correspondence", cnxn, adOpenKeyset,
adLockOptimistic

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNew
linktable.Fields("SchoolId") = rs.Fields("SchoolId")
linktable.Fields("CorespondenceId") = Text5.Value
linktable.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...
(apologies for accidentally mailing this to your reply address)

I'm afraid that whatever code you have that is working, it's fundamentally different from this code.

If you take the SQL statement Select Count(*) As MatchCount FROM
QryICTMassDistribution3 and paste it into the SQL window of a query, and
then switch to the datasheet, then you'll get a single record, single
field
display showing you how many records you have.

You'd be better off just replacing that with Select * FROM
QryICTMassDistribution3... that will then give you a loop that works.
You'll be able to use rs.recordcount to return the number of records
returned for your message.

On the .update question then yes, you should have it run inside the loop
as
you suggest.

And regarding the idea to use an append query, you should be appending on to
a table - ensuring that the values you're writing into foreign key fields are correct. That would get around the recordset not being updatable.

"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg2.svr.pol.co.uk...
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...
> 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
> 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
>>
>>
>>
>
>



Nov 13 '05 #8

P: n/a
Jan
Thank you for this.
I now get a recordcount of 52.
Can anyone suggest why there is this discrepancy between the recordcount
compared to the qbe?
Many thanks
Jan

"Randy Harris" <ra***@nospam.com> wrote in message
news:eD**************@TK2MSFTNGP09.phx.gbl...
ADO uses a forward only cursor for recordsets by default.
change:
rs.Open "Select * FROM QryMassICTDist5", cnxn
to:
rs.Open "Select * FROM QryMassICTDist5", cnxn, adOpenKeyset

Then you will at least get a recordcount. I don't know why the
discrepancy
in the number of records processed, but perhaps getting a recordcount
might
give you a clue.

--
Randy Harris
(tech at promail dot com)
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg1.svr.pol.co.uk...
Hiya

What you described is what I had originally! I had added the matchcount
as
.recordcount only returns 1! Reading on the net it was suggested that

there
is some timing issues and matchcount was recomended, i didnt realise that
would only return one record! :(

I have changed my code back to the original as shown below.

I now get told it will process 1 record, but it then processes 52! There

is
still a difference between the number of records QryMassICTDist5 returns
when I put Select * FROM QryMassICTDist5 in an SQL view and then view the
records returned i get 345!!!!!

I appreciate your help :)

I now have:
rivate 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

rs.Open "Select * FROM QryMassICTDist5", cnxn
linktable.Open "TblSchoolId_Correspondence", cnxn, adOpenKeyset,
adLockOptimistic

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNew
linktable.Fields("SchoolId") = rs.Fields("SchoolId")
linktable.Fields("CorespondenceId") = Text5.Value
linktable.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.com> wrote in message
news:eJ**************@tk2msftngp13.phx.gbl...
> (apologies for accidentally mailing this to your reply address)
>
> I'm afraid that whatever code you have that is working, it's fundamentally > different from this code.
>
> If you take the SQL statement Select Count(*) As MatchCount FROM
> QryICTMassDistribution3 and paste it into the SQL window of a query,
> and
> then switch to the datasheet, then you'll get a single record, single
> field
> display showing you how many records you have.
>
> You'd be better off just replacing that with Select * FROM
> QryICTMassDistribution3... that will then give you a loop that works.
> You'll be able to use rs.recordcount to return the number of records
> returned for your message.
>
> On the .update question then yes, you should have it run inside the
> loop
> as
> you suggest.
>
> And regarding the idea to use an append query, you should be appending on > to
> a table - ensuring that the values you're writing into foreign key fields > are correct. That would get around the recordset not being updatable.
>
>
>
> "Jan" <Ja*************@hotmail.com> wrote in message
> news:d3**********@newsg2.svr.pol.co.uk...
>> 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...
>> > 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
>> > 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>



Nov 13 '05 #9

P: n/a
Jan
Hi

Have been looking at the append query. Not sure this meets our need even if
I can overcome the non updatable recordset problem. From my understanding;
an append query adds records from one table into another. What we need to do
is to add the ID's from a query into another table along with some fixed
data. The fixed data being the ID of the correspondence being sent. So for
example we might have a query that returns customers: 3, 7, 10,11,15 that
have been sent mailshot type 17, we would thus want in our link table new
records with:
customerID,mailshotID
3,17
7,17
10,17
11,17
15,17

The only way I can think of doing this is via code.

JD
"Rob Oldfield" <bl**@blah.com> wrote in message
news:O8**************@TK2MSFTNGP10.phx.gbl...
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

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


Nov 13 '05 #10

P: n/a
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news6.svr.pol.co.uk...
Thank you for this.
I now get a recordcount of 52.
Can anyone suggest why there is this discrepancy between the recordcount
compared to the qbe?
Many thanks
Jan


Jan, it sure makes no sense that I can think of. Are you certain that you
are looking at the same query in the query grid? The only reason I suggest
that possibility is that, in your code you are selecting from
"QryMassICTDist5", but in your initial post you mention
"QryICTMassDistribution3".

Randy

Nov 13 '05 #11

P: n/a

"Randy Harris" <ra***@nospam.com> wrote in message
news:O8**************@tk2msftngp13.phx.gbl...
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news6.svr.pol.co.uk...
Thank you for this.
I now get a recordcount of 52.
Can anyone suggest why there is this discrepancy between the recordcount
compared to the qbe?
Many thanks
Jan
Jan, it sure makes no sense that I can think of. Are you certain that you
are looking at the same query in the query grid? The only reason I

suggest that possibility is that, in your code you are selecting from
"QryMassICTDist5", but in your initial post you mention
"QryICTMassDistribution3".

Randy


Agreed that'd be worth checking.
Nov 13 '05 #12

P: n/a
Jan
Hiya

Yes it the same query, I have re-written (hence the change in name) it to
include extra fields, to make it almost identical to a query that worked
fine.

I also tried posting the actual SQL statement that made
QryMassICTDist5/QryICTMassDistribution3, again running in code I only get 52
records. However, if I make a sql query using the sql view of qbe, and use
'Select * FROM QryMassICTDist5' I get the full 340 ish records.

Could it be something wrong in the way I have access set up? Do I need to do
anything to use adodb commands? I have searched the net for similar problems
and found nothing :(

thank you

JM


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

"Randy Harris" <ra***@nospam.com> wrote in message
news:O8**************@tk2msftngp13.phx.gbl...
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news6.svr.pol.co.uk...
> Thank you for this.
> I now get a recordcount of 52.
> Can anyone suggest why there is this discrepancy between the
> recordcount
> compared to the qbe?
> Many thanks
> Jan


Jan, it sure makes no sense that I can think of. Are you certain that
you
are looking at the same query in the query grid? The only reason I

suggest
that possibility is that, in your code you are selecting from
"QryMassICTDist5", but in your initial post you mention
"QryICTMassDistribution3".

Randy


Agreed that'd be worth checking.

Nov 13 '05 #13

P: n/a
Strange. Two things to try...

Drop a count into the loop to see how many times it actually loops.
Probably worth also setting a break point in there so you can see what route
the code is taking.

If that doesn't show anything up... can you look at the 52 records that are
added... is there anything that they have that the other ones don't?
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@newsg1.svr.pol.co.uk...
Hiya

Yes it the same query, I have re-written (hence the change in name) it to
include extra fields, to make it almost identical to a query that worked
fine.

I also tried posting the actual SQL statement that made
QryMassICTDist5/QryICTMassDistribution3, again running in code I only get 52 records. However, if I make a sql query using the sql view of qbe, and use
'Select * FROM QryMassICTDist5' I get the full 340 ish records.

Could it be something wrong in the way I have access set up? Do I need to do anything to use adodb commands? I have searched the net for similar problems and found nothing :(

thank you

JM


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

"Randy Harris" <ra***@nospam.com> wrote in message
news:O8**************@tk2msftngp13.phx.gbl...
"Jan" <Ja*************@hotmail.com> wrote in message
news:d3**********@news6.svr.pol.co.uk...
> Thank you for this.
> I now get a recordcount of 52.
> Can anyone suggest why there is this discrepancy between the
> recordcount
> compared to the qbe?
> Many thanks
> Jan

Jan, it sure makes no sense that I can think of. Are you certain that
you
are looking at the same query in the query grid? The only reason I

suggest
that possibility is that, in your code you are selecting from
"QryMassICTDist5", but in your initial post you mention
"QryICTMassDistribution3".

Randy


Agreed that'd be worth checking.


Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.