Getting RecordCount [Resolved] (almost)

[dglienna]

In my query, i was using .recordcount to set the records. now I wanted to get a count in a msgbox, so I printed it, but found out that it was always -1.
Is there a way to get the count accurately, or do I have to do a separate query?

[RobDog888]

The -1 tells you that your current cursor type, lock type, etc do not support the
recordcount property. How are you opening your rs?

[dglienna]

With oDoc.MailMerge
        .MainDocumentType = wdMailingLabels
        .OpenDataSource Name:=App.Path & "\MyData.mdb", LinkToSource:=True, Connection:="TABLE MyTemp", _
         SQLStatement:="Select [First Name], [Last Name], [Mailing Address], City, State, Zip From [MyTemp]"
    End With
    With oDoc.MailMerge.DataSource
        .FirstRecord = 1
        .LastRecord = .RecordCount
        RecCount = .RecordCount
    End With

[RobDog888]

Use the .RecordCount in your With block and save the value to a variable. Then you can use it
later on in your msgbox.

[dglienna]

.RecordCount says "Method or Data Method not found"

I placed it below the sql statement.

RecCount = .RecordCount

EDIT: .Datasource.Record produced the same -1

[RobDog888]

But doesnt it work for the .LastRecord = .RecordCount?

[dglienna]

nope. just works with the -1

[RobDog888]

It works for mine so I double checked in the help file and this its what I found.

If Microsoft Word cannot determine the number of records in a data source, the RecordCount property will return a value of -1

So it may depend on the way datasource was setup or if its a complex query???
What is your datasource? .odc?

[szlamany]

I've used this very non-standard trick at times to arrive at a recordcount - if the number of rows in the table is low - it won't matter that this is a ugly query...

Select [First Name], [Last Name], [Mailing Address], City, State, Zip
,(Select Count(*) From [My Temp]) From [MyTemp]

Thus getting the recordcount as a column in the recordset itself...

[dglienna]

It actually works for the complex query that isn't part of the mailmerge. (the one that sends email or printer output) I guess that I'll have to do a separate query to get the count. That's what I was trying to avioid if it wasn't necessary.

Thanks

[szlamany]

If you are going to go the separate query route, then my technique guarantees that the recordcount will truly match the row count in the "data" recordset. If you do two separate queries, then you could run the risk of row counts changing during the operation. We would have that happen in our applications - they load lots of rows in a given day!

[dglienna]

How do I refer to the Count() field in my RS ? I forgot that I had already done it with the complex query. Now I need it for the simple query. If I can add the count like szlammy said, I'd like to do that.

EDIT: I have this

rs.Open "Delete TempTable .* from TempTable ", cn, adOpenKeyset, adLockOptimistic, adCmdText
 
  rs.Open "INSERT INTO TempTable (RecCount) " & _
     "SELECT  Count(*)" & _
     "From FarmingTemp", cn, adOpenKeyset, adLockOptimistic, adCmdText
  RecCount = rs.Fields(1)

So how do I get it back out. I've also tried RecCount = rs.fields("RecCount") but that returns an error.

[dglienna]

I think I figured it out:

rs.Open "select RecCount from TempTable", cn, adOpenKeyset, adLockOptimistic, adCmdText
 
  RecCount = rs.Fields("RecCount")

[dglienna]

Still like to know how to use the example that slammy suggested.

[szlamany]

Still like to know how to use the example that slammy suggested.

I am not familiar with the WORD object you are using - it appears that you don't have access to the RS from the code you posted...

Where is the execute or open of the recordset?

[dglienna]

word takes care of it all.

it I do a separate query, how do I access the count()? Where is it placed?
The only way I could get a count is my later code that placed it into a seperate field in a new table. If I use it like you did? How do I retrieve it?

I think that I see that you are putting it into an extra field in the table? I'm not sure, though.

[szlamany]

rs.Open "SELECT  Count(*) From FarmingTemp" _
    , cn, adOpenKeyset, adLockOptimistic, adCmdText
RecCount = rs.Fields(0)

OR

rs.Open "SELECT  Count(*) "RecCount" From FarmingTemp" _
    , cn, adOpenKeyset, adLockOptimistic, adCmdText
RecCount = rs.Fields("RecCount")

[dglienna]

No, I meant from THIS CODE:

Select [First Name], [Last Name], [Mailing Address], City, State, Zip
,(Select Count(*) From [My Temp]) From [MyTemp]

[szlamany]

Actually - I didn't mean to leave the FROM as FARMINGTEMP - the FROM should be the real table you are getting this data from.

There is no need for a temporary table in this example.

[szlamany]

No, I meant from THIS CODE:

Select [First Name], [Last Name], [Mailing Address], City, State, Zip
,(Select Count(*) From [My Temp]) From [MyTemp]

In that code it's the 6th column in the recordset - but I don't see from that WORD object code you showed above where you actually get the RECORDSET in the VB side of things...

[dglienna]

Does it store it is every record, or the last one?
When I query a recordset, does it use the last one if there is more than one?
If I run a query again, and the old count was in there, and I added the new count, when I do a query to select *, would that use the second (last) one?

I think I'm missing something concerning RS vs flatfile. In a flat file, I can go to record two to see all new info. How does this relate?

[szlamany]

Does it store it is every record, or the last one?
When I query a recordset, does it use the last one if there is more than one?
If I run a query again, and the old count was in there, and I added the new count, when I do a query to select *, would that use the second (last) one?

I think I'm missing something concerning RS vs flatfile. In a flat file, I can go to record two to see all new info. How does this relate?

Yes - it really stores the value in each and every row - that's why I said it was non-conformist (I've been accused of that before )

When you run the query again, it's a freshly calculated sub-query value.

[dglienna]

My RecCount had about 8 records in it, from each time I ran, and put in 1 record. I ran a query 8 times. The table had only the count field in it.

I just started deleting everything each time I ran the query, but I still wanted ask.

I added RecCount to TempTable, without deleting the values each time.

When i ran the query, would it always return the last record/field?

[szlamany]

I'm not understanding what you mean by ran it about 8 times and what you mean by deleting everything.

Are you creating a TEMP TABLE with the rows you want to process? Could you show some real code so I can grasp this a bit better?

I'm not a VBA person - so maybe I'm confusing something in regard to that.

[dglienna]

I just used count() to get a record count, and put it into a table.
Here:

rs.Open "INSERT INTO TempTable (RecCount) " & _
     "SELECT  Count(*)" & _
     "From FarmingTemp", cn, adOpenKeyset, adLockOptimistic, adCmdText

After I ran it, I had 8 records in the table, each with a different number of records in it. Then, I was getting the number from the table:

rs.Open "select RecCount from TempTable", cn, adOpenKeyset, adLockOptimistic, adCmdText

I wanted to know what this was returning? Was it the last record, or the first?
I just wanted to know a general rule of thumb as to how it all worked.

Now, all is good, as I delete all records before I write any, so the problem won't arise again. I just wonder what would come up if I didn't delete all of the records each time.

Was it the first record, or the last record?

[RobDog888]

The "SELECT Count(*)" part gets you the number of records in the FarmingTemp table. So you
should have 8 in your RecCount field for all 8 records.

[dglienna]

Hmm. That makes sense, but I thought they were different numbers, and selecting the count only added one record. I'm going to do it again to see exactly what is happening. Thanks.