Recordsource Count

[TonyT]

continuing from a previous post that i thought had answered my question, but
proved to be erratic in use;

I have code in the On_Open event of a form setting the recordsource for that
form;

If lngCRefNum > 0 Then
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
End if

This all works fine, but, I am then trying to count the number of records
this may include before the form becomes 'active' so i can make various
changes to how it displays etc. sometimes the code;

Me.Recordset.MoveLast
Me.Recordset.MoveFirst
lngRecCount = Me.Recordset.RecordCount

returns the correct value, but mostly returns the error 'No Current Record'
as it appears to be trying to count before the code above has actually
populated the RecordSource regardless of where I put each piece of code.

Where should i be putting the recordCount code in the form's procedures? Or
should i be using different methods?

(ps for future reference, if I mark a post response as 'helpful', do the
respondents usually ignore it from then on as completed?)

 

[Brendan Reynolds]

Your existing code may work more reliably if you move it from the Open to
the Load event. Alternatively, you could do something like this, which
should work in either event ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngRecords As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Count(*) As Records FROM tblCont WHERE
ContCRefNum = " & lngCRefNum & "'")
lngRecords = rst.Fields("Records")
rst.Close

The variable lngRecords now contains the number of records.

The 'mark the post as helpful' thing is a feature of Microsoft's web-based
interface to the newsgroups. Those of us who access the newsgroups using a
news-reader program such as Outlook Express never see it.