If Query is empty (1 Viewer)

[Xenix]

I have a problem, hope someone can help.
I have the following code:


If Forms![stores].RecordCount = 0 Then
MsgBox "There are no Jobs to complete yet", vbOKOnly
Else
If Me!Printme = "PRINTED" Then
Me!Box38.BackColor = 255
' Otherwise, display as a green box.
Else
Me!Box38.BackColor = 65280
End If
Forms!stores!TrayDetailsSub2.SetFocus
End If

Maybe you can see the problem here. When the form opens it
sets the colour of printed box to green or red the problem is if I open the form when there are no work orders the query has no records so the Printme field is not present creating an error, is there a simple way like the first IF statement that will do this?

Thank you in advance

Mike

 

[Emohawk]

I'm not sure (I use Access 97) but I thought RecordCount property only applied to RecordSet objects. If you put this small bit of code before I think it should work.

Dim Rst as Recordset
Dim Db as Database
Dim SQLString as String

SQLString = "SELECT * FROM [Jobs Table]"

Set Db = CurrentDB

Set Rst = Db.OpenRecordset(SQLStr, DBOpenDynaset)

I assume your table containing your Jobs is called (imaginatively) Jobs Table.

Now change you first IF statement to this...

If Rst.EOF Then

and if it has already reached the end of the recordset (i.e. no data in the Jobs Table) then it should tigger the event.

Hope this works for you, let me know if it doesn't... My advice isn't always the best... All praise the Mighty Pat.

 

[Jerry Stoner]

Assuming youre using a query as your forms record source:

If DCount("*", "[YourQueryHere]") = 0 Then
MsgBox "There are no Jobs to complete yet", vbOKOnly
Exit Sub(or whatever else you need to do)
Else
Rest of your code here

 

[Xenix]

Thank you

Thank you Jerry

That worked 100% fine

thanks very much

Mike

 

[Jerry Stoner]

Welcome