EOF in VBA

[Samantha R via AccessMonster.com]

I am trying to use the EOF function but I don't really know how. My code is:

Public Function CountQuestions() As Integer
Dim recnum As Integer
recnum = 0
DoCmd.OpenTable ("tblqsts")
DoCmd.GoToRecord , , acFirst

Do Until (EOF = True) 'this is where I want to test if tblqsts reaches
the EOF
DoCmd.GoToRecord , , acNext
recnum = recnum + 1
Loop

DoCmd.Close acTable, "tblqsts"
CountQuestions = recnum
End Function

I have tried to do some recordset things but either I was doing it wrong or I
didn't have the correct references. Any help much appreciated.

 

[Nikos Yannacopoulos]

Samantha,

Kind of an overkill! Try this:

Public Function CountQuestions() As Integer
CountQuestions = CurrentDb.Tabledefs("tblqsts").Recordcount
End Function

EOF requires an object reference; for example, it could be used on a
recordset object, like:

Dim db As DAO.DAtabase
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblqsts")
rst.MoveFirst
Do Until rst.EOF
blah blah
Loop
rst.Close
Set rst = Nothing
Set db = Nothing

Again, it would be an overkill to cycle through all records just to
count them, all you would need would be:
CountQuestions = rst.RecordCount
and would only make sense to go this way if you needed to open the
recordset for additional processing.

HTH,
Nikos

 

[Samantha R via AccessMonster.com]

Lol, thank you so much! I didn't know that there was a count record function.
...thanks