Unanswered: Multi-table queries and Inserting New Records
I need to find a way to be able to insert a record into a query... or something like that... I have a query which joins 3 tables together, but I really only want to update and insert into one table. I however want to display parts of the other tables within the same subform as the table I want to update... maybe it's easier if I explain with my example...
I have a master form which displays project information 'tblProject'
and a listbox which lists the claims that the project has which is found
from a query to 'tblClaims' with the 'tblProject' primary key as the constraint.
When one selects an individual claim to edit a new form opens 'frmClaim'
In 'frmClaim' it displays all the information about this individual claim that one can edit at will. There is a subform that displays all the tender items in this individual claim.
This subform is where my problem lies. I tried creating it with a query that joined 'tblTenderItems', 'tblPricing'(a master price list), and 'tblLineItem'(a master list of line items that we have). I wanted to show 'Quantity', 'Lineitem_No' from 'tblTenderItems', 'Price' of each item taken from 'tblPricing' and the 'Unit_Label'(or Unit of measurement for the line item) from the 'tblLineItem'.
When you have a one table query it gives you an additional new line to be able to insert new tenderitems as needed, but with a multi-table query you may only edit the data that is already inputted. Since Unit_Label and Price are already set I would only be inserting new rows into 'tblTenderItems' and none of the other tables.
I tried to make a subform with only the one table... and then create unbound disabled textboxes to show the Unit and price... but when I used VBA to populate the textbox... the value just propagates through instead of say showing the price and unit of each of the tender items... it just shows the first price and unit as the price and unit for all of them.