DLookUp In A Report? (1 Viewer)

rob1

Registered User.
Local time
Today, 09:10
Joined
Jun 7, 2012
Messages
11
Hi there,

I am stuck with using a DLookUp whilst making an invoice style report.

I am using 2 Tables

"Transactions" & "Products" with Fields :

TransID ProductID
Product1 ProductName
Product2 ProductPrice
Product3 ShippingPrice
Product4
Product5

Products1-5 Are Filled In A Form By A DropDown Box Linked To ProductID

The Report(Invoice) Is Based On The "Transactions" Table

What I need is for the Report(Invoice) to have a column of textboxes next to all 5 Product Boxes with a DLookUp filling them with the correct ProductPrice.

Then another column of textboxes with a DLookUp for the correct Shipping Price per item.

I am completley lost :/

Any help would be great :)
 

pr2-eugin

Super Moderator
Local time
Today, 09:10
Joined
Nov 30, 2011
Messages
8,494
Hello rob1, It seems you have set up the tables correctly.. With two table Transaction holds only product name and the Product table holding the product description and price..

Now your Report does not only take Tables, it can also take in Queries.. Instead of using DLookUp which is a very costly operation if you are planning on doing it for each and every product.. The simplest option to do here would be to create a Query, that performs a JOIN between Transaction table and Products table and set the RecordSource of the Report to the Query you created and in turn, the Control Source of the Text box to the appropriate field in the query that displays the Price..
 

rob1

Registered User.
Local time
Today, 09:10
Joined
Jun 7, 2012
Messages
11
Ive just tried using a query but it only gives me the option to link
[ProductID1] with a [ProductPrice] and [ShippingPrice]
what do I do for the other 4 ProductIDs??
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 19, 2002
Messages
42,970
Each record should have only a single productID. What happens if you have more than four products on an order? How do you think Amazon handles this? Do you think they limit customers to 4 items per order?
 

Users who are viewing this thread

Top Bottom