No Current Record

[bard777]

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:

Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)) & " ", "0")

I tried an IF db.EOF, but that doesn't work.

Thanks for any ideas.

[Megalog]

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.

Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)), "0")

[bard777]

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.

Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)), "0")

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:

Dim rsSum As Recordset


Set db = CurrentDb

strSQL = "<SQL HERE>"
Set rsSum = db.OpenRecordset(strSQL)
If Not rsSum.EOF Then
    Me.txt_high_cnt = rsSum(0)
Else
    Me.txt_high_cnt = "0"
End If

[NeoPa]

A quicker and easier approach would be to use DCount().