Editing Records in Multi-Table Forms in Microsoft Access (Forms Based on Multiple Table Queries)

In this video, we're going to discuss the best way to edit data in a form that is based on fields from multiple tables.
Lina from St. Petersburg, Florida (a Platinum Member) asks: I've got a form based on a query with 4 different tables in it. It seems like whenever I make changes to one of the fields, strange things happen. Sometimes I can't add a new record. Sometimes I get an error message. Sometimes I think I'm editing one field and I end up changing 3 other things. What am I doing wrong?
Silver Members and up get access to an Extended Cut of this video. Members will see how to safely allow the user to edit fields in a form based on multiple tables by warning the user and asking if they're sure. We'll also see how to quickly open up another form to edit related data.
MEMBERS VIDEO:
• Multi-Table Forms EXTE...
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/MultiTableForms
LINKS:
Not Updateable: 599cd.com/NotUpdateable
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Relationships: 599cd.com/Relationships
Relational Combo: 599cd.com/Relational
Subforms: 599cd.com/Subforms
Invoicing: 599cd.com/Invoicing
RECOMMENDED COURSE:
Access Expert Level 4: 599cd.com/ACX4
SUGGESTED COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, microsoft access form based on multiple tables, Multiple Table Forms in Microsoft Access, How do you Create a form based on multiple tables in Access, subform, locked, Forms Involving Multiple Tables
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@cosmicfugue1226]

Thank you for this video tutorial. No wonder I've been scratching my head for several days to try to make a new record data entry form from 5 different tables. Now I've learnt that it is not possible whilst unknowingly I thought it was possible! 😂

[@anybody0077]

It is wonderful to discuss such a problematic problem. Combining tables in a query to get values from different tables (for a form), its a mess. Your example is the real one i've faced; and now seeing in your video laughed a lot.

[@599CD]

Thanks

[@stephenbeswick2163]

Great video I am learning so much from these videos please keep continuing with the videos steve

[@599CD]

Good to hear.

[@bluebear6198]

You are fantastic at this! Thank you

[@599CD]

You are so welcome!

[@stephenbeswick2163]

Another great video once again. Steve

[@599CD]

Glad you enjoyed it

[@user-rh6bi9ny5p]

Hi, I found your video really helpful. Although I have this problem. I use a form to register research groups, so it has all the fields on the groups table. Aditionally, each group may contain many researchers, so I made a continuous subform to enter each individual. A group may contain between 5 and 20 researchers so a single form would be so tedious to navigate. So here's my problem. Researchers have a beginning of activities date in the groups, but they don`t have an ending date, until they are out. So to avoid having null values I added a drop table (I have a table for persons, a table for groups, a table for the relationship between persons and groups -many to many- and a drop table to reflect the end date of the relationship). I really would like to do a continuous form that lets me add a researcher to a group AND lets me consign an end date to that relationship if there is one. Don't if I made myself clear. Any insights would be appreciated.

[@RamachandranS20]

hey thanks!!! Was having this very same issue - having 2 tables - and got them in the form - displays correctly, but I was unable to edit it. Thanks for clarifying. I'll keep it simple - and create two forms - one for each table :)

[@599CD]

Exactly!

[@RamachandranS20]

@@599CD Can we query from two tables [which are linked using an outer join], but display only from one in the form and get it updated?

[@RamachandranS20]

@@599CD Can we query from two tables [which are linked using an outer join], but display only from one in the form and get it updated?

[@ShlewisLewis]

You are the best!

[@599CD]

Thanks

[@messageofislam958]

Dear Richard, Can I navigate through multiple tables using only one form? {Requirement is to create an audit form for different excel reports of machines. I do not want to design a form for each table separately} Is there a short way to do this? or Is there a way to create multiple forms from multiple tables automatically as the fields remain same?

[@nando8067]

Hi Richard! Many thanks for your great content! That video describes my problem partially. I wonder If you have a similar video or a short advice to the following situation: I use 2 tables with 2 specific forms for each table. One form is called offers and the other one deals. I use a 1:1 relationship because I want to have a better structure of my data. I understand If I would store all the data in one table, I would not face the following problem: I edit my offer form and from this form I click a button and it opens the deals form with a new record. I edit the new form and press save, this will write the record id (deal) into the table offers. If I close the form deals and continue editing data in the form offers, I get an error message "write conflict". I am looking for a simple tutorial that explains: Open from one form a new form with a specific record, if this record does not exist, create a new record. After editing this new record, write the record id in the other table. Do you have one of those videos available? I've been going through your youtube videos but could not find it. Kind regards

[@599CD]

Use one table if they hold the same data. Differentiate offers from deals with a field.

[@nando8067]

@@599CD Thanks for the quick reply. They do not hold the same data. Does it make sense if you have 2 tables and both tables have stored the primary key of each other? I think that could solve my problem.

[@sedrickbowser3588]

This is on the level of importance as normalization and relationships. It's theory that helps build a good workflow for how to structure form design.

[@599CD]

Yep

[@mikewashington4188]

What if you placed the associated customer table data on a tab ensuring the customer data cannot be mistakenly updated when only the contact data should be updated?

[@599CD]

I personally don't like tab controls, but I suppose you could do that. Better to just lock the fields you don't want being changed.

[@NagendraKumar-wk1eh]

Hi Richard! I really learnt a lot from your fast tip videos. I am trying to maintain some stock items which are to gift some of my clients and some of organization. Both info have in different table. Now I am not able to make the table in which gifted items records can be stored. How can I make this? Thanks

[@599CD]

599cd.com/Inventory

[@ryanw1906]

I've had an issue where I have a separate form for Contacts, but if it's joined to Customer table to display data and I delete the record in Contacts, it's taking the Customer with it. Never could figure that one out.... caused a big headache.

[@599CD]

Yep. 599cd.com/QQ

[@learnmsexcel6129]

Hi Richard i need some help for example: i have 2 tables Table_A & Table_B ,Table-A has 3 fields Empid, Employee name, designation, department some records in this table while Table_B is blank with same fields and 1 form on which define Table_A fields. with command find button and save button, so record find from Table_A and on click save button record save in Table_B. please help me to resolved very thanks

[@599CD]

599cd.com/ask

[@benjamincorbin887]

RICHARD please Help! I am trying to make an issue list database but with the different forms within the same database, but it keeps inheriting data from the initial form. How do I separate these form into their own entity??

[@599CD]

Sounds like you need to change the Form's Record Source property to whatever table it's based on. However, if these forms are similar, you may want to use the same table and just differentiate the questions with a field.

[@599CD]

599cd.com/qq

[@benjamincorbin887]

@@599CD You have definitely earned my support. Thanks a ton!!

[@JstnW]

Good stuff. Could also be that Lina is using lookup tables incorrectly.

[@599CD]

Could be. :)