RECORDCOUNT not working (1 Viewer)

[gary.newport]

I have a table called tbl_courses. This currently has 3 complete records in it (3 courses).

I have a form with a textbox; the textbox is called txt_course_total.

I want the textbox to show the total number of courses currently stored.

I wrote the following:

    Dim rst_courses As Recordset

    Set rst_courses = CurrentDb.OpenRecordset("SELECT * FROM tbl_course")
    
    txt_course_total.Value = rst_courses.RecordCount

I am expecting 3 but I get 1. Not -1 but 1.

Why?

 

[namliam]

Access is doing JIT, it will only fetch records you actually need at the time you need them.

If you want the count of your table, you either do
Select count(*) from tbl_Course
or ....
Dcount("*", "tbl_Course")
Or ...

....
Set rst_courses = CurrentDb.OpenRecordset("SELECT * FROM tbl_course")
rst_courses.movelast
txt_course_total.Value = rst_courses.RecordCount
rst_courses.movefirst
....

happy hunting

 

[gary.newport]

And that works perfectly!

I like the DCOUNT since this reduces the code down to a single line:

txt_course_total.Value = DCount("*", "tbl_course")

Which seems to be time and memory efficient.

Is there something I am missing about the others?

 

[namliam]

Yes you are, DCount only does that count and only that... As soon as you need just that little bit more you cant and the other two methods win out...

Also if you need the full recordset, you lose nothing with "Movelast/first"
Where as if you use dcount you effectively get a refetch of your data on a seperate query.
Particularly if your recordset is big or if you have a rather complex query running it 2 times instead of one time only has its obvious drawbacks.

Dcounts and DLookups in particular are the devil and should be used sparingly ONLY in places where it makes proper sence to use them. Generaly they get WAY overused ....
i.e. using dcount 4 times instead of
Select quarter, count(*) from table... where ... group by quarter
and looping the recordset for the four records, if you get what I mean.

 

[gary.newport]

I do indeed and in fact began to rethink when I realised that I need the results of the query to do more. So the movelast, movefirst wins this time!

Thank you so much for the speed and response.