Avatar of Kongta
Kongta
Flag 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
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Ken Butters

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

Patrick Matthews

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 Coachman

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Patrick Matthews

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
Kongta

ASKER
Fair played Patrick, seems that Jeffs won't be overruled, therefore I close this Q
Many thx to all
Rgds
Kongta
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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