By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

How to test if query is empty

P: 48
Hi there again folks. Im continuing on from the code posted on SQL Error.

I wish to display a msg box and prevent a new form from opening if it is an empty recordset. The following comes after the SQL Code in the click event

Expand|Select|Wrap|Line Numbers
  1.  
  2.          qdf.SQL = strSQL
  3.          Dim msg As String
  4.          msg = "Sorry there are no models in stock with that specification"
  5.  
  6.         If IsNull(qdf.Fields("model")) Then
  7.         MsgBox (msg)
  8.  
  9.         Else
  10.  
  11.          'DoCmd.OpenQuery "Admin_query"
  12.          DoCmd.Close acForm, Me.Name
  13.  
  14.  
  15.          Set qdf = Nothing
  16.          Set db = Nothing
  17.          DoCmd.OpenForm "laptop_specs", , "Admin_query"
  18.  
  19.         End If
  20.  
  21.  
but it still opens a blank white screen as there is no data. Am i on the right path here " If IsNull(qdf.Fields("model")) Then " or is there another method to determine if the query is empty.
Regards panteraboy
May 9 '08 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
Hi there again folks. Im continuing on from the code posted on SQL Error.

I wish to display a msg box and prevent a new form from opening if it is an empty recordset. The following comes after the SQL Code in the click event

Expand|Select|Wrap|Line Numbers
  1.  
  2.          qdf.SQL = strSQL
  3.          Dim msg As String
  4.          msg = "Sorry there are no models in stock with that specification"
  5.  
  6.         If IsNull(qdf.Fields("model")) Then
  7.         MsgBox (msg)
  8.  
  9.         Else
  10.  
  11.          'DoCmd.OpenQuery "Admin_query"
  12.          DoCmd.Close acForm, Me.Name
  13.  
  14.  
  15.          Set qdf = Nothing
  16.          Set db = Nothing
  17.          DoCmd.OpenForm "laptop_specs", , "Admin_query"
  18.  
  19.         End If
  20.  
  21.  
but it still opens a blank white screen as there is no data. Am i on the right path here " If IsNull(qdf.Fields("model")) Then " or is there another method to determine if the query is empty.
Regards panteraboy
Try the Dlookup() like:
Expand|Select|Wrap|Line Numbers
  1. IF IsNull(Dlookup("model","tblX","ID=" & Me.ID) then
  2.    msgbox msg
  3.    end sub
  4. endif
  5.  
You'll need to have a unique key (Here Me.ID) for the WHERE parameter.

Nic;o)
May 9 '08 #2

P: 48
Cheers for that Nic. Did the trick nicely. Its easy when you know how lol.
Regards panterboy
May 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.