Display a form or dialogue box when the result is 0 or a minus

[Mcnultyj]

I have written a query to produce the result of two rows which have been subtracted from each other, what i want to do is display a form or dialogue when on the reults is a 0 or a minus figure

regards

James

 

[nickjar2]

Hi James,

This is how I do it. If i open a form based on (for example) a query, before displaying the form i will run the sql in code using DAO and if the recodset returns 0 rows then cancel opening the form.

Eg.

dim db as database
dim sSelect as string
dim rs as recordset

set db = currentdb
sSelect = "Select fields from table where etc etc"
' where sSelect is the exact SQL that your form is based.

set rs = db.openrecordset(sselect,dbopendynaset)
if rs.recordcount<1 then
' No records returned
docmd.openform &quot;form&quot;
else
docmd.openform &quot;other form&quot;
end if

I think this is the code. You may have to tweak it and correct any syntax problems.

Nick