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: 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

 

[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.

 

[Randy Harris]

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]

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]

(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]

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

 

[Douglas J. Steele]

RecordCount isn't accurate until you've read all of the records in the
recordset.

 

[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.

 

[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

 

[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

 

[Randy Harris]

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

 

[Rob Oldfield]

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.

 

[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]

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?