Coding problem, query return 0 results

[Phil4tektips]

The code below displays the number of items that a user has open in the database on the front screen when they login.

I pick up their username on form open, and use it in a query to find the number of their items. The following code is a subroutine that is called to write to a couple of text boxes on the form the required information. The two record sets are the queries.

It all works fine, unless a user has no Open records. In other words the query returns blank. i tried an If statement with NULL, but this has failed. What should my condition be? If Error then dislay "0"??

Any help would be much appreciated!

Code:

Private Sub Counter()
Dim dbs As DAO.Database, rst3 As DAO.Recordset, rst4 As DAO.Recordset
Set dbs = CurrentDb()
Set rst3 = dbs.OpenRecordset("Number of Users Opens")
Set rst4 = dbs.OpenRecordset("Number of Opens")

With rst3
If (![Count] = Null) Then
[PersonalOpenCounter] = "0"
Else
[PersonalOpenCounter] = ![Count]
End If
End With

With rst4
[OpenCounter] = ![Count]
End With

Set dbs = Nothing: Set rst3 = Nothing: Set rst4 = Nothing

End Sub

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

The error that comes up is due to the RecordSet being empty and highlights this line of code:

If (![Count] = Null) Then

thanks,

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

I've solved it (yay!!) with:

Private Sub Counter()
Dim dbs As DAO.Database, rst3 As DAO.Recordset, rst4 As DAO.Recordset
Set dbs = CurrentDb()
Set rst3 = dbs.OpenRecordset("Number of Users Opens")
Set rst4 = dbs.OpenRecordset("Number of Opens")

If (rst3.BOF = True) Then
[PersonalOpenCounter] = "0"
Else
With rst3
[PersonalOpenCounter] = ![Count]
End With
End If

With rst4
[OpenCounter] = ![Count]
End With

Set dbs = Nothing: Set rst3 = Nothing: Set rst4 = Nothing

End Sub


~Phil4tektips~
Grant us peace in our days work!