Recordcount Problem

[Denis Bisson via AccessMonster.com]

Good day,

A2K on a Win2K platform

My objective is to have a pop-up form appear when the database opens, ONLY
if there are one or more records that result from an SQL statement. At the
moment, I am substituting the pop-up form with MsgBoxes – only for testing
purposes.

The function below is called from an “AutoExec” macro and partially works…
The IF RecordCount only returns a value of “1” even though the number of
records should be higher (for testing purposes I should see “3” records
instead of only one.

What am I missing?

Thank you

Denis



Public Function LinkChk()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb


strSQL = "SELECT Register.ID, Register.QDCN, Register.[Title of document]
, " _
& "Register.TentativeAuditDate, Register.LinksCheckedDate " _
& "FROM Register " _
& "WHERE (((Register.TentativeAuditDate)<Now() OR
(Register.TentativeAuditDate)<Now()+ 300) AND ((Register.LinksCheckedDate)
Is Null));"

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
MsgBox "There is/are " & rst.RecordCount & " product(s) scheduled for
an audit requiring links to be checked"

Else
MsgBox "No products requiring link check"
End If
Debug.Print rst.RecordCount

Set dbs = Nothing
End Function

 

[Larry]

Try moving to the last record in the recordset before checking the record
count (rst.movelast)

 

[Denis Bisson via AccessMonster.com]

Thank you Larry. Works perfectly!

Denis

 

[Larry]

My pleasure!

 

[John Spencer (MVP)]

You could do this a couple of different ways. One would be to use the DCOUNT
Function. Another would be to use sql that would return the record count in one record.

IF DCOUNT("*","Register","TentativeAuditDate<Now() OR
(TentativeAuditDate<Now()+ 300 AND Register.LinksCheckedDate Is Null)") THEN

OR CHANGE THE SQL TO

"SELECT COUNT(*) FROM REGISTER WHERE ..."

Set rst = dbs.OpenRecordset(strSQL)

MsgBox "There are " & rst.Fields(0) & " Products ... "

 

[Denis Bisson via AccessMonster.com]

Hi John,
Thanks for your reply. I ommitted to say (my apologies) that the pop-up
form would actually contain a listbox listing each record resulting from
the SQL statement.

I was looking for a way to determine if at least 'one' record existed
before the pop-up form popped up
Cheers!

Denis