Results 1 to 6 of 6
  1. #1
    CiViCChiC79 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Raleigh, NC
    Posts
    3

    What is the Best Way to Design Navigation Forms?

    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

    Click image for larger version. 

Name:	REPO-Relationships.jpg 
Views:	9 
Size:	199.7 KB 
ID:	15728

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CiViCChiC79 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Raleigh, NC
    Posts
    3
    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Why archive data? 15,000 is a very, very small database.

    Why would you use subforms for tblRepos fields?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CiViCChiC79 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Location
    Raleigh, NC
    Posts
    3
    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

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Navigation Record Buttons in Forms
    By jaarons in forum Forms
    Replies: 4
    Last Post: 01-02-2013, 09:57 PM
  2. Forms Navigation Bar on every form
    By dhogan444 in forum Forms
    Replies: 2
    Last Post: 12-03-2012, 06:19 PM
  3. Navigation Forms and Filters
    By Trichmond in forum Forms
    Replies: 2
    Last Post: 10-10-2012, 03:26 PM
  4. Navigation Pane will not accept any forms
    By Alexandre Cote in forum Forms
    Replies: 0
    Last Post: 02-08-2011, 09:16 PM
  5. Replies: 1
    Last Post: 01-04-2011, 05:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums