Empty record

[mderby]

Hi guys,

I have added a function to retrieve the last line no. from table TM0002 which has a link relationship with table TM0001 in field ProjectID. Below is the code :

Public Function GetLastLineNo(ProjectID As Integer, _
Own As String) As Integer
Dim rst2 As DAO.Recordset
'Dim rsa As ADODB.Recordset
Dim cdb2 As Database
Dim sqlstr As String

Set cdb2 = CurrentDb()
sqlstr = "Select Max([Line No]) as Line from TM0002 where [Project ID]=" & ProjectID & ";"

Set rst2 = cdb2.CreateQueryDef("", sqlstr).OpenRecordset

GetLastLineNo = rst2("Line")
If IsNull(GetLastLineNo) Then
GetLastLineNo = 1
End If
rst2.Close
cdb2.Close
End Function

Now, if there is no data to be selected will they be any error. Can we trap the error ? This will happen if new projectID is added to the master file TM0001.

I really don't know how ?

Thanks

Regards,
mderby

 

[FoFa]

Right after your:
Set rst2 = cdb2.CreateQueryDef("", sqlstr).OpenRecordset

do a
IF rst2.BOF or rst2.EOF then
' no rows returned
end if

 

[mderby]

Still error

I added the code in it but I got this error message.

Run-time error 94

Invalid use of Null

How to go about it ?

Thanks

mderby

 

[Jon K]

...............
...............
Set rst2 = cdb2.CreateQueryDef("", sqlstr).OpenRecordset

If IsNull(rst2("Line")) Then
  MsgBox "No records returned for this ID"
  Exit Function
End If
...............
...............

 

[mderby]

Solved

Thank you to two of you !!!

it worked.

thanks