I have 2 identical tables in an employee information database, one is called "active" the other "inactive". The field types in both are identical. When an employee leaves/resigns/retires etc I move their entire record line to the inactive table via a delete & append query. This works fine.
We track our employees by thier shield number. I want to setup a form to track disciplinary action. When you enter the members shield # in a field, his last & first name & Shield # & assigned station populate on the form. In addition, I would like certain items from thier disciplinary history to drop into a subform embeded in the main form.
Is there a way to have access search the active table first and then, if the employee is not found there, search the inactive table for the same employee and populate the form with that info?
How do I do it?
We track our employees by thier shield number. I want to setup a form to track disciplinary action. When you enter the members shield # in a field, his last & first name & Shield # & assigned station populate on the form. In addition, I would like certain items from thier disciplinary history to drop into a subform embeded in the main form.
Is there a way to have access search the active table first and then, if the employee is not found there, search the inactive table for the same employee and populate the form with that info?
How do I do it?
Comment