Recordset Count - I give up

[dynamictiger]

I have the following code which is wrong:
Sub LastCount()

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb

With dbs
' Open dynaset-type Recordset and show RecordCount
' property before populating the Recordset.
Set rst = .OpenRecordset("SELECT Sum(qryResults.NoBuy) AS SumOf NoBuy FROM qryResults;", _
dbOpenDynaset)


' Show the RecordCount property after populating the
' Recordset.
rst.MoveLast
Debug.Print "Snapshot-type recordset " & _
"from Employees table after MoveLast"
Debug.Print " RecordCount = " & _
rst.RecordCount
rst.Close
.Close
End With

End Sub

The code does not run. I don't know why.

 

[mdthornton]

What error are you getting? Best Regards,
Mike

 

[dynamictiger]

The code only runs as far as the With dbs line then goes back to the calling sub???

 

[mdthornton]

Not sure but try removing the with & end With to start with its only helpful to you on 2 lines anyway.

Best Regards,
Mike

 

[dynamictiger]

I have tried all sorts of variations and it does not seem to help what I do. I have also tried various other combinations??

 

[dynamictiger]

I fibbed it is not running past:

Set rst = dbs.OpenRecordset("SELECT Sum(qryResults.NoBuy) AS SumOf NoBuy FROM qryResults;", _
dbOpenDynaset)

 

[mdthornton]

try putting a bracket around your as clause, i don't think
it will like the space in there, eg:

Set rst = dbs.OpenRecordset("SELECT Sum(qryResults.NoBuy) AS [SumOf NoBuy] FROM qryResults;", dbOpenDynaset)

or just get rid of it:

Set rst = dbs.OpenRecordset("SELECT Sum(qryResults.NoBuy) AS SumOfNoBuy FROM qryResults;", dbOpenDynaset) Best Regards,
Mike

 

[dterrie]

Try this:

MsgBox "My count is " & trim(dcount("[NoBuy]","qryResults&quot) & "."

 

[KyleS]

If you're still looking for the answer I have some input. Since you're not updating the recordset at all you don't need to open it as a dynaset. For some reason Dynasets don't return proper recordcounts (don't ask me why, I just know it doesn't work - someday I'll get curious and look it up) so take off the ", dbOpenDynaset" and your recordcount will work. Kyle :

 

[Quehay]

Hey, look at the SQL here:

You're asking for a SQL aggregate function with no grouping levels. There's no count to be had, this just returns one row!

 

[KyleS]

Quehay's got a point, since you're only bringing back a SUm() field, and no Group By, you will only return one field. Kyle :

 

[mdthornton]

Let's put an end to this thread... it appears "dynamictiger" figured it out or doesn't care anymore.... perhaps he/she would let us know.

Anyway, as I said earlier...

AS SumOf NoBuy - this is not going to work
AS SumOfNoBuy - this will work
AS [SumOf NoBuy] - this will work

It doesn't matter whether aggregate functions are used or not the record count will be 1 once the SQL is fixed. Best Regards,
Mike

 

[ljprivara]

I am still getting my bearings with using VBA in Access. However, yesterday I came up with a quick routine that requires counting the number of records returned in a sql stmt. Since it works, you might find it useful in debugging your code: (This routine does not display the results table, which I do not need to see.)

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String, getCUSIP As String
Dim countRecords As Integer

Set dbs = CurrentDb()
getCUSIP = Me![CUSIP] 'getting data off form which is used as criteria

strSQL = "SELECT * FROM TradeDetail WHERE TradeDetail.CUSIP = '" & getCUSIP & "';"
Set rst = dbs.OpenRecordset(strSQL)
countRecords = rst.RecordCount

If countRecords = 0 Then ' . . . open subsidiary form for CUSIP record

 

[dynamictiger]

Thank you all for your postings.

I must admit it became very confusing with all the different suggestions coming through in a mishmash.

However, it is now solved.