CODEWITHSUNDEEP
ms-access
Rob Thomson
Rob Thomson

Reputation: 9

MS-ACCESS - Using returned value from DLookup as part of calculation

Please don't laugh. I'm sure this is something simple for all of you but as a novice, I'm in need of help.

I have an invoicing form which contains a subform. The subform allows users to select products and quantities.

For each product selected, users enter a quantity and the subtotal is automatically calculated. This part is fine.

My problem is, when I want to provide an overall total in the footer of the subform, I am just getting £0.00.

Here are the details: Field one is called "Unit Price". This unit price is found using DLookUp. The formula is "=DLookUp("[Price]","[tblCashPrices]","[ProductID]=" & [Product] & "AND [SalesTypeID]=" & [Forms]![tblInvoice1]![SalesTypeID])"

This auto-populates and seems to be fine.

Field 2 is called "quantity" and is just a number field where users can enter an integer.

In the subform, I want to generate the overall total. I have tried "=sum([quantity] * [unit price])" This is just returning £0.00.

I don't know what is going on because my subtotals for the products work but the overall total doesn't?

Please help

Upvotes: 0

Views: 956

Answers (1)

June7
June7

Reputation: 21370

Aggregate functions must reference fields from table or query used as the form RecordSource, not a calculated control.

Better options than DLookup() on form to retrieve related data.

  1. include the lookup table in the form RecordSource, join type: "Include all records from tblInvoices and only those from tblCashPrices that match."

  2. multi-column combobox for the products, the price can be in a hidden column, then a textbox can reference the combobox column by index

Option 1 will allow the Sum() function to reference the Price field. Optionally, do the DLookup() calculation in a query and use that as the form RecordSource. Then the calculated field can be referenced in Sum() calc. Just be aware domain aggregate can be slow performer in query or textbox.

Another option is to create a field in table to store the price. Then use code (macro or VBA) to execute DLookup and save the retrieved value into that field.

Also, your DLookup() has syntax error in the WHERE argument. Need space each side of AND.

Record must be committed to table before the Sum() calc will update. Record is committed when: 1. close table/query/form; or 2. move to another record; or 3. run code to save.

Upvotes: 1

Related Questions