CODEWITHSUNDEEP
ms-accessvba
user2119980
user2119980

Reputation: 509

adding an on error code

I have a code in my form, once you type in an employees id it searches a table and auto fills the corresponding data. If the number is incomplete or is not in the table an error window pops up enter image description here

And below is the code:

The Me.txtEmpName = rec("EMP_NA") is highlighted. I would like in the instance of an incomplete ID or if the ID does not exist, a msg box appears saying that employee id is not valid, please try again. or something along those lines, then it just goes back to the form instead of getting the error message above. Any thoughts?

Private Sub txtEmpID_AfterUpdate()
Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = CurrentDb
strSQL = "Select * From tblEmpData Where TMSID = '" & Me.txtEmpID & "'"
Set rec = db.OpenRecordset(strSQL)

Me.txtEmpName = rec("EMP_NA")
Me.cboGender = rec("EMP_SEX_TYP_CD")
Me.cboEEOC = rec("EMP_EOC_GRP_TYP_CD")
Me.txtDivision = rec("DIV_NR")
Me.txtCenter = rec("CTR_NR")
Me.cboRR = rec("REG_NR")
Me.cboDD = rec("DIS_NR")
Me.txtJobD = rec("JOB_CLS_CD_DSC_TE")
Me.cboJobGroupCode = rec("JOB_GRP_CD")
Me.cboFunction = rec("JOB_FUNCTION")
Me.cboMtgReadyLvl = rec("Meeting_Readiness_Rating")
Me.cboMgrReadyLvl = rec("Manager_Readiness_Rating")
Me.cboJobGroup = rec("JOB_GROUP")

Set rec = Nothing
Set db = Nothing
End Sub

Upvotes: 1

Views: 49

Answers (2)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

The following code will fix the issue you are having. 1. You are not finding a record, thus the error you received. 2. If you want to handle other errors, change the code in the Error_Trap to test for the error number.

Private Sub txtEmpID_AfterUpdate()
Dim db As DAO.Database
Dim rec As DAO.Recordset

    On Error GoTo Error_Trap
    Set db = CurrentDb
    strSQL = "Select * From tblEmpData Where TMSID = '" & Me.txtEmpID & "'"
    Set rec = db.OpenRecordset(strSQL)
    If rec.EOF Then
        MsgBox "The Employee ID you entered was not found. Please try again", vbOKOnly, "Wrong ID"
        GoTo Close_It
    End If

    Me.txtEmpName = rec("EMP_NA")
    Me.cboGender = rec("EMP_SEX_TYP_CD")
    Me.cboEEOC = rec("EMP_EOC_GRP_TYP_CD")
    Me.txtDivision = rec("DIV_NR")
    Me.txtCenter = rec("CTR_NR")
    Me.cboRR = rec("REG_NR")
    Me.cboDD = rec("DIS_NR")
    Me.txtJobD = rec("JOB_CLS_CD_DSC_TE")
    Me.cboJobGroupCode = rec("JOB_GRP_CD")
    Me.cboFunction = rec("JOB_FUNCTION")
    Me.cboMtgReadyLvl = rec("Meeting_Readiness_Rating")
    Me.cboMgrReadyLvl = rec("Manager_Readiness_Rating")
    Me.cboJobGroup = rec("JOB_GROUP")

Close_It:
    Set rec = Nothing
    Set db = Nothing
    Exit Sub

Error_Trap:
    If Err.Number = 99999999 Then                       ' Change this line to test for other conditions
        MsgBox "...... ", vbOKOnly, "....."
        Resume Close_It
    End If
End Sub

Upvotes: 1

HansUp
HansUp

Reputation: 97100

After you open the recordset, check whether it is empty. If empty, present your message. If not empty, load the recordset values into your data controls.

Set rec = db.OpenRecordset(strSQL)
If (rec.BOF And rec.EOF) Then
    ' when both BOF and EOF are True, the recordset is empty
    MsgBox "employee id is not valid, please try again"
Else
    Me.txtEmpName = rec("EMP_NA")
    ' and so forth
End If

Upvotes: 4

Related Questions