Forms Help

[Chrisopia]

My head hurts trying to figure this out...

I have a subform in Datasheet view, basically so I can create an invoice type form.
The Form is Called "Order" and holds the subform (in datasheet view) called "OrderDetails", basically to sort out the order details by the order.

The order details contain fields which need to be totted up at the bottom of the page, like the totals box on an invoice.

How would I go about doing this? and where do I place the totals box (The order form, or subform?)
Preferably a controled Text box on the form somewhere.

I'm not the best with access, but i have played around with queries so the subForm is now a Totals query, placing a "SUM" catagory on the 2 fields I have to tot up at the bottom, "VAT" and "TOTAL DUE"


Hope you can help.

 

[Chrisopia]

Simpler:

How do I create a SUM field, relative to the orderID?


The OrderID is the ID of the order form

 

[DanWall]

Are you using Access 2007?

If so, I just do it like this.

Select the subform table, or table you are wanting the total column on, in Form view.

Then apply a 'totals' row.

 

[Chrisopia]

Unfortunately I do not own 2007 version yet,

seems like that would be ideal though.

Can you somehow find the coding behind that, is it a VB code or something?

 

[DanWall]

I don't know mate. Sorry.
I'm new at this myself.

I'd bet there's a way though.

 

[tarcona]

Maybe what I would do, but probably not the best way, is to create another text field in the mainform and then fiddle with the Control Source of that text field so everything you want to add from the subform is added.

Hope that helps...

 

[Chrisopia]

Maybe what I would do, but probably not the best way, is to create another text field in the mainform and then fiddle with the Control Source of that text field so everything you want to add from the subform is added.

Hope that helps...

hehe, that was my first guess.

I tried SUM([VAT])
and even SUM([Order]!Form.VAT)

or whatever... all that came up was a error sign in the text box.

DanWall:
What's the control source of it? maybe that has some clues?

 

[tarcona]

Just click on the Properties of the field and go to Data and then Control Source and then click the elipses (SP?) and then create an equation.

 

[Chrisopia]

ahuh, but whats the equation?

 

[tarcona]

Ah, sorry for the confusion. I wouldn't know the equation right now. I would need to see your database to figure it out. So if you send an example or whatever, I can see if I can figure it out for you real quickly.

 

[Chrisopia]

heres a zipped copy of my database

www.chrisopia.co.uk/SAMPLE.zip

 

[tarcona]

Says "File Not Found".

 

[Chrisopia]

sorry, thats because of the forums dodgy linking thingy...

http://www.chrisopia.co.uk/SAMPLE.zip

 

[tarcona]

Ok, I looked at the database. Yeah, you are right it doesn't work right when you put the text box on the Main form. I know you want your subform to be in Datasheet view, but since you have so many fields and you have to scroll horizontally, it probably would look more professionally done if you put the subform in Single Form view and then rearrange the text boxes. I would quickly do it for you but your form is only in Read-Only.

Then you can make a text box in the form view that shows the totals. I tried a simple equation and it worked. Hopefully this helps you. You are on the right path though. I accidentally opened up the other form and it looks very professionally done.

 

[tarcona]

If you send me something that I can edit, I can inport one of my forms that I have done and then just change the fields to match yours. If you want that done I can do it. Just send me a database I can edit.

 

[Chrisopia]

That would be much appreaciated, however i thought my database was already editible?

Not sure why it's only read-only. Very odd.

Cant think fo how i can change that, but could you possibly just add them to a new database, and Ill import them myself?

 

[tarcona]

Hm....ok I got everything going. Except what is weird is that it calculates the total when you open just the subform. But it brings up an error when you open up the Orders with the subform. Hum....I will keep on trying here.

 

[Chrisopia]

much appreciated.

This will be all worth while if we can figure this out.

I know I can create this on my own, it's jsut better than paying £3000 which i was once quoted!?

 

[tarcona]

I am having trouble with the sum thing too. I cleaned up your form a little bit so it looks better. What I suggest is that you search through this forum. But first you can go on your computer to Local Disk C > Program Files > Microsoft Office > Office > Samples > Northwind.mdb. And open that up and go to Quarterly Orders form and Quarterly Order Subform. These deal with what the problem is.

I will keep on trying and I will post if I have success. But here is what I have gotten done. Hopefully you like it.

 

[tarcona]

Just ran across this:

"Add Total Text Box on Main Form for Totals on Subform"

from Mr. Bob Larson's site:

http://www.btabdevelopment.com/main/

I haven't looked at it at all, but it seems like it deals with the exact problem that you are dealing with...GOOD LUCK!
Post back or send me an PM if you still have troubles.