Access '97 Form

[ernmeister]

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

[jmcswain]

Off the top of my head:


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