adding an on error code

Question

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

HansUp · Accepted Answer · 2014-03-26 18:38:07Z

4

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

answered Mar 26, 2014 at 18:38

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2119980 Over a year ago

Your vast knowledge of this stuff is nothing short of amazing.

Wayne G. Dunn · Accepted Answer · 2014-03-26 18:47:04Z

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

Collectives™ on Stack Overflow

adding an on error code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related