no data to display in forms>close

  • Thread starter Thread starter john
  • Start date Start date
J

john

hi on some of my forms wich use a filter/criteria some
times the forms have no data to display is there a way in
code wich i can stop the form from opening instead of it
showing a blank form with no controls?


thanks


john
 
Yes. In code, set a string to the same SQL statement as
controls your form, including any filter or Where
clauses. Then, open a recordset with that SQL string. If
the recordset is BOF (beginning of file), then you have no
results and can close the form. Something like this
should do:

dim strSQL as string
dim testRst as object

strSQL = "SELECT table.field1, table.field2 " _
& "FROM table WHERE ({your test});"
Set testRst = CurrentDb.OpenRecordset(strSQL)
If testRst.BOF Then 'BOF = no hits
MsgBox "No matches!"
Else
DoCmd.OpenForm "{your form}"
End If

testRst.close
set testRst = nothing

Hope this helps!

- Scott
 
Back
Top