October 27, 2006 at 3:30 am
Hi
I have a simple problem, which I don't seem to be able to easily solve. I am using 2003 .adp.
I have the following vba code, which is the problem part of a long routine:
Dim PTsf As Variant
Dim PTfs As Variant
Dim Pi As Variant
Dim PTut As ADODB.Recordset
Set PTut = New ADODB.Recordset
Dim STRSQLpt As String
STRSQLpt = "select * from dbo.mytable where currentyear ='" & Forms!frmACyear!AcYear & "'" & _
"and person_id =" & Ps 'the person_id comes from another routine (all working fine)
PTut.Open STRSQLpt, CurrentProject.Connection, adOpenStatic, adLockOptimistic
On Error GoTo NXT
Pi = PTut!ptid
PTfs = PTut!ptname
PTsf = PTut!pt
PTut.Close
The above code comes from a long routine. Everything works fine until STRSQLpt doesn't return values for a particular person_id - thus PTid etc are null. It then falls over. The error trap doesn't work by sending the routine on to the next person, it just returns the message 'Either BOF or EOF are true etc.'
There must be an easy way to deal with this. Has anybody any ideas?
Thanks
Paul
October 27, 2006 at 4:09 am
PTut.Open STRSQLpt, CurrentProject.Connection, adOpenStatic, adLockOptimistic
if not pTot.eof then
Pi = PTut!ptid
PTfs = PTut!ptname
PTsf = PTut!pt
endif
PTut.Close
October 27, 2006 at 4:30 am
Hi
Thanks, very much. I knew the answer was simple, but I just couldn't spot it.
Thanks again
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply