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

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

[@anybody007_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.

[@599CD]

Thanks

[@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.

[@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!

[@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

[@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.

[@stephenbeswick2163]

Another great video once again. Steve

[@599CD]

Glad you enjoyed it

[@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

[@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

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

[@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!!

[@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.

[@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

[@JstnW]

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

[@599CD]

Could be. :)

[@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