What is the Best Way to Design Navigation Forms?

[CiViCChiC79]

I have setup a database as seen below. I want to use a master navigation form with tabs to section out parts of the huge REPOs table for data entry. Each tab will be a subform that will use some combination of the five tables pictured.



My question is, Do I need to create a query with the primary keys from all tables as the record source for the navigation form and then create the subforms off of that? OR should I create individual forms for each tab querying only the tables needed for that tab and pass the primary key from tab to tab using VBA?

Which design would produce the best performance overall? Is there a better design for this scenario that I am missing?

Thank you for your time,
CiViCChiC79

[June7]

A form can enter/edit data for only one table. The fields from tblRepos can be distributed on pages of tab control without use of subforms. Use comboboxes to enter values into the FK fields.

New records for the other tables should be entered on other forms. These can be subforms on pages of tab control but will not be linked to main form bound to tblRepos.


Why is tblInsitutions linked to two tables?

[CiViCChiC79]

Yes, each tab will only allow editing of certain fields for one table (REPOs) and the foreign keys are drop down boxes. Fields from other tables that populate based on the foreign keys will not be active for editing. Yes, adding completely new records will be done on other forms. I probably need to remove that extra relationship from Institutions. I thought the REPO and the Member could be associated with different Institutions, but I found out today that that is irrelevant.

If I use a subform for each tab to display the data I need, will this affect database/form performance? The database will top out at about 15,000 records before we archive data.

Thanks, CiViCChiC79

[June7]

Why archive data? 15,000 is a very, very small database.

Why would you use subforms for tblRepos fields?

[CiViCChiC79]

Data would only be archived if needed. Database has a FE/BE design over a WAN so sometimes performance suffers. At least is does on other similar databases, those of which I will be redesigning.

Users need to view REPO data divided into sections based on their needs. There are 71 fields in tblREPOs and they can't all be viewed/edited on one form anyway.

Thanks, CiViCChiC79

[June7]

Why they couldn't they all be viewed/edited on one form?

Use the pages of tab control to organize the fields by section grouping.

A form does have a limit on the number of elements it can have (755 I think).

It just occurred to me you may be referring to a Navigation Form as opposed to a form with a tab control. I never use Navigation Form.