Phone Book Problem

Here is my setup:

In Acess 97 I have a single data base with 5 tables, Attorneys, Employers, Health Care Professionals and two others. All tables have exactly the same fields.

I am trying to create a “phone book” made up of all the records in the data base. The form for this phone book has only a few fields such as fname, lname, phone, address. It also has the autonumber field. The record source for the form is a Union Query. When I open the form it works well. I get lawyers, employers, doctors , etc. all in alphabetical order.


Here is my problem:


I have a command button labeled “View Record.” I want to be able to click on this and have it find the record in its original table and open the form for that table so the record can be modified (on exiting the record I have it requery so the phone book is updated).

I can only get this to work with one table at a time, that is, the table that is named in the event procedure code. So if I am in, say, an Attorney record and tblAttoney is named in the event procedure code, all works well. But if I am in, say, an Employer record I get a blank Attorney form opened.


How can I get it to trace the autonumber to the proper table and open the record . I assume that the autonumbers are unique ACROSS all five tables since they are in the same database.


Any help would be greatly appreciated.