trapping run-time error

[DaveE]

Using Access 2007 with linked tables to SQL2005 and have code like below.
When no record is returned, I want to display my own message to the user, not
the run-time error. What am I missing?

I also have an error handler that catches the 3021 run-time error but it
still won't display my error message.

Dim dblID as Long
Dim rstClient As ADODB.Recordset
Dim strSQL2 As String
Dim strTemplate As String

Set rstClient = New ADODB.Recordset

strSQL2 = "SELECT ReferringEntity, ReviewReport "
strSQL2 = strSQL2 & "FROM dbo_MasterCaseLog "
strSQL2 = strSQL2 & "WHERE ID = " & dblID

rstClient.Open strSQL2, CurrentProject.Connection

If rstClient.BOF And rstClient.EOF Then
MsgBox "A report template has not been defined for " & rstClient!
ReferringEntity & ".", vbCritical, "Data Error"
Exit Sub
End If

 

[Albert D. Kallal]

I not sure what you mean by trap an error here?

If your rstClient.Open strSQL2

does not return reocrds, then you can go:

rstClient.Open strSQL2, CurrentProject.Connection
if rstClient.EOF = true then
msgbox "no records returned"
end if

You don't need the bof check you have.

However, when the sql results in no records, then then there is no error
message. So, I am not sure what you are asking.

If you pass bad sql, then sure, you can trap the error.

You can go:

strSql = "select * from dbo_altry3"

On Error Resume Next
rst.Open (strSql), CurrentProject.Connection

If Err.Number <> 0 Then
MsgBox "Unable to get data" & vbCrLf & _
"Error number = " & Err.Number & vbCrLf & _
"Error message = " & Err.Description
End If

You can also on error goto to trap an error. Do expand on your question,
but no errors are generated when the sql don't return records.

 

[DaveE]

Albert,
Thanks for the response.

This problem turned out to be a faulty SQL statement that should have been
using left joins. No record was being returned which generated the run-time
error. Now I'm testing for a null value like this:

strSQL2 = "SELECT R.ReferringEntity, RR.ReviewReport "
strSQL2 = strSQL2 & "FROM dbo_MasterCaseLog M LEFT JOIN
(dbo_ReferringEntity R LEFT JOIN dbo_ReviewReport RR ON
R.ReviewReportTemplate = RR.ID) "
strSQL2 = strSQL2 & "ON M.ReferringEntity = R.ID "
strSQL2 = strSQL2 & "WHERE M.ID = " & dblID

Set dbsMCL = CurrentDb
Set rstClient = dbsMCL.OpenRecordset(strSQL2, dbOpenForwardOnly)

'rstClient.Open strSQL2, CurrentProject.Connection
If IsNull(rstClient!ReviewReport) Then
MsgBox "A report template has not been defined for " &
rstClient!ReferringEntity & ".", vbCritical, "Data Error"
Exit Sub
End If

This provides the functionality I need. The answer was hiding under my nose
the whole time, I just couldn't see it.

Thanks again.