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:

BECOME A MEMBER:

LEARN MORE:

LINKS:

RECOMMENDED COURSE:

SUGGESTED COURSES:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

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.

[anybody_who]

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.

[benjamincorbin]

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??

[SecretaríadeInvestigaciónFCEyN]

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.

[messageofislam]

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?

[Initial_Gain]

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! 😂

[sedrickbowser]

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.

[learnmsexcel]

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

[nando]

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

[ryanw]

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.

[NagendraKumar-wkeh]

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

[mikewashington]

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?

[JstnW]

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

[stephenbeswick]

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

[RamachandranS]

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 :)