Results 1 to 7 of 7
  1. #1
    emsadoon is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3

    Question A form for 3 tables


    Click image for larger version. 

Name:	Capture.JPG 
Views:	38 
Size:	44.2 KB 
ID:	44894I have the following 3 tables. A software has one to many subscriptions. A user has one to many subscriptions. What is a good form design needed to see the existing subscriptions and also add new ones. Can I have a sub-form with a drop down menus for users?

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You have a many-to-many table setup, see this:

    Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

    Advise before you go much further,
    Remove spaces from field names.
    Having ID as primary key name for all three tables is confusing. Suggest Software_PK, Subscription_PK and User_PK. The two linked fields in the Subscription table should be Software_FK and User_FK.
    _PK is for primary keys, _FK is for foreign keys.

    Your table names also include spaces. Suggest tblSoftware, tblSubscription and tblUser.

    Having a consistent and logical naming convention helps make it clear just what elements are when programming and reviewing code.
    Last edited by davegri; 04-02-2021 at 11:26 PM. Reason: added suggested convention

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    normally, you dont edit 3 tables at once.
    you CAN have a master/child form ,where edit the master table in the top part,
    edit the child(s) in the subform. You can swap out subforms using a tab control to keep things neat and simple for the users.

  4. #4
    emsadoon is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3

    Thumbs up Appreciation

    Thanks a lot for your advices. I appreciate your time teaching me.

    Quote Originally Posted by davegri View Post
    You have a many-to-many table setup, see this:

    Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

    Advise before you go much further,
    Remove spaces from field names.
    Having ID as primary key name for all three tables is confusing. Suggest Software_PK, Subscription_PK and User_PK. The two linked fields in the Subscription table should be Software_FK and User_FK.
    _PK is for primary keys, _FK is for foreign keys.

    Your table names also include spaces. Suggest tblSoftware, tblSubscription and tblUser.

    Having a consistent and logical naming convention helps make it clear just what elements are when programming and reviewing code.

  5. #5
    emsadoon is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3
    Thanks a lot.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, be aware of reserved words. "Name" is a reserved word and shouldn't be used for object names.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Usual data entry arrangement for many-to-many relationship is a form/subform.

    1. main form bound to SoftwareInfo, subform bound to SubscriptionHistory with a combobox to select user

    or

    2. main form bound to UserInfo, subform bound to SubscriptionHistory with a combobox to select software

    or

    3. use a single form bound to SubscriptionHistory with 2 comboboxes to select software and user


    Enter new record for combobox list 'on-the-fly' during data entry with combobox NotInList event.
    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. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  2. Replies: 1
    Last Post: 12-04-2017, 08:44 AM
  3. Replies: 19
    Last Post: 08-10-2017, 01:49 PM
  4. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

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