Reports :: Populate Table With Price From DLookup In Form To Total In Report

I am working with a database that I downloaded and am trying to modify to fit my needs.

This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.

However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.

As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.

I was unable to upload my file...so a few notes of info...

There are no queries set up in the database for this report.

I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.

When I show the field list for the report, across the top of the window reads:

SELECT DISTINCTROW Employees.*, Products.*, [Inventory Transactions].*, [Purchase ORders].*, Suppliers.*, nz([Inventory Transact

Looks like it runs out of space

I am trying to attach a couple of images to support my comments.

Since this issue crosses both reports and forms (and tables!), I am not sure where to properly post. The end result I am looking for is on my report.

I am using Access 2003...