#Name? error

[Red6]

This refers to a basic Customers / Orders database.
I have a form Orders, with a subform Order Details, the order details subform has a field called ReSaleValue

The Orders form has a field called Order Value, which I want to be populated by the sum of the ReSaleValue from each order.

I have created a qry called ReSale Totals, which sums and returns the total value for each order based on the ReSaleValue.

I have tried to make the control source of the field 'Order Value' on the Orders form, to be populated (using the expressions function) from the ReSale Totals qry but it just shows #Name? in the field when the Orders form is displayed.

Any help would be appreciated
Thanks
Ian

 

[MSAccessRookie]

This refers to a basic Customers / Orders database.
I have a form Orders, with a subform Order Details, the order details subform has a field called ReSaleValue

The Orders form has a field called Order Value, which I want to be populated by the sum of the ReSaleValue from each order.

I have created a qry called ReSale Totals, which sums and returns the total value for each order based on the ReSaleValue.

I have tried to make the control source of the field 'Order Value' on the Orders form, to be populated (using the expressions function) from the ReSale Totals qry but it just shows #Name? in the field when the Orders form is displayed.

Any help would be appreciated
Thanks
Ian

The #Name error that you are getting means that Access is unable to locate the field that you have declared for your source. The most common Control Sources would be a a column from the Table or the Query that is the Record Source for the form. Verify the spelling of the Control Source in the form compared to the Table/Query and you can usually see a spelling error.

 

[dkinley]

To the best of my knowledge, the #Name is because the control doesn't understand the reference assigned to it - in that case the data source.

It might be that there is some typo in the expression. Could you paste the expression you are using in the data source?

-dK

 

[Red6]

Thanks guys - I have checked and double checked the spelling and it's correct.

The only thing I can think of, is that there is something wrong with the expression that references the qry where the totals are calculated.

In the field where I want the data to appear I have the following:

=[Order ReSale Totals qry]![SumOfReSale Price]

The qry correctly calculates the total of each order but for some reason it will not display it in the Order Value field of the Orders form.

This is the sql for the qry which calculates the totals.

SELECT [Orders Query].OrderID, Sum([Order Details].[ReSale Price]) AS [SumOfReSale Price]
FROM [Orders Query] INNER JOIN [Order Details] ON [Orders Query].OrderID = [Order Details].OrderID
GROUP BY [Orders Query].OrderID;

I have spent all afternoon on this and I am sure that I am missing something simple but cannot see it.

Any guidance greatly appreciated.

Ian

 

[Soulium]

Check this out:
http://www.access-programmers.co.uk/forums/showthread.php?t=156541

its an alternative way of doing what you want i think.

You can sum things on a form using an onbound txt box and in its control source put =Sum([txtbox1]*[txtbox2]) e.g =Sum([Quantity]*[SalePrice])

 

[Red6]

This is now doing my head in !!!

My form Orders, has a sub form called Order Details.

The subform Orders Details has a field called ReSalePrice:

The value in this field is entered for each item within the order, thereby resulting in a number of values in several records under the field name ReSalePrice.

All I need to do is add up the the value of all these entries and put the value in the Orders form in the field called Order Value.

I have tried doing this via a qry as mentioned above and have also tried as suggested, creating a field in the form and then referencing to it, but sadly to no avail.

The Order Value filed just keeps returning #Name?

Ian

 

[dkinley]

Might require greater minds ... I thought your original post should have worked. I know this is a workaround:

=Nz(DSum("[ReSalePrice]","tblOrder","[OrderID]=" & Me!SubFormName.Form!OrderID),0)

Substitute appropriate naming, I just took a swag of your names with exception of subform name.

Or if you prefer, do a DLookup on the sum field of the query. Again, these are workarounds.

-dK

 

[Red6]

I give up !!!!!

Despite everybodys best efforts and having tried both everything I can think of and all the suggestions that have been made, I still can't get this to work.

Ian

 

[dkinley]

DLookup won't work?!

= Nz(DLookup("[TotalsField]","qryTotalsQuery","[OrderID]=" & Me!SubFormName.Form!OrderID),0)

An example to limit to the total for that particular order ID, or if there is only one field returned in your query ...

= Nz(DLookup("[TotalsField]","qryTotalsQuery",0)

Make appropriate naming substitutions. If still now work, you could post your db? If too sensitive, make a copy and strip it down for only the relevant information and dummy data.

-dK

 

[boblarson]

I give up !!!!!

Despite everybodys best efforts and having tried both everything I can think of and all the suggestions that have been made, I still can't get this to work.

Ian

Don't give up, just post your db.
How to upload a database to the forum

 

[Red6]

Thanks guys,

I have stripped out as much as I can, leaving just some dummy data on both the customers and orders tables and forms.

Hopefully someone will be able to solve this.

Just to recap: The Order Value field needs to be to sum of the resale price for each order. For example, order number 7 has a sum of £2950, this is the figure I would like to show as the Order Value.

Many thanks

 

[dkinley]

You bet ... I think I have what you wanted ...

Thanks again for posting the db. The visual I had in my head for what you wanted was slightly different. Now that I have experienced what is going on, I changed your query to a totals query and performed the appropriate calculations in the query and then used those columns as the data source for your control in the subform.

As a side note, you can obtain the order totals, VAT, etc., in a query as I have demonstrated so there is no reason to have those fields in the Order table. The concept is you are essentially duplicating data after you have massaged it with some calculation, so why have duplicate data in a database?

The counter argument is that you need to store this data because you might change the resale price in a different table at a later date and it will ripple effect through the database; thus the original total from a year ago will change when you run a totals query like that. I don't think this will be an issue for you since you store the resale price in that particular record.

The only thing that might change is the VAT calculation. I know for me, I usually store the tax rate for a particular order because that can change over time and then I use an expression in a query to recalculate the totals for me as needed.

Hope all of that made sense and this works for you.

-dK

 

[Red6]

Dk - Thanks for that.

I had actually got this far myself at one stage, however, because of the way the qry is formulated, in that grouping is applied, means that the Orders form is deactivated, i.e. the Orders form does not allow for a new record to be entered.

It is so frustrating.

 

[dkinley]

Ah .... I see clearer now, I wasn't thinking of using the same form for adding information - egg on my face. I use the excuse that I had a very late night last night.

I have to bail out right now but will have time tomorrow to readdress with that aspect in mind.

-dk

 

[Red6]

I was thinking about this last night with glass of red wine in hand, feeling sorry for myself, as we had just lost possession of the Ryder Cup - Ah well, there's always next time in Wales.

Anyway - to summarize:

We know that the calculation can be done to show the total order value, but by doing so, disables further input entry to the Order Form.

So - what if we had a form on which to input the orders, which did not have any calculated fields and which was linked in some way to the forms which we already have?

This would then give us:

Input Order form

and

View Completed Orders with it's subform showing the order detail for each order.

The Input form would be where the Order Number (Auto Number) would be generated, and the Viewing form would just feed from the Input.

I sort of know where I am coming from here, and I think the tables are in place, but just not sure how to tackle it.

Earlier posting of db refers (Stripdowndb RGKC)

Ian

 

[Red6]

Hurrah !!!!

I have almost cracked it.

By creating a new form called Input Order, I can now input new orders and still be able to show the calculated order details in a seperate form/sub form (Orders sub form / Order Details sub form), via the View Order Details button.

A couple of small problems now though:

The Input New Order button on the Customers form, needs to open a new blank form with a new order number, relative to the customer currently selected - For example, if you scroll to customer record 4 and hit the Input New Order button, it currently returns customer number 1 - I think this needs an expression.

Also, a customer can have many orders, and it is possible that each order may need delivering to a different location for example to a site where work is being undertaken. Not sure how to do this.

Thanks for bearing with me on this.

Ian

 

[dkinley]

I haven't taken a look at V2 you posted, but went back to the previous version I had to make the changes. It should work, but I only did a minimum test and don't know all of your requirements.

Holler back if this isn't quite right.

-dK

EDIT1: I forgot to delete the "Order Value" control on top of the subform - it was a scratch pad I was messing around with. I also forgot to include this link that may help you out in other areas as well.

http://bytes.com/forum/thread189852.html

EDIT2: Read other post ... here is a great starting point for VBA. http://www.functionx.com/vbaccess/