How to edit records in multiple tables through a form in Access 2013 (1 Viewer)

[saby]

To start with, there are around ten tables for each process. The first process starts with the order received from the client; the info is stored in the first table. The second process is about an employee being assigned to carry out the reconnaissance, where he gets all the required information alongwith photographs. These information are stored in the second table where a unique id is given based on the recce sheet no. in which he fills in the details. The sizes, item description and qty related to this particular recce are stored in the third table. This table also contains rates, taxes and other relevant information. The path to a PPT file (which is the third process and where the photographs alongwith requirements, size and diagram is made) is stored in the fourth table. The fourth table contains design details alongwith the approval date. The fifth process is about preparing estimate for which the second table is used to store rates and other info. Now, after reviewing the estimate, the client sometimes makes changes to the item or material description or bargains for which a revised estimate has to be prepared. I am stuck over this. I can call the tables in the form, but I am unable to edit it.

The recordsource in the property sheet of the form is:
SELECT [qtyForformEdittblSizeQtyAsPerRecce].[Element], [qtyForformEdittblSizeQtyAsPerRecce].[ProjWidth], [qtyForformEdittblSizeQtyAsPerRecce].[ProjHeight], [qtyForformEdittblSizeQtyAsPerRecce].[ProjDepth], [qtyForformEdittblSizeQtyAsPerRecce].[Qty], [qtyForformEdittblSizeQtyAsPerRecce].[UOMDescription], [qtyForformEdittblSizeQtyAsPerRecce].[tblTotal], [qtyForformEdittblSizeQtyAsPerRecce].[HSNSACode], [qtyForformEdittblSizeQtyAsPerRecce].[ItemRate], [qtyForformEdittblSizeQtyAsPerRecce].[CGSTPercent], [qtyForformEdittblSizeQtyAsPerRecce].[SGSTPercent], [qtyForformEdittblSizeQtyAsPerRecce].[IGSTPercent], [qtyForformEdittblSizeQtyAsPerRecce].[Est_Emp_Id], [qtyForformEdittblSizeQtyAsPerRecce].[Est_date] FROM [qtyForformEdittblSizeQtyAsPerRecce] WHERE [qtyForformEdittblSizeQtyAsPerRecce].[Recce_num]=[forms]![frmEstimateMain]!txttemprecnum ORDER BY ID ;

qtyForformEdittblSizeQtyAsPerRecce is a query table which uses multiple tables AND it is not editable.

Gina has suggested breaking up and using form/subform. Can someone guide me how to go about it.

 

[CJ_London]

cant tell without seeing your qtyForformEdittblSizeQtyAsPerRecce query which is what matters. please post the sql to that

 

[isladogs]

Ideally each form that requires editing should just be based on one table.
You can sometimes use more than one, but every additional table increases the likelihood that your record source will be read only.
Your SQL IS based on a query but we can't tell what the query SQL comprises.

Create a main form based on as few tables as possible and ensure it is editable.
Now relate a subform using one or more tables with the same editable property and link to the main form using a master/child field or fields.
Repeat as often as ne essaryto include all necessary data from your tables.
Note your sub forms can also be subforms of the main form or you can nest them - up to 7 levels deep IIRC.,

Hope that helps. If you look at Access help it should provide some examples or do a forum search for subforms.
Come back with more specific questions if you need further help

 

[saby]

cant tell without seeing your qtyForformEdittblSizeQtyAsPerRecce query which is what matters. please post the sql to that

Hi CJ, herebelow please find the SQL query, as required by you:

