Null event as there is no recordset

Kongta
Kongta used Ask the Experts™
on
I do have following code:

strSQL = "SELECT IQ  " & _
             "FROM dbo_tblStatistikTmp_sb_Land WHERE ((Land)='VC') "
           
             With .Item("VC")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With

But as in some events, there will be even no recordset in the table that matches 'VC' for example, it gives back NULL for .Item which this can't handle. I read through a lot of NULL questions here and tried but didn't solve my issue. Any idea how to fix?
thx
Kongta
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Use an on error statement.

For example:

function test() as boolean

         On Error GoTo handle_Error
         
             With .Item("VC")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With

         On Error GoTo 0 'This removes the "goto function for errors after this

         exit function 

handle_Error:
       ' Do error handling here

end function

Open in new window

Top Expert 2010

Commented:
Or handle the null explicitly:


                .Value = Nz(CurrentProject.Connection.Execute(strSQL)(0), "") 'if text

                .Value = Nz(CurrentProject.Connection.Execute(strSQL)(0), 0) 'if numeric

Open in new window

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Or don't even run the code if no records match...

strSQL = "SELECT IQ  " & _
             "FROM dbo_tblStatistikTmp_sb_Land WHERE ((Land)='VC') "

If Dcount("*","dbo_tblStatistikTmp_sb_Land","Land='VC'")>0 then          
             With .Item("VC")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With
else
    msgbox "No records matching 'Land=VC' found"
end if
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

MIS Liason
Most Valuable Expert 2012
Commented:
this may be better...

{...I'm in a rush, ..;-)  }

strSQL = "SELECT IQ  " & _
             "FROM dbo_tblStatistikTmp_sb_Land WHERE ((Land)='VC') "

If Dcount("*","dbo_tblStatistikTmp_sb_Land","Land='VC'")=0 then
    msgbox "No records matching 'Land=VC' found"
    exit sub
Else
             With .Item("VC")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With
end if

Author

Commented:
to be honest, matthew's would suit me best as it is very short and I use this several times in this event, but it doesn't work. I get an error 'BOF or EOF is true or the actual recordset has been deleted. The requested operation needs an actual rst'.

Jeff, yours is working fine but as said, shorter would be better, let me wait for short if there are better inputs coming.

buttersk, yours gets me an error on the code following on these lines, therefore Jeff's code to handle the error seems better
Top Expert 2010

Commented:
Kongta,

In this case, shorter <> better :)

If the root cause is that there is no rs to start with, Jeff addresses that, and I don't.  His comment is the solution, not mine.

Patrick

Author

Commented:
Fair played Patrick, seems that Jeffs won't be overruled, therefore I close this Q
Many thx to all
Rgds
Kongta
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Hey, Guys, you know me...
;-)

I Just threw that out there as another option.

There may situations when either of the other Expert's post may be a better fit.

All of the solutions would solved your question, so AFAIC, a split between all of us would be fine with me...

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial