Single Form

[Erichss]

Hello all.
I took over working on an Access database.
It's a facility phonebook listing.
Each location has it's own table, and then also 2 forms, a "main form" and a subform. The subform is basically the table in a dataset view, and the main form has a search function and some other information.


An overall navigation form is used to organize the layout.

But here's my question, how would I be able to make it so I can use same form and/or subform to open the correct table when the button for that location is clicked. That powers that be want to maintain the look of it, with the main location tabs at the top and then under each of those the facility tabs that open the phonelisting for each.

[ArviLaanemets]

Add a locations table, as minimum with fields for location ID and location description;
Instead of phonebook table for every location, create a new phonebook table with same structure + a field for location ID;
Create a saved append query, which reads all info (except autonumber ID's in case they are used) from specific location phonebook table + adds a field where you write in the ID of this location;
Use this query to import all phonebooks into this new table;
Probably with some corrections you can use your current main form - you have to add an unbound combo to select a location, and make some changes to take into account the existence of location ID in new table;
Base the continuous subform on new table. Add a bound control linked to location ID field in new table (and hide it - it is better any user has no chance to mess it up). Use location ID to link subform to main form (setting Link Master Fields and Link Child Fields properties of subform).

User selects a location in main form, and all phone numbers for this location are displayed in subform. When a new record is added into subform, it automatically gets same location ID as is selected in main form.

[ranman256]

you should have 1 table for the phonebook, but a field for LOCATION. then you can search in all facilities.

[Gicu]

You should really go through the suggestions in post #2 and combine all locations in one table. But until you do that you could use a union query as the source of the subform:

SELECT *, "LocationA" As Location FROM tblLocationA
UNION
SELECT *, "LocationB" As Location FROM tblLocationB 
UNION
SELECT *, "LocationC" As Location FROM tblLocationC
UNION
....

This query will not be updateable but you mention the purpose of the form is listing the numbers so you should be OK.
Cheers,