Error handling

[scojerroc]

I have a form whos source is a query with record number criteria. This way,
upon opening of the form, it asks for a record number. When the user puts in
a record that is not in the set, the error handler gives a message and
requerys, which prompts for the record number again. This works perfectly,
but only once. If the user inputs an invalid record a second time, it asks
to end or debug. It appears that rearranging the code does not change
anything. Any suggestions?

 

[John Vinson]

I have a form whos source is a query with record number criteria. This way,
upon opening of the form, it asks for a record number. When the user puts in
a record that is not in the set, the error handler gives a message and
requerys, which prompts for the record number again. This works perfectly,
but only once. If the user inputs an invalid record a second time, it asks
to end or debug. It appears that rearranging the code does not change
anything. Any suggestions?

Could you please post your code?

I'd actually suggest using a Form control for the criteria, opening a
recordset, and seeing if its Recordcount is zero; and take appropriate
action if so.

John W. Vinson[MVP]

 

[Graham Mandeno]

It's hard to say without seeing your code, but I'll have a guess.

If an error occurs while you are in an error handler, then it will be
treated as an unhandled error. Your error handler should execute a Resume
statement to return to some code outside the handler.

Are you using Resume? I suspect not. Your code should look something like
this:

On Error Goto ErrHandler
PromptForRecord:
' ask the user for a record number
' lookup record
' error occurs if no record found
' continue with form opening
ExitPoint:
Exit Sub
ErrHandler:
' display message
If <need to retyr> then Resume PromptForRecord
Resume ExitPoint

 

[scojerroc]

I am using goto instead of resume (but only because I am not familiar with
it), and I don't want to exit, as I am using this code on form load. This is
the code without the error handler, which works fine until I put in a
nonexistant loan number:

Private Sub Form_Load()
Begin:
If IsNull(Me.Lock) Then
Me.Lock = fosusername()
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "This record is locked by " & UCase([Lock]) & ", please try
another loan."
Me.Requery
GoTo Begin
End If
End Sub
-------------------------------------
And since I wrote the the semi-functional Error code yesterday and
subsequently deleted it instead of copying it, I can't even rewrite it to
work as well as it was then. This is very nearly it, though - it's currently
failing the first time around:

Private Sub Form_Load()
Begin:
On Error GoTo ErrHand
If IsNull(Me.Lock) Then
Me.Lock = fosusername()
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "This record is locked by " & UCase([Lock]) & ", please try
another loan."
Me.Requery
GoTo Begin
End If
Exit Sub
ErrHand:
MsgBox "This is not a valid loan number, please try again."
Me.Requery
GoTo Begin
End Sub

Thank you both for your suggestions, I will look into them.

It's hard to say without seeing your code, but I'll have a guess.

If an error occurs while you are in an error handler, then it will be
treated as an unhandled error. Your error handler should execute a Resume
statement to return to some code outside the handler.

Are you using Resume? I suspect not. Your code should look something like
this:

On Error Goto ErrHandler
PromptForRecord:
' ask the user for a record number
' lookup record
' error occurs if no record found
' continue with form opening
ExitPoint:
Exit Sub
ErrHandler:
' display message
If <need to retyr> then Resume PromptForRecord
Resume ExitPoint
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form whos source is a query with record number criteria. This
way,
upon opening of the form, it asks for a record number. When the user puts
in
a record that is not in the set, the error handler gives a message and
requerys, which prompts for the record number again. This works
perfectly,
but only once. If the user inputs an invalid record a second time, it
asks
to end or debug. It appears that rearranging the code does not change
anything. Any suggestions?

 

[Graham Mandeno]

The point is that once you start executing your error handler code, you have
moved to a new "stack level", rather like calling another procedure or
executing a GoSub command. While you are at that level, any error that
occurs will be unhandled.

If you do a GoTo Begin in your error handler code, you are still at the
error handling level. (GoTo simply does a branch and makes no change to the
call stack). To return to the level where the original error occurred, you
MUST execute a Resume.

So, instead of GoTo Begin, use Resume Begin.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using goto instead of resume (but only because I am not familiar with
it), and I don't want to exit, as I am using this code on form load. This
is
the code without the error handler, which works fine until I put in a
nonexistant loan number:

Private Sub Form_Load()
Begin:
If IsNull(Me.Lock) Then
Me.Lock = fosusername()
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "This record is locked by " & UCase([Lock]) & ", please try
another loan."
Me.Requery
GoTo Begin
End If
End Sub
-------------------------------------
And since I wrote the the semi-functional Error code yesterday and
subsequently deleted it instead of copying it, I can't even rewrite it to
work as well as it was then. This is very nearly it, though - it's
currently
failing the first time around:

Private Sub Form_Load()
Begin:
On Error GoTo ErrHand
If IsNull(Me.Lock) Then
Me.Lock = fosusername()
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "This record is locked by " & UCase([Lock]) & ", please try
another loan."
Me.Requery
GoTo Begin
End If
Exit Sub
ErrHand:
MsgBox "This is not a valid loan number, please try again."
Me.Requery
GoTo Begin
End Sub

Thank you both for your suggestions, I will look into them.

It's hard to say without seeing your code, but I'll have a guess.

If an error occurs while you are in an error handler, then it will be
treated as an unhandled error. Your error handler should execute a
Resume
statement to return to some code outside the handler.

Are you using Resume? I suspect not. Your code should look something
like
this:

On Error Goto ErrHandler
PromptForRecord:
' ask the user for a record number
' lookup record
' error occurs if no record found
' continue with form opening
ExitPoint:
Exit Sub
ErrHandler:
' display message
If <need to retyr> then Resume PromptForRecord
Resume ExitPoint
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form whos source is a query with record number criteria. This
way,
upon opening of the form, it asks for a record number. When the user
puts
in
a record that is not in the set, the error handler gives a message and
requerys, which prompts for the record number again. This works
perfectly,
but only once. If the user inputs an invalid record a second time, it
asks
to end or debug. It appears that rearranging the code does not change
anything. Any suggestions?

 

[scojerroc]

The point is that once you start executing your error handler code, you have
moved to a new "stack level", rather like calling another procedure or
executing a GoSub command. While you are at that level, any error that
occurs will be unhandled.

If you do a GoTo Begin in your error handler code, you are still at the
error handling level. (GoTo simply does a branch and makes no change to the
call stack). To return to the level where the original error occurred, you
MUST execute a Resume.

So, instead of GoTo Begin, use Resume Begin.

Excellent. Thank you, sir.