Link to home
Start Free TrialLog in
Avatar of Kongta
KongtaFlag for Switzerland

asked on

Null event as there is no recordset

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
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

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

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

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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kongta

ASKER

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
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
Avatar of Kongta

ASKER

Fair played Patrick, seems that Jeffs won't be overruled, therefore I close this Q
Many thx to all
Rgds
Kongta
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