Access '97 Form

Here's the deal. I want to be able to key an employee number into a form and have the employee's name and dept. # inserted from another table. Other productivity info will also be keyed into the form. I just can't figure out how to get the employees' name and dept # automatically dropped into the form based on the employee number that is entered.

Any suggestions would be appreciated.

Thanks,
ern

Off the top of my head:

Code:

---

Private Sub txtEmpNo_AfterUpdate()
    Dim db As Database
    Dim rst As Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM OtherTable WHERE EmpNo = " & Me.txtEmpNo & ";",dbOpenSnapshot)

    If rst.EOF Then
        MsgBox "Employee #" & Me.txtEmpNo & " not found.", vbInformation, "Notice"
        Exit Sub
    End If
 
    Me.txtDeptNo = rst!DeptNo
    Me.txtEmpName = rst!EmpName

    rst.Close
    Set rst = Nothing
End Sub

---