Form in Form != Subform?

[isoquin]

Upon using the built in subforum wizards, I find the end product is not exactly what I want, but close to it.

For the purposes of this explanation, I'm going to use the classic example of a store's database. Tables include [Customer], [Employee], [Product], [Sale] (intuitively), whereas the first three tables are linked to [Sale] in a one to many relationship.

When someone goes to buy an item, a form (tied to the [Sale] table) comes up for data entry. It should record things like date, the employee ID who sold the item, the customer info, and the product info, per the tables associated with these records.

The question: How do I, upon entering a product ID in my Sale form, have the form automatically update so that certain fields (such as product name, price, stock, etc) are automatically filled in (for friendly use, allowing the user to double check it's the right product), while leaving the other fields relevant to the Sale itself (date, comment, etc) left unchanged?

In other words, in the one form, linked to [Sale], I need it to access, search through, and display parts of a single record which is found in other tables, while recording to the [Sale] table (just the identifiers like product ID, not all the fields it needs to display).

If this is unclear, let me know so I may clarify. Thank you in advance.

[MMcCarthy]

OK first of All the Sale table should only contain the following information on Customer, Employee and Product

SaleID (AutoNumber, primary key)
ProductID
Qty
CustomerID
EmployeeID
SaleDate
Comment

No assuming you want to see the sale information create a query as follows and base a form on it.

SELECT SaleID, Product.ProdName, Sale.Qty, Product.Price*Sale.Qty As SalePrice, Cusomer.Name, Customer.Address, Employee.Name
FROM (((Sale INNER JOIN Product 
ON Sale.ProductID = Product.ProductID)
INNER JOIN Customer
ON Sale.CustomerID = Customer.CustomerID)
INNER JOIN Employee
ON Sale.EmployeeID = Employee.EmployeeID)