How to handle recordset errors?

[PJFry]

Below is a piece of code I am working on. The issue I am having is that if
there is a recordset error, specifically if the the value in iBox does not
exist in the recordset, there is your standard 'Not BOF or EOF' error. If
that error exists, I will have a msgbox popup stating that; however, if there
is any other error, I want the gcfHandleErrors sub to run.

How can I do that?

Private Sub btnSelectCd_Click()

If gcfHandlErrors Then On Error GoTo PROC_ERR

Dim iBox As String

iBox = InputBox("Please enter retreat code", "Retreat Code")

'Creates recordset as rs
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

'SQL for rs
strSQL = "SELECT * FROM tRetreat WHERE txtRetreatCd = '" & iBox & "'"

'open rs
rs.Open strSQL, cn

retSelect = rs!RetreatID

rs.Close
Set rs = Nothing

DoCmd.OpenForm "fSwitchboard", acNormal
DoCmd.Close acForm, "Form1"

'Error
Handler--------------------------------------------------------------------------------
Exit_PROC_ERR:
Exit Sub

PROC_ERR:
Call ErrorLog("", Module, Err.Number, Err.Description)

End Sub

Thanks in advance!
PJ

 

[mscertified]

Use On Error goto gfchandlerrors
Your code will crash if the user types in an apostrophe.
Its generally not a good idea to get input via InputBox.

-Dorian

 

[PJFry]

I'm not too concerned about the apostrophe. The codes they enter in are
always in a AAA format.

What about this:

When a the PROC_ERR encounters a 3021 error, I can have it resume the sub it
came from. Then embed a different handler...

That sounds clunky though. Worth a try I suppose...

 

[Klatuu]

It is a good practice to always check to see if the recordset has records
before you do anything with it:

strSQL = "SELECT * FROM tRetreat WHERE txtRetreatCd = '" & iBox & "'"

'open rs
rs.Open strSQL, cn
If rs.RecordCount = 0 Then
MsgBox "No RecordFound"
Else
retSelect = rs!RetreatID
End If

rs.Close
Set rs = Nothing

Now, in your case, since you want one field from one record, a DLookup would
be a better choice:

retSelect = DLookup("[RetreatID]", "tRetreat", "txtRetreatCd = """ &
iBox & """'")

Also, retSelect should be a Variant data type in case the record isn't
found. If no matching record is found or there RetreadID contains a Null
value, you will get an Invalid Use of Null Error.

 

[PJFry]

That is exactly what I am looking for. Thanks!

It is a good practice to always check to see if the recordset has records
before you do anything with it:

strSQL = "SELECT * FROM tRetreat WHERE txtRetreatCd = '" & iBox & "'"

'open rs
rs.Open strSQL, cn
If rs.RecordCount = 0 Then
MsgBox "No RecordFound"
Else
retSelect = rs!RetreatID
End If

rs.Close
Set rs = Nothing

Now, in your case, since you want one field from one record, a DLookup would
be a better choice:

retSelect = DLookup("[RetreatID]", "tRetreat", "txtRetreatCd = """ &
iBox & """'")

Also, retSelect should be a Variant data type in case the record isn't
found. If no matching record is found or there RetreadID contains a Null
value, you will get an Invalid Use of Null Error.
--
Dave Hargis, Microsoft Access MVP

Below is a piece of code I am working on. The issue I am having is that if
there is a recordset error, specifically if the the value in iBox does not
exist in the recordset, there is your standard 'Not BOF or EOF' error. If
that error exists, I will have a msgbox popup stating that; however, if there
is any other error, I want the gcfHandleErrors sub to run.

How can I do that?

Private Sub btnSelectCd_Click()

If gcfHandlErrors Then On Error GoTo PROC_ERR

Dim iBox As String

iBox = InputBox("Please enter retreat code", "Retreat Code")

'Creates recordset as rs
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

'SQL for rs
strSQL = "SELECT * FROM tRetreat WHERE txtRetreatCd = '" & iBox & "'"

'open rs
rs.Open strSQL, cn

retSelect = rs!RetreatID

rs.Close
Set rs = Nothing

DoCmd.OpenForm "fSwitchboard", acNormal
DoCmd.Close acForm, "Form1"

'Error
Handler--------------------------------------------------------------------------------
Exit_PROC_ERR:
Exit Sub

PROC_ERR:
Call ErrorLog("", Module, Err.Number, Err.Description)

End Sub

Thanks in advance!
PJ