SELECT DISTINCT [Project master].Proj_Id, [Project master].[Project name] AS [Outlet name], [Recce order].CustId, [Customer master].[Customer name], [Recce done details].Recce_done_date, [Emp_first_name] & ' ' & [Emp_last_name] AS [Recce Employee Name], [Recce done details].[Work Description], [Recce done details].RecDoneRemark, tblSizeQtyAsPerRecce.ID, tblSizeQtyAsPerRecce.ElementIDFK, tblElement.ElementID, tblElement.Element, tblSizeQtyAsPerRecce.ProjWidth, tblSizeQtyAsPerRecce.ProjHeight, tblSizeQtyAsPerRecce.ProjDepth, tblSizeQtyAsPerRecce.UOMTypeID, tblUOM.UOMType, tblUOM.UOMDescription, tblSizeQtyAsPerRecce.tblTotal, tblSizeQtyAsPerRecce.ItemRate, tblSizeQtyAsPerRecce.HSNSACode, tblSizeQtyAsPerRecce.ItemRate, tblSizeQtyAsPerRecce.SGSTPercent, tblSizeQtyAsPerRecce.CGSTPercent, tblSizeQtyAsPerRecce.IGSTPercent, tblSizeQtyAsPerRecce.Est_date, tblSizeQtyAsPerRecce.Est_Emp_Id, tblSizeQtyAsPerRecce.Qty, tblSizeQtyAsPerRecce.Recce_num
FROM ([Customer master] INNER JOIN ([Recce order] INNER JOIN [Project master] ON [Recce order].Projec_Id = [Project master].Proj_Id) ON [Customer master].Cust_Id = [Project master].Customer_Id) INNER JOIN (([Employee master] INNER JOIN [Recce done details] ON [Employee master].Emp_Id = [Recce done details].Emp_Id_recce) INNER JOIN (tblElement INNER JOIN (tblSizeQtyAsPerRecce INNER JOIN tblUOM ON tblSizeQtyAsPerRecce.UOMTypeID = tblUOM.UOMType) ON tblElement.ElementID = tblSizeQtyAsPerRecce.ElementIDFK) ON [Recce done details].Recce_num = tblSizeQtyAsPerRecce.Recce_num) ON [Recce order].Recce_prime_no = [Recce done details].Recce_prime_no
WHERE (((tblSizeQtyAsPerRecce.ItemRate)>0) AND ((tblSizeQtyAsPerRecce.Qty)>0));


The fields I should be able to view are all the records in the query, however, I should be able to edit in the fields tblSizeQtyAsPerRecce.HSNSACode, tblSizeQtyAsPerRecce.ItemRate, tblSizeQtyAsPerRecce.SGSTPercent, tblSizeQtyAsPerRecce.CGSTPercent, tblSizeQtyAsPerRecce.IGSTPercent

 

[saby]

Ideally each form that requires editing should just be based on one table.
You can sometimes use more than one, but every additional table increases the likelihood that your record source will be read only.
Your SQL IS based on a query but we can't tell what the query SQL comprises.

Create a main form based on as few tables as possible and ensure it is editable.
Now relate a subform using one or more tables with the same editable property and link to the main form using a master/child field or fields.
Repeat as often as ne essaryto include all necessary data from your tables.
Note your sub forms can also be subforms of the main form or you can nest them - up to 7 levels deep IIRC.,

Hope that helps. If you look at Access help it should provide some examples or do a forum search for subforms.
Come back with more specific questions if you need further help

Sir, presently I am using just one table to edit and its name is "tblSizeQtyAsPerRecce". The other tables are related for the descriptions in them which has to be displayed on the form for easy understanding of the user so they do not get confused and make error. I have posted the SQL query in reply to CJ_London, for your perusal.

 

[CJ_London]

with regards subforms, each subformcontrol has two properties, linkchildfields and linkmasterfields - these represent your joins e.g. Assuming your main form is based on the customer master table and the subform project master then this join

ON [Customer master].Cust_Id = [Project master].Customer_Id)

would have Cust_id in the linkmasterfields property and customer_id in the linkchild fields. Note: If you have set up relationships this should happen automatically when you create the subform control

Not sure why you are bringing through table employee master since you don't appear to use any of the fields

it may be that some fields don't need a subform - the record would show in a combo or listbox

 

[saby]

with regards subforms, each subformcontrol has two properties, linkchildfields and linkmasterfields - these represent your joins e.g. Assuming your main form is based on the customer master table and the subform project master then this join

ON [Customer master].Cust_Id = [Project master].Customer_Id)

would have Cust_id in the linkmasterfields property and customer_id in the linkchild fields. Note: If you have set up relationships this should happen automatically when you create the subform control

Not sure why you are bringing through table employee master since you don't appear to use any of the fields

it may be that some fields don't need a subform - the record would show in a combo or listbox

A single customer may or may not have multiple projects which are assigned to us. Each project may have equal to or more than one item (element) to be supplied or attended to by us. Moreover that same customer may repeat the assignment for the same project but with different items (in a span of 6 to 12 months). Hence, we keep the recce sheet no. as the unique Id. We need to keep track of the customers and the projects, but mainly the items delivered to the respective project (sometimes it gets cancelled or kept on hold). Based on the items delivered can a bill be raised on the customer. When a user enters the rates and taxes, he should know for which customer and project and for which items he is entering the numbers. If he makes an error the entire project will go haywire. Hence, the customer and the project names are displayed on the form along with the Item name (for which separate tables are made). FYI, I have made a form to display two list boxes (one for entry of item rates for the first time and the other for editing item rates already present). When I select a project from the list, it takes me to another form. I am successful in entering fresh item rates in one of the forms. But in the other form I am able to call data from the table but could not edit it. This is where I am seeking help.

I was going through the website and came across make-table and update tables, but I could not understand it. Please note that mine is a linked table.

 

[CJ_London]

The other tables are related for the descriptions in them which has to be displayed on the form for easy understanding

in that case use combo controls with your form based on whatever

 

[CJ_London]

It is not clear to me what your form is supposed to do, at the moment you appear to have an Excel way of thinking which is not the database way.

 

[saby]

It is not clear to me what your form is supposed to do, at the moment you appear to have an Excel way of thinking which is not the database way.

At last, I just overhauled the SQL query in the design view of the query table as well as the record source of the property sheet and now I am able to view and edit as per requirement. Sometimes, discussion helps and unclogs the otherwise clogged brain. Thanks for your help.

 

[GinaWhipp]

I see you have got it working!

 

[Pat Hartman]

It would have been helpful to have seen a relationship diagram. Let me explain two different scenarios. The first is a relationship that is hierarchial.
Customer-->Order-->OrderDetails-->Products
For this set of tables, you can (although you probably wouldn't) create a single query that joins all the tables. This query will be updateable (unless you did something to make it not updatable like using a group by)

Another scenario is one table that has several child tables that are not related to each other:
Student-->Parents
Student-->Classes
Student-->Vehicles

Since all the tables have a relationship to Student, you can create a query that joins all four tables. The output will not make sense because it will appear to duplicate information. For example, the student has 2 parents, 5 classes, and 2 vehicles. When you join these tables you will get 2*5*2 = 20 rows. And you will see his motorcycle with his father and his math class and his mother with his math class and his motorcycle. Then the rows repeat for the history class and the science class. Really, what does his mom and dad have to do with his history class? That's why the query makes no sense.

The tables you described seem to fall into the second pattern rather than the first and so they should always be shown as subforms or subreports so you don't see the meaningless repetition.

 

[c.access]

Hi Pat,
Your post above about types of entity relationships such as hierarchial vs non- hierarchial ties in to an issue i'm having getting an access form to allow me to update all of my tables. In my case, I have many parent tables all connected to one child table w/ one to many relationship as shown below.

All of the parent tables surround the child table (APPLICATION). The child table contains FK's from each parent table.
1. Is this a logical and workable relationship design?
2. Access wizard lets me create a form w/ subform with all of the fields I want to update, but then it won't let me enter any data into the form. Thoughts?

 

[CJ_London]

You've hijacked this thread and it would be better if you started your own thread since your issue will be different to that of the OP (original poster). Best I can say at the moment is

1. Without knowing your business model not possible to say - other than possibly
2. Rule is one form, one table or updateable query. A main form can have a number of subforms. It sounds like you are not following this rule

 

[c.access]

Ok, thanks for the guidance CJ. I'm still pretty new on this site and learning how it works. I'll post a new thread with my issue once I'm able to phrase it better.