missing records from coded recordset when compared to qbe

[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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3", cnxn
linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub

[Rob Oldfield]

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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3", cnxn
linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub

[Randy Harris]

"Rob Oldfield" <bl**@blah.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3", cnxn linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub

[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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3",
cnxn
linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub

[Rob Oldfield]

(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
QryICTMassDistr ibution3 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
QryICTMassDistr ibution3... 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.p ol.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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3",
cnxn
linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub

[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.Connectio n
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_Co rrespondence", cnxn, adOpenKeyset,
adLockOptimisti c

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNe w
linktable.Field s("SchoolId") = rs.Fields("Scho olId")
linktable.Field s("Corespondenc eId") = Text5.Value
linktable.Updat e
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.co m> wrote in message
news:eJ******** ******@tk2msftn gp13.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
QryICTMassDistr ibution3 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
QryICTMassDistr ibution3... 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.p ol.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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
>> 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 QryICTMassDistr ibution3",
>> cnxn
>> linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
>> adLockOptimisti c
>> rs.MoveFirst
>> MsgBox "you are about to process " & rs!MatchCount & " records"
>> count = 0
>> While Not rs.EOF
>> count = count + 1
>> linktable.AddNe w
>> linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
>> linktable.Field s("Corespondenc eId") = Text5.Value
>> rs.MoveNext
>> Wend
>> rs.Close
>> MsgBox count & " records added to corrispondence table"
>>
>> Set rs = Nothing
>> linktable.Updat e
>> linktable.Close
>> Rem Set linktable = Nothing
>> End Sub
>>
>>
>>
>
>

[Douglas J. Steele]

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.p ol.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.Connectio n
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_Co rrespondence", cnxn, adOpenKeyset,
adLockOptimisti c

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNe w
linktable.Field s("SchoolId") = rs.Fields("Scho olId")
linktable.Field s("Corespondenc eId") = Text5.Value
linktable.Updat e
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.co m> wrote in message
news:eJ******** ******@tk2msftn gp13.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
QryICTMassDistr ibution3 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
QryICTMassDistr ibution3... 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.p ol.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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
>> 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 QryICTMassDistr ibution3",
>> cnxn
>> linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
>> adLockOptimisti c
>> rs.MoveFirst
>> MsgBox "you are about to process " & rs!MatchCount & " records"
>> count = 0
>> While Not rs.EOF
>> count = count + 1
>> linktable.AddNe w
>> linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
>> linktable.Field s("Corespondenc eId") = Text5.Value
>> rs.MoveNext
>> Wend
>> rs.Close
>> MsgBox count & " records added to corrispondence table"
>>
>> Set rs = Nothing
>> linktable.Updat e
>> linktable.Close
>> Rem Set linktable = Nothing
>> End Sub
>>
>>
>>
>
>

[Randy Harris]

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.p ol.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.Connectio n
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_Co rrespondence", cnxn, adOpenKeyset,
adLockOptimisti c

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNe w
linktable.Field s("SchoolId") = rs.Fields("Scho olId")
linktable.Field s("Corespondenc eId") = Text5.Value
linktable.Updat e
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.co m> wrote in message
news:eJ******** ******@tk2msftn gp13.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
QryICTMassDistr ibution3 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
QryICTMassDistr ibution3... 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.p ol.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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
>> 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

QryICTMassDistr ibution3", >> cnxn
>> linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
>> adLockOptimisti c
>> rs.MoveFirst
>> MsgBox "you are about to process " & rs!MatchCount & " records"
>> count = 0
>> While Not rs.EOF
>> count = count + 1
>> linktable.AddNe w
>> linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
>> linktable.Field s("Corespondenc eId") = Text5.Value
>> rs.MoveNext
>> Wend
>> rs.Close
>> MsgBox count & " records added to corrispondence table"
>>
>> Set rs = Nothing
>> linktable.Updat e
>> linktable.Close
>> Rem Set linktable = Nothing
>> End Sub
>>
>>
>>
>
>

[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.c om> wrote in message
news:eD******** ******@TK2MSFTN GP09.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.p ol.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.Connectio n
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_Co rrespondence", cnxn, adOpenKeyset,
adLockOptimisti c

rs.MoveFirst
MsgBox "you are about to process " & rs.RecordCount & " records"
While Not rs.EOF
linktable.AddNe w
linktable.Field s("SchoolId") = rs.Fields("Scho olId")
linktable.Field s("Corespondenc eId") = Text5.Value
linktable.Updat e
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
linktable.Close
End Sub

"Rob Oldfield" <bl**@blah.co m> wrote in message
news:eJ******** ******@tk2msftn gp13.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
> QryICTMassDistr ibution3 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
> QryICTMassDistr ibution3... 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.p ol.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.co m> wrote in message
>> news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
>> >> 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 QryICTMassDistr ibution3", >> >> cnxn
>> >> linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
>> >> adLockOptimisti c
>> >> rs.MoveFirst
>> >> MsgBox "you are about to process " & rs!MatchCount & " records"
>> >> count = 0
>> >> While Not rs.EOF
>> >> count = count + 1
>> >> linktable.AddNe w
>> >> linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
>> >> linktable.Field s("Corespondenc eId") = Text5.Value
>> >> rs.MoveNext
>> >> Wend
>> >> rs.Close
>> >> MsgBox count & " records added to corrispondence table"
>> >>
>> >> Set rs = Nothing
>> >> linktable.Updat e
>> >> linktable.Close
>> >> Rem Set linktable = Nothing
>> >> End Sub
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[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,mail shotID
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.co m> wrote in message
news:O8******** ******@TK2MSFTN GP10.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.po l.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: QryICTMassDistr ibution3) , 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 QryICTMassDistr ibution3 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.Connectio n
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 QryICTMassDistr ibution3",
cnxn
linktable.Open "TblCompanyId_C orrespondence", cnxn, adOpenKeyset,
adLockOptimisti c
rs.MoveFirst
MsgBox "you are about to process " & rs!MatchCount & " records"
count = 0
While Not rs.EOF
count = count + 1
linktable.AddNe w
linktable.Field s("CompanyId" ) = rs.Fields("Comp anyId")
linktable.Field s("Corespondenc eId") = Text5.Value
rs.MoveNext
Wend
rs.Close
MsgBox count & " records added to corrispondence table"

Set rs = Nothing
linktable.Updat e
linktable.Close
Rem Set linktable = Nothing
End Sub