Placing Query Results in a control on a subform.

[MuskokaMad]

Thanks for the help... Brain must still be saturated with the Stout from last night (2 Nights in a row = Brain Dead)

I have a Form called frm_NewOrderEntry2 with 3 tab controls with subforms sfrm_NewOrderDetails, sfrm_Payments and sfrm_DeliveryDetails.

I have a Query qry_OrderTotal that Calculates the Total of the OrderDetails.

SELECT tbl_OrderDetails.Order_ID, Sum(tbl_Products.RetailPrice) AS SumOfRetailPrice, Sum(((([Price]*[Quantity])*(100-[Discount])/100)*1.15)) AS OrderTotal
FROM tbl_Products INNER JOIN tbl_OrderDetails ON tbl_Products.ID = tbl_OrderDetails.Product_ID
GROUP BY tbl_OrderDetails.Order_ID
HAVING (((tbl_OrderDetails.Order_ID)=[Forms]![frm_NewOrderEntry2]![txtID]));

I want to put the returned Value OrderTotal in a control on the Payments Subform so that I can confirm that the Payments are equal to the Order.

How do I do this?

Jason

 

[vbaInet]

Lookup the DSUM() function. You can use that in the control source of the text box. That could be all you need.

 

[MuskokaMad]

Tried the DSum Function even using the expression builder I get a NAME? Error. ARGHHHHHH!

=DSum((([tbl_OrderDetails]![Price]*[tbl_OrderDetails]![Quantity])*((100-[tbl_OrderDetails]![Discount])/100)*1.15),[Forms]![frm_NewOrderEntry2]![sfrm_NewOrderDetails].[Form],[tbl_OrderDetails]![Order_ID]=[Forms]![frm_NewOrderEntry2]![txtID])

 

[vbaInet]

You probably didn't understand how the DSUM() function works. Let's use the DLookup instead:

=DLookup("[OrderTotal]", "qryOrderTotal", "[Order_ID] = " & [Order_ID])

Have a look at that function instead.

 

[MuskokaMad]

Have I mentioned you have the patience of a saint????

I was completely oblivious to the fact that you can do a DLookup (which I use elsewhere in my data base with a query.

I really appreciate the way you have helped me understand what is being done. I have implemented most of your ideas in a ton of other forms in the application.

Thanks Again!

 

[vbaInet]

Hehe!! Thanks. You're welcome

I hope you're not using the DLookup multiple times? It's not the fastest of functions. If all you were doing was getting an OrderTotal of a subform you can get that result without needing the DLookup.

Good luck with the rest of your db!

 

[MuskokaMad]

Should I be considering an alternate method???

 

[vbaInet]

Let's see your db and we would be able to advise appropriately.

1. Make a copy of your db
2. Remove any confidential info
3. Compact & Repair
4. Add some bogus data
5. Zip, upload and post. (Click the GO ADVANCED button and you will find the button to MANAGE ATTACHMENTS).

 

[MuskokaMad]

I Currently use Dlookup in 1 other context. To set Point of Time Values for Products in Order Details ie. To store the price of an item at the time it was purchased so that the record is unaffected by future markups of the same product.

In the above case I will be using the Value in an IF Statement to validate closing an order record payments MUST Equal the order details Subtotal before the record can be closed in this application.

So back to the previous is there a better solution?

 

[MuskokaMad]

will enter some dummy data and post shortly

 

[MuskokaMad]

Here it is... and surprise the DLookup No longer works... ARGHHHHHH.

I understand that cleaning sewers is a rewarding career... wonder if it too late to change????

The Form is frm_NewOrderEntry2. It is meant to be opened on creating a new customer record with frm_NewCustomerEntry.

The DLookup is on The Payments Tab.

It stopped working when I put in the button on the payments tab which now doesn't work either.

There are only 2 products in tbl_Products so you will need to reset there OrderStatus to In Stock to have them appear in the Product List.

Use Customer Neville as it had all the info in it.

Thanks again!

Jason

 

[vbaInet]

The Form is frm_NewOrderEntry2. It is meant to be opened on creating a new customer record with frm_NewCustomerEntry.

The DLookup is on The Payments Tab.

I noticed you've already calculated the value in your Order tab in the Order Total control. You don't need a DLookup for this. Use the Expression Builder on the control source (i.e. click the elipsis "...") and locate that Order Total control which will be under FORMS -> LOADED FORMS -> frm_NewOrderEntry2 -> sfrm_NewOrderDetails -> txtOrderTotal.

It stopped working when I put in the button on the payments tab which now doesn't work either.

That doesn't work because the AmountOwning control is showing #Error. Remember to use the Nz() function when doing calculations. This would interest you too:
http://www.mvps.org/access/forms/frm0022.htm

Use Customer Neville as it had all the info in it.

I used Joe Bloggs. I preferred to create mine

Lastly, do not use the Format() function in the control source if you're performing calculations on the result. There's a Format Property for your control (i.e. textbox in your case). Look under the Format tab of the control's properties. You will find there's Currency there.