Getting RecordCount [Resolved] (almost)

[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")