By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

How to use total query result in a form

P: 37
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 =tblRRNewPropertyPrice3!NPPricePerWeek 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
May 23 '10 #1
Share this Question
Share on Google+
3 Replies


Jerry Maiapu
100+
P: 259
@ebasshead
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
Expand|Select|Wrap|Line Numbers
  1. =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:
Expand|Select|Wrap|Line Numbers
  1. =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
May 24 '10 #2

P: 37
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
May 25 '10 #3

Jerry Maiapu
100+
P: 259
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
Jun 17 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.