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
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.
Thanks a lot for your advices. I appreciate your time teaching me.
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.
Thanks a lot.
Also, be aware of reserved words. "Name" is a reserved word and shouldn't be used for object names.
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.