How to use total query result in a form

[ebasshead]

Hi Everyone,

I'm having problems with putting a result from a totals query in a form which is based on aother query. If I add the totals query to the form query, I'm not able to add or delete records on the form.

I tried to add
Val([NPPricePerWeek]) to bypass the problem but no luck,

I tried to create a txtbox on the form to access the totals query directly =tblRRNewProper tyPrice3!NPPric ePerWeek and came up with #Name?
I suspect because the query wasn't run

Is there a way to convert the value into something that will allow the form to add delete records please

Cheers Eddie

[Jerry Maiapu]

Hi Everyone,

I'm having problems with putting a result from a totals query in a form which is based on aother query. If I add the totals query to the form query, I'm not able to add or delete records on the form.

I tried to add
Val([NPPricePerWeek]) to bypass the problem but no luck,

I tried to create a txtbox on the form to access the totals query directly =tblRRNewProper tyPrice3!NPPric ePerWeek and came up with #Name?
I suspect because the query wasn't run

Is there a way to convert the value into something that will allow the form to add delete records please

Cheers Eddie

You cannot edit/modify records on a form that is based on a summary query.
So what you do is just create your form based on the first query.
To do this the easiest way is to drag and drop your first query into a new form. Give the names. Now you can calculate the totals on the sub form instead of the query.

Go to design mode of the sub form, in the form footer section, insert a text box and put this

=SUM([your field name goes here!])

This will calculate the totals within the sub form . If you need to calcualte totals for every field then create more text boxes and insert the code above for each text box.

But you cannot view the totals field you just created in the footer. So In your main form just reference the text box in sub form’s footer . To reference use this syntax.
Create a text box in the main form. And type:

=Forms![name of main form]![Name of subform]![Name of Subform footer text box]

Now you can view the total in the parent/main form and at the same time modify records..

Hope this helps..

If this method does not work then give me the details of your object descriptions: ie what is your first query doing and what is that you expect your form to dispaly and what you are really trying to achieve with respect to the query + forms.

Thankyou

Jerry

[ebasshead]

Thank you so much Jerry,

Your recommendation was a great help, noew I can get on with my life... at least until next time.

Cheers Eddie

[Jerry Maiapu]

Hi, we are here to help one and other.
I am glad to hear that my solution worked.

I was on holiday and did not see you post until now so if you are stucked up do post it as this helps us both to built our access knowledge.

Regards