Adding query fields to a subform when the subform contains fields from a table

[adagio12]

Hi guys,
since I'm not an expert with Access, I'm having lots of trouble with it.

I want to create a form that contains a main form and a subform, and when the forms are filled out, they update information to two tables.

So far, I've created a main form that can update info to the "Transactions" table (contains info such as transaction date, employeeID, customerMemberID...) and a subform that updates to the "TransactionDetails" table (data relating to quantities, product code, unit price...).
However, I have a query that contains calculated fields based on information from the "TransactionDetails" table (such as taxes and total price) and would like to add some of those query fields to the subform. I failed many times in doing this because the subform wouldn't work when I add those fields via the subform creation wizard.

I also think it would be nice if somehow the 'unit price' would automatically show after the data for the 'ProductCode' is selected, so that the user of the form wouldn't need to manual type in the unit price.

Any help would be appreciated.
Thanks in advance

 

[PNGBill]

You normally can not update a table field while in a query that uses more then one table as it's data source.

If the Product Code and UnitPrice are in the same record source then the field should populate.
Or you can have some data in a subform and link Master Child fields so the product code in one form will set the criteria for the data in another.

Another way is to use DLookup.

Seems like you may have your forms and record source a bit screwed up.

Stand back and review how you are handling the task.

 

[adagio12]

@PNGBill:
I went to look at my queries, revised them and made improvements.
The form finally works now.
Thanks for the reply.

 

[SteveSchapel]

Bill,

You normally can not update a table field while in a query that uses more then one table as it's data source.

Just to clarify, this is not exactly true, as stated. Whether a query is updateable depends on a number of factors. Most of the time, data in a table that is part of a query based on 2 tables is in fact updateable. One of the relevant factors is whether the join between the tables is based on a field that is uniquely indexed (it nearly always is in queries based on 2 tables). Cartesion queries (no joins) are also normally updateable. Queries based on more than 2 tables are often updateable too, but not in the case of a many-to-one-to-many configuration.

Hope that helps.

 

[vbaInet]

As an adendum to what Steve said, here are scenarios when a query is not updateable:

http://allenbrowne.com/ser-61.html