Counting the number of recordsin a table or query with code!!

[neemi]

Access 2000. Well the situation is this....

I have a table with some contact details in it, including the date that the they are suppose to be contacted.

I then have a query which looks up the contact details that are due to be contacted today or are overdue for a call. i.e the date stored in the table against their contact details are >= Date().

This query is the recordsource for a form accessed by a button named "Overdue calls". So for example when the user wants to know what calls they are due to make this form opens up with the details.

However if no calls are due then the form opens up anyway with no records. What I want is some way of counting the number of records in this query and if it is > 0 (i.e there are some calls due) then the form opens with the details. Otherwise the user gets a message saying something like there are no overdue calls.

Does anyone know how I can do this.

Help appreciated

Cheers

Neemi

 

[AWithers]

You may have to use a recordet and use the recordcount property, count the number of records in the query which drives the form before deciding whether to open the form or not

Andy

 

[neemi]

do you have some example code? to show how it works. I know there is a recordcount property but dont know how to set it up

 

[AWithers]

copied from help file access 97

Sub CountRecords()
Dim dbs As Database, rst As Recordset

' Return reference to current database.
Set dbs = CurrentDb
' Open table-type Recordset object.
Set rst = dbs.OpenRecordset("Orders&quot
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

 

[neemi]

this is the code that i am trying to use but I cant seem to apply it...

 

[AWithers]

Which bit isn't working?

if its the recordcount bit then you could instead use the eof property i.e when eof is true then no records then dont open form

Andy

 

[scking]

And of course there is the easy way. This counts the records in the Orders table and cancels the opening of the form if there are no records. It's quick, easier to program and a bit more elegant. I know you need to do a little bit more than the Cancel but this is only the snippet. And, of course, you can set criteria for it.

Form_OnOpen

If DCount("*", "Orders&quot = 0 Then
Cancel = True
End If

End Sub
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------