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

No Current Record

bard777
P: 23
I am running some queries on form load to get some summary info on a database.

My query is summing records based on a priority field value (1, 2, or 3) and may find none. When there are no records that meet the criteria for a given priority the line that updates the textbox with the value errs out (below).

here is my code for the textbox:

Expand|Select|Wrap|Line Numbers
  1. Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)) & " ", "0")
  2.  
I tried an IF db.EOF, but that doesn't work.

Thanks for any ideas.
May 11 '10 #1
Share this Question
Share on Google+
3 Replies


Megalog
Expert 100+
P: 378
This NZ statement will never work correctly. You are adding " " to something, which in all cases will never return a null.

This would be a proper example that would return "0" on a null value, but if you need that space in there after a value, you'll have to break this out a bit more to handle both scenarios.

Expand|Select|Wrap|Line Numbers
  1. Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)), "0") 
May 11 '10 #2

bard777
P: 23
@Megalog
I do not need the space in there, I added that after what you suggested didn't work. I tried again in case I missed something the first time, but it still gives the "No Current Record" error.

It seemed like "no current record" is other than a Null value, so Nz can't handle it. That is why I tried to catch it with EOF, but I had no recordset defined.

Here is what worked:

Expand|Select|Wrap|Line Numbers
  1. Dim rsSum As Recordset
  2.  
  3.  
  4. Set db = CurrentDb
  5.  
  6. strSQL = "<SQL HERE>"
  7. Set rsSum = db.OpenRecordset(strSQL)
  8. If Not rsSum.EOF Then
  9.     Me.txt_high_cnt = rsSum(0)
  10. Else
  11.     Me.txt_high_cnt = "0"
  12. End If
  13.  
May 11 '10 #3

NeoPa
Expert Mod 15k+
P: 31,299
A quicker and easier approach would be to use DCount().
May 12 '10 #4

Post your reply

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