Run-time error '94' and No current record

[dawn_dudley]

Hello,

I have some code that is bombing at the ***** line of code and I don't know
why, there are definitely records in the file and definitely the exact record
I am on. Your help would be greatly appreciated.

Public Function ListContracts(RefNo As String, PN As String) As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select RefNo, ContractNo FROM
tblHrlContractNumbers WHERE RefNo = '" & RefNo & "';")

If Not rs.EOF Then
rs.MoveFirst
***** ListContracts = rs!ContractNo *****
rs.MoveNext
If Not rs.EOF Then
Do
ListContracts = ListContracts & vbCrLf & rs!ContractNo
rs.MoveNext
Loop While Not rs.EOF
End If

End If
rs.Close

If PN <> "" Then
ListContracts = ListContracts & vbCrLf & "(" & PN & ")"
End If

Set db = Nothing

End Function

 

[Allen Browne]

Error 94 is about invalid use of Null?

Try declaring the function as a Variant, so it doesn't fail if the return
value is null, i.e.:
Public Function ListContracts(RefNo As String, PN As String) As Variant

Alternatively, replace the problem line with:
ListContracts = Nz(rs!ContractNo, "")