Help with Error handling

[pcdaveh]

I'm not new to coding but I can say that my weak point is error handling. I have a text field in a form for an account number in this format "##-####.#" I'm trying to handle a couple of different errors.

This all occurs on the After Update event. Maybe a different event should be used?

I check the length of the text that was keyed.
I run a query to look the account number up in a table.

if it doesn't exist I want to undo what was keyed and set the focus back in that field. I can't get undo to work or get the focus back to that field?



Actual Code:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
DoCmd.SetWarnings (0)

If Len(txtAccount.Text) <> 9 Then
MsgBox &quot;Please follow the Account Number Convention&quot;
txtAccount.SetFocus

Exit Sub
End If
Select Case Right(txtAccount.Text, 1)
Case 9

Case 0

Case Else

MsgBox &quot;The last digit in your account must be 9 or 0&quot;
txtAccount.SetFocus
End Select


If Right(txtAccount.Text, 1) = 9 Then
DoCmd.OpenQuery &quot;q_tbl_AccountLookup3&quot;
Else
DoCmd.OpenQuery &quot;q_tbl_AccountLookup2&quot;
End If

Set rst = db.OpenRecordset(&quot;Mktbl_AccountLookup&quot
On Error GoTo ErrHandler:
rst.MoveFirst
MyInvID = rst![InvIDN]
MyPartID = rst![PartIDN]
MyInvName = rst![PartnershipInvestorName]
MyPartnershipName = rst![PartnershipName]
rst.Close
MyAccountNumber = txtAccount.Text
MyGlobalAccountNumber = MyAccountNumber
ErrHandler:
If Err.Number = 3021 Then
MsgBox &quot;The Account Number You entered is invalid&quot; & Chr(10) _
& &quot;It doesn't exist in tbl_PartnershipMembers&quot; & Chr(10) _
& &quot;Correct this immediately!&quot; & &quot; &quot; & Err.Number & Chr(10) _
& Err.Description
txtAccount.Undo
txtAccount.SetFocus
End If


Exit Sub
If Len(txtAccount.Text) <> 9 Then
MsgBox &quot;Please follow the Account Number Convention&quot;
txtAccount.SetFocus
Else
MyPrefix = Left(txtAccount, 2)
Call AutoSaveFiller
End If
txtTransactionDate.SetFocus
End Sub

 

[jebry]

Hi!

Try this in the BeforeUpdate event procedure:

Dim rst As DAO.Recordset
Dim sql As String

If Len(txtAccount.Value) <> 9 Then
Call MsgBox(&quot;Please enter a valid Account number. The Account number must follow the pattern ##-####.#&quot
Cancel = -1
Call txtAccount.SetFocus
Exit Sub
End If

sql = &quot;Select AccountNumberField From tbl_PartnershipMembers Where AccountNumberField = '&quot; & txtAccount.Value & &quot;'&quot;
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
Call MsgBox(&quot;Please enter a valid Account number. The Account number you entered is not in the table.&quot
Cancel = -1
Call txtAccount.SetFocus
Exit Sub
End If

Set rst = Nothing

You may be able to save some trouble by using a combo box for the account numbers and making the user choose one from the list.

hth Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

The after update event is too late to undo updates. Try the before update event. Maq B-)
<insert witty signature here>

 

[pcdaveh]

Thanks for your help/time Jeff